I created a package whose source data looks like this

Code EDate Price

HO 6/20/2015 2.0

HO 6/20/2015 3.1

HO 6/20/2015 4.2

HO 6/20/2015 3.3

CL 6/22/2015 2.5

CL 6/22/2015 3.9

and so on..

I want to store Edate in a variable for a particular Code. How can i do this in SSIS?

var_EDate_HO = 6/20/2015

var_EDate_CL = 6/22/2015

See this article.

Do you need then to accumulate them? Or action somehow?

Typically it is a good idea to simply put them into a database table.

One way of doing that is to have the Multicast Transformation split the data into two flows.

One flow will work as is, and the other branch will have one or more Conditional Splits to act on the "particular Code" essentially redirecting to a different destination (the table) from which you can harvest them or process.

The Derived Column transformation does it for a single value in the data pipeline, but fairly limited to what a SSIS expression can do, another option is to use a Script Transformation which also operated like the Derived Column Transformation, but exposes the full power of VB/C#.

Hi SyedF,

According to your description, you want to store particular Edate data in a variable.

• If the particular Edate data is defined by yourself, then we can directly create two datetime or string data type variables for the particular values.
• If the particular Edate data is defined by the source data, then we can use an Execute SQL Task to return the resultset for the variable. For more details, please see:
  https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/

Thanks,
Katherine Xiong

There are different Flat File Destination. I want to save the file as HO200615.txt. So i wanted to save these dates in a variable and use it rename the output file.

We have to save date based on Code. And each code has different date. So are you saying we have to use n number of Execute SQL to store n number of Edates.

Noo, you cannot have a dynamic number of Execute SQL Tasks.

You need something to drive the logic which still remains obscure.

What we need to know is if the codes change dynamically, hardcoding is too hard to manage later, and too long to event develop.

Perhaps some processing needs to shift into a database.

No, you cannot have a dynamic number of Execute SQL Tasks.

You need something to drive the logic which still remains obscure.

What we need to know is if the codes change dynamically, hardcoding is too hard to manage later, and too long to even develop.

Perhaps some processing needs to shift into a dat

Yes the codes changes dynamically. The user can add few more codes or remove some of the existing codes.

You should then come up with some logic that allows the codes to be managed ideally by the client in a database. And have some dynamic processing in place.

I at the moment do not see the whole picture of how it is going to be processed and why you need these variables to recommend any particulars.