Solved

SSIS - how to use DestinationVariabke in a File System Task

Posted on 2007-11-13
5
1,875 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:CraigLazar
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 20277827
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.
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 20278050
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
0
 
LVL 19

Accepted Solution

by:
frankytee earned 125 total points
ID: 20286733
i think the term FileSystem task is for sql 2005 which i haven't used but since you are renaming the file in this task i would assume it has the ability to communicate with your package variables.
If not then you would need to create an active x script (using either vb script or jscript) and associate your file to the variable. eg below, replace names accordingly.

Function fnGetVariable()
    Dim pkg
    dim s  
    Set pkg = DTSGlobalVariables.Parent
    s = DTSGlobalVariables("whatevervariable").Value
   'now rename your file with this variable
      ....etc
End Function
0
 
LVL 19

Expert Comment

by:frankytee
ID: 20286770
in sql 2005 the syntax might be:
Dts.Variables.Item("whatevervariable").Value
rather than my previous post
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 20295813
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
get column names from table in vb.net 8 28
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 32
Find results from sql within a time span 11 30
SQL view 2 27
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question