February 7, 2008 at 9:25 am
I am engaged in a crash course (self taught) in SQL Server. A contractor who is no longer with us built a project to extract data from a database and output it to Excel.
The paths are completely different in development to production and I need to find a way to supply a path dynamically at run time to avoid changing the packages after they have been signed off in test.
I think I understand how variables and configurations might be used in flat file sources and destinations from searching the web. I can't make the same principles apply to excel connections in a file system task
The first package in the project is a File system task to prepare the workbook. At present it has the source and destination workbook in the same folder in our development environment. The source and destination connections are both set to say that the file exists, and the file system task copies the source to the destination, overwriting output from the last run.
The file system properties don't seem to allow for variables and I've not been able to locate an example fo this specific activity on the net. Any ideas anyone?
TIA
February 8, 2008 at 5:25 am
My apologies, I missed this post when I replied to your last.
In order to dynamically set the file path you need to specify a variable for either the source path, the destination path or both. This is actually done on the FileSystemTask component. You will see in the component editor 2 properties called IsDestinationPathVariable and IsSourcePathVariable. Set these to TRUE and specify the variables that will hold the path.
Then use the configurations to set the variable at run time.
Kindest Regards,
Frank Bazan
February 8, 2008 at 6:21 am
Thanks for the reply. I've been able to take this further but I'm still not getting it 🙁
I have 3 package level variables; EXFileRootDir, ExcelInput and ExcelOutput. They are set, respectively, as: J:\ITID\CUSTOMER SERVICE\Problem Management\Analysis team\Mirror\DEV\Projects\Time to Repair Analysis\Time to Repair Analysis@[User::XLFileRootDir]+"DTS All Exceptions Template.xls"
@[User::XLFileRootDir]+"DTS All Exceptions.xls"
My File System Task has DelayValidation set to True, Destination set to User::ExcelOutput, and Source set to User::ExcelInput
When I run under debugging I get the message "Illegal characters in path".
I've tried a few different variations to no avail.
Sorry if I'm missing something basic and thanks again for the help
February 8, 2008 at 6:35 am
It sounds like a typo.
Try putting in a breakpoint post execute the filesystem task. While its still in debug mode, open the locals window and find out exactly what is in both the excel source and destination variables. Or better, copy whats in there and past it into the address bar on the Windows Explorer. See if the path actually works.
If its saying there is an illegal character in there, it just sounds like the expression has been incorrectly constructed.
Hope this helps
Kindest Regards,
Frank Bazan
February 8, 2008 at 6:52 am
Frank. Thanks for all your help, but I think I'm going to have to find another way to solve this. I'm just too new to SSIS.
I worked out how to put the breakpoint in, but as far as I can see, the package has just gone mad with "\". It has put 2 in between every folder in the file path, and added more in at the beginning and end of the file name.
I've tried a making a few changes but to no avail. I will return to hard coded file names and create the same path on the production server. Hopefully, by the time I get to the end of this project I'll have gained enough knowledge to suss it out for myself. 🙂
Thanks again, anyway
February 8, 2008 at 7:14 am
Hi,
There are supposed to be double slashes. This is because "\" is an escape character. My apologies, I forgot that this happens. If you copy that path and replace each "\\" with "\" you will have the windows path that its looking for.
The other alternative to reading directly from a variable is to create 2 connection managers. Highlight each one and hit F4 to bring up the properties. In there, you will find an expandable field called expressions. Use this to create a new expression for the ConnectionString property. Remember as I alluded in the paragraph above, that you have to escape each backslash with another (i.e. The expression will look something like "C:\\Directory\\" + [@user::excelsource]). Click "Evaluate Expression" on the expression editor to verify that the correct path is being used.
You then have to change your filesystemtask, so that instead of getting the path from a variable, you specify a connection manager instead.
Good luck
Kindest Regards,
Frank Bazan
February 8, 2008 at 9:59 am
No luck, I'm afraid. With or without the extra \, and whether I have a separate connection or use the variable in the File System Task.
It always evaluates OK, but as soon as I run it or save it I get either invalid format or it thinks the variable doesn't exist. I've tried defining the variable at File System Task level and at Package level I've tried creating 2 separate variables, each holding the complete path and file name, and I've tried every combination of the above, so I'm definitely missing something.
Your help has been very much appreciated but I'm honestly out of my depth for the moment. I just hate the way it works instantly the minute I hard code the connections 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply