Ideas on Passing filename from script task to data flow task.
-
Hi Team,
I have a Script task(VB code),I have the output as filename to pick the most recent file from my folder but how do I pass this filename as input to my data flow task??
Any ideas or suggestions will be appreciated.
Let me know if you need further explanation.Thanks
SE - sathwik.em91 - Tuesday, August 14, 2018 9:20 AM
Put the file name into a package-scoped SSIS variable. It's not clear how you want to use this in your data flow, but this should work, depending on your requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Tuesday, August 14, 2018 9:25 AM
Thanks for your reply Phil,
It's not clear how you want to use this in your data flow:
To Answer your question I need to pass three columns from my output file to my dataflow task. -
create all your variables you need in your SSIS package.
Pass them to your script task.
then in your code, read them into a variable, assign a value to them in your code, and finally send them back to SSIS; your subsequent processes can use the variables, and your script task assigns the new value.
string FullFileName= (string)Dts.Variables["FullFileName"].Value;
//some logic to get some values
FullFileName = GetLastRecord();
Dts.Variables["FullFileName"].Value = FullFileNameLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - sathwik.em91 - Tuesday, August 14, 2018 9:46 AM
So is your question about how to configure the data flow to use the dynamic file name stored in the package variable as a data source? If not, please clarify what you are asking for.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin - Tuesday, August 14, 2018 11:19 AM
Phil-Yes can you let me know the process of configuring the data flow task to use the filename stored in the variable as a datasource??
- sathwik.em91 - Tuesday, August 14, 2018 12:05 PM
OK, easy enough. Create a connection to your flat file in the usual way.
Then, open the Expressions property for the connection and override the ConnectionString property for the connection, using the variable you have created.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Or you can change the connection string for the flat file connection manager in code in you script. This example is setting the zip file connection for a load process:
Dts.Connections("CWC-COUNTY-RIVERSIDE-DEPT_OF_PUBLIC_SOCIAL_SERVICE.zip").ConnectionString = Dts.Variables("User::ZipFile").Value.ToString
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply