Illegal character in File Path

I am working on a SSIS package that firsts takes the input file and copies it somewhere and renames it.  I used to be able to perform the rename easily in DTS.  In SSIS, I am getting an error on "Illegal character in file path."  Here is the path....

"I:\\DEPARTMENTS\\Business Management\\Business Intelligence\\Initiatives\\ScopingDB\\EmployeeModel"  + "_" + 
(DT_STR, 4, 1252)  YEAR( GETDATE()  ) +
(DT_STR, 2, 1252)  MONTH( GETDATE()  ) +
(DT_STR, 2, 1252)  DAY( GETDATE()  )+
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss", @[System::ContainerStartTime]), 2) +
".xlsx"

I had this same path in the Expression tab of the File System Task and it showed me the value so why won't it work?  I also have it as a variable and it doesn't work.  Suggestions?

Puzzling.  I just set up what you described and it worked fine for me.  Here are the screenshots of what I did - maybe you can compare settings to make sure there isn't a difference?

First, I set up the variables like this:

Then, I set the properties on the "NewTest" selecting that it was to be evaluated as an expression - and I set the expression text to a slightly modified version of what you posted ( I used my C drive instead of I ).

Next up, I dropped in the file system task, set it to rename and changed the source and destination values to be variables.  I set my "Test" as my source and my "NewTest" as my destination like this:

Then I ran the package and here is my result:

I checked and indeed the source file was gone and it was moved and renamed to the new location.

Hope this helps,

Keith Hyer

Your variable has the file name with the date and time.  Why is that?  How come it doesn't look like the link in the expression?

This is the error that I get now.....

Error at File System Task [File System Task]: An error occurred with the following error message: "Failed to lock variable "I:\DEPARTMENTS\Business Management\Business Intelligence\Initiatives\ScopingDB\EmployeeModel_2011824152736.xlsx" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

bluize76 said:

Your variable has the file name with the date and time.  Why is that?  How come it doesn't look like the link in the expression?

• Open up your variable window. 
• Then right-click on the variable and select properties. 
• In the properties window that will open, look for the "Evaluate at expression" property.  Set that to "True" - I believe it is false by default.
• Then click on the area that is blank next to "Expression".  The "..." button will appear. 
• Click it an enter your epxression in the "Expression" area of the window.
• Click on "Evaluate Expression" to ensure that it worked as you wanted it to.
• Click on OK to close the window.
• When you get back to the variable window, the "Value" will be updated to the evaluated expression.

NOTE:  This does NOT automatically happen in BIDS, but it does update the value when the package is running.  If you want to update it in BIDS, you need to manually evaluate any expression - and they all update ( as far as I know ).

Hope it helps!

Keith A Hyer

bluize76 said:

This is the error that I get now.....

Error at File System Task [File System Task]: An error occurred with the following error message: "Failed to lock variable "I:\DEPARTMENTS\Business Management\Business Intelligence\Initiatives\ScopingDB\EmployeeModel_2011824152736.xlsx" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Did you put the path in as the variable name perhaps?  I'd double-check your settings on the file system task.  You want both the source and destination to be your variables - not the value contained within the variable at runtime. 

Does that make sense?

Keith

Keith,

Here are some screenshots.  I think I am doing it right.  The destination variable is to the DataTimeFile but then defaults to the actual filename with date and time after it is done running.  Thanks!

http://www.bidn.com/Assets/Uploaded-CMS-Files/SSISError-f84169b3-dde3-44f3-83c2-cd6dccd55183.doc

Ok, here is a quick 3-step to correct it - everything else looks good.  Right click and "Edit" your file system task.

Click inside the "Destination Variable" text - anywhere should do.

A drop-down arrow should appear to the right of your ( what is currently the path and file name ).

Find and Pick your variable from the list.

What you should end up with will be your variable name in there ( this pic shows the variable already selected ).

The rest looks good from what I can see.  I think if you change this one setting, you'll have it.

Keith A Hyer

I have attached a document to show my setup and run.  I do set it to the variable and when it runs unsuccessfully, it changes the variable name to the actual file name. 

http://www.bidn.com/Assets/Uploaded-CMS-Files/SSISError-28546c2f-c38e-49c7-b721-706fa324582d.doc

Ah.  Ok.  Try this -

Right-click your File System Task and go to properties.

In the properties window, scroll down until you find "Expressions".

Click on either the "+" to expand it or click in the box next to it and an "..." button will appear.  Click that to see if the Source property is being set to the variable.  If it is - just delete the text in the "expression" side ( if you opened the window ) or the right-side if you just expanded the "+". 

When you clear out the value and try to click OK ( or move from the field ), you should see a message that the expression will be deleted.  Click "Yes" to delete the expression.

If you don't have anything in there - let me know and we can dig a bit deeper into it.

Keith A Hyer

Keith,

I deleted the expression, but it tells me "Illegal character in path".  As the variable, I have using double slashes vs single slashes and WSTR vs STR and still get Illegal character.  Here are the samples that I have tried:

"I:\\DEPARTMENTS\\Business Management\\Business Intelligence\\Initiatives\\ScopingDB\\EmployeeModel"  + "_" + 
(DT_STR, 4,1252)  YEAR( GETDATE()  ) +
(DT_STR, 2,1252)  MONTH( GETDATE()  ) +
(DT_STR, 2,1252)  DAY( GETDATE()  )+
RIGHT("0" + (DT_STR, 2,1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("ss", @[System::ContainerStartTime]), 2) +
".xlsx"

"I:\\DEPARTMENTS\\Business Management\\Business Intelligence\\Initiatives\\ScopingDB\\EmployeeModel"  + "_" + 
(DT_WSTR, 4)  YEAR( GETDATE()  ) +
(DT_WSTR, 2)  MONTH( GETDATE()  ) +
(DT_WSTR, 2)  DAY( GETDATE()  )+
RIGHT("0" + (DT_WSTR, 2)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_WSTR, 2)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_WSTR, 2)DATEPART("ss", @[System::ContainerStartTime]), 2) +
".xlsx"

"I:\DEPARTMENTS\Business Management\Business Intelligence\Initiatives\ScopingDB\EmployeeModel"  + "_" + 
(DT_STR, 4,1252)  YEAR( GETDATE()  ) +
(DT_STR, 2,1252)  MONTH( GETDATE()  ) +
(DT_STR, 2,1252)  DAY( GETDATE()  )+
RIGHT("0" + (DT_STR, 2,1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("ss", @[System::ContainerStartTime]), 2) +
".xlsx"

"I:\DEPARTMENTS\Business Management\Business Intelligence\\Initiatives\ScopingDB\EmployeeModel"  + "_" + 
(DT_WSTR, 4)  YEAR( GETDATE()  ) +
(DT_WSTR, 2)  MONTH( GETDATE()  ) +
(DT_WSTR, 2)  DAY( GETDATE()  )+
RIGHT("0" + (DT_WSTR, 2)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_WSTR, 2)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_WSTR, 2)DATEPART("ss", @[System::ContainerStartTime]), 2) +
".xlsx"

Would it help you if I sent you the code from the SSIS package?  Thanks for everything!  This has been really frustrating.  I worked on DTS packages a couple of years ago and had an idea of what to do.  It should have been easy. 

I think I may have missed one step in my instructions there. 

• ** Set the source to the variable one more time ( since it's replacing it with the path/file ) and..
• THEN delete the expression(s) on the File System Task. 
• Save it in this new state.

The expression should only be on the variable - that way the value in the variable gets set at runtime.  The File System Task just uses the [value] to which the variable was set by the expression.

If that doesn't take care of it, let me know and sure - I can take a look at the code ( as long as that won't get you in trouble with you boss. :) ).

As for being easier - I totally agree!  SSIS is a major paradigm shift from DTS.  I didn't get it at first but my boss was kind enough to let me take one of PragmaticWork's online classes on it.  The first day I was thinking - what a huge waste of money ( it starts out very simple - a little TOO easy to follow ).  The 2nd and 3rd days made it worth it.  Once you get used to the quirks and over the, "Microsoft!  Why in the @_)&% did you do it THAT way!?" thoughts, it gets better.  I feel your pain on it - and I'll help as much as I am able.

Keith A Hyer

FYI - Another thing to try would be to delete the file system task and start it from scratch.  You should only have to change the IsDestinationPathVariable to true, then select it and select your source connection again.  Finally, "hook it back up" with any precedence constraints that were there before.

Keith,

Since I took the expression out and only have the task linked to the variable with the datetime configurations, it does not change the destination to the file name.  It leaves it as the variable name.  So by removing the expression, it fixed the issue with the destination changing from the variable to the value of the variable.

Now it just keeps saying "Illegal characters in path."

One down one to go.

What's the new error message you're getting?

The error is "Illegal characters in path."  I have tried paths w/o spaces and using the desktop as my path.  None of it works.

Can you tell if the characters are illegal on the destination path or the source path?

Take a look at your flat file connection manager properties.  Open up the "Expressions" on it like you did above.  Are there any expressions defined on the adapter?

Keith

I have a File Connection Manager for my Source file with the filename of I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Employee Model.xlsx.  The destination variable goes to my variable that I defined with the above definitions that I have tried.

Do I need a Connection Manager for the output file?

I don't think so.  I just set it up on my system ( C instead of I ) and it ran like a champ.

Take a look at this .dtsx file.  It just needs the same directory names, but on your C: instead of your I: drive.

http://www.bidn.com/Assets/Uploaded-CMS-Files/Package-35d223e3-e3fe-44c2-acc7-5782f1bf1b0f.zip

I probably should have asked this a while back - which version of SQL Server are you running for this?

Keith

OK, so I got yours to run and found the issue with mine.  The problem is with my destination file that points to my variable. 

I had it working when the variable defined as:

I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_20090826.xlsx

But when I changed it to include the system datetime, if failed.  My variable looked like this and the package gave me "Illegal characters in path". 

"I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_" + (DT_STR, 4, 1252) Year( @[System::StartTime] ) & ".xlsx"

I also tried and it failed on illegal characters:

"I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_" + "20090826" + ".xlsx"

"I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_" & "20090826" & ".xlsx"

I tried the below and got the following file name in my folder - Employee Model_ & 20090826 &  .xlsx

I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_ & 20090826 & .xlsx

I tried and got that the given path's format is not supported so it didn't like the single quotes.

'I:\DEPARTMENTS\Delivery Services\Scope Planning\2012 Scope Planning\Data Models\Backups\Employee Model_' & '20090826' & '.xlsx' 

In conclusion, I need a way to put in the variable field the code to get the system datetime. 

(DT_STR, 4,1252)  YEAR( GETDATE()  ) +
(DT_STR, 2,1252)  MONTH( GETDATE()  ) +
(DT_STR, 2,1252)  DAY( GETDATE()  )+
RIGHT("0" + (DT_STR, 2,1252)DATEPART("hh", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("mi", @[System::ContainerStartTime]), 2) +
RIGHT("0" + (DT_STR, 2,1252)DATEPART("ss", @[System::ContainerStartTime]), 2)

I have tried many versions and haven't figured it out yet.  Ideas?

I started writing this when I first read your post and finished it up this morning.  Here is a step-by-step on how to move a source file to a destination file ( the destination is built via your original posted expression ).  It's EVERY step, so please don't take it as "talking down" - I just tried to document it all in case there was a minute detail or step that you weren't aware of or overlooked. 

This process should not require a flat-file connection manager - just 2 variables, one of which would have your original expression.  Your original expression was correct.  I'm just guessing you have it either in the wrong place - or in too many places.

http://www.bidn.com/Assets/Uploaded-CMS-Files/DailyArchiveViaSSIS_Step-By-Step-bc33c742-b789-4a3b-88c7-089b828949c0.doc

I hope this helps you find your issue. 

Once you run through it, I would delete the flat file connection manager and the File System Task from your package and do these steps from scratch - probably your best bet to get it clean / working the way you want it to work.

Keith A Hyer

It works!!!  I was missing the "Expression" in the properties box of the destination variable.  I was just putting the formula in the value box for the variable. 

Thank you SO MUCH!! 

Nothing had a document similar to yours to walk me through that.  One little thing that you miss in anything Sharepoint just throws you for a loop.  I really appreciate all the time that you have taken to help me. 

You're quite welcome - glad you got it working!

Keith