asked on

SSIS - how to use DestinationVariabke in a File System Task

Hi,
I am using a File System task in SSIS. My source file is a standard xls output file. i have a for loop container and in this container i have a variable. I need to somehow in the File System task add this variable to the output name of my xls doc.

so lets say my source file is Trn Extract.xls - now based on the current variable value in my for loop container, lets say its currently X
then i need my Destination file name must be "X Trn Extract.xls" and so on so the next one would then use the same source file and the next value of the variable would then be "Y Trn Extract.xls"

Now i have set the IsDestinationPathVariable to True
but i am not sure how to then set the DestinationVariable to my output directory including the variable as part of the file name

hope this makes sense

thanks

frankytee

are you using a simple "transform data task" to create your excel file? if so then the variable would be out of scope with the task. either create an active x script to do the extract where you can use your variable, or rename the file after creating it by using one the reserved sql stored proces (something like xp_rename etc) but you would need to insert the variable into an sql table and then read from this sql table when renaming the file.

CraigLazar

ASKER

Hi,
i am using a Data flow task where i have an OLE DB source being my Qry then to an Excel Destination task. Once the file is created i am then using the FileSystem task to rename the file thats just been created

ASKER CERTIFIED SOLUTION

frankytee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

frankytee

in sql 2005 the syntax might be:
Dts.Variables.Item("whatevervariable").Value
rather than my previous post

CraigLazar

ASKER

Hi,
I figured it out,
I am just going to use a script object, and in there write code to manipulate my files they way i need to

thanks