Populating SSIS variables. 1

[victordba]

I have a little question. I have a small Excel file with 2 colums: "Rate" and "Rate Description". There are only about 10 records there (but it might change over time). Users can change rates at any time. I am supposed to use those rates in my SSIS package to calculate derived fields in one of my tables. Is there a way to read this file every time I run my package and assign those rates to package variables, so I could use them for my calculations ?

Thanks,
Victor.

 

[Catadmin]

Absolutely.

Now, this might be a little ham-handed, but this is the way I would do it.

Create your DataFlow to import your Excel sheet into a Temp Table (global with ## before the name). Add the appropriate Package level variables to the package. Then run an Execute SQL Task to Declare a couple of local variables, pull the values you're looking for and, in the EDIT of the Execute SQL Task, add in the Package variables so the local statement variables get mapped to the Package variables.

There might be a better way do this. But that's the first method I would try. However, in Data Flow, there are several little tasks you could play with to see what would work best for you.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

WOW. I was playing with it and it is NOT as simple as it sounds if only because SSIS is so not intuitive.

It looks like you might have to have a table in your SQL Database that imports the Excel values and then do an Execute SQL Task on that to get the variables set. Or you could set the variables with a Script Task, but that's a little more complicated if you don't know C# or VB .Net

If someone else knows the details of how to go from Excel (or a flat file) directly into a global Temp table, I'd love to hear it.





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[victordba]

Thanks, Catadmin.

I found the easy solution. First I used a Data Flow task. Inside this task first I put Flat File Source connection (it also can be an Excel). Then I used a Recordset Destination. What it does, it stores the recordset output from a flat file (or Excel) in memory recordset, and actually you have to specify a package variable (type object) to hold this recordset. So this step provides an easy way to store my records ina global variable.

Then, I used Foreach Loop Container (Foreach ADO Enumerator) and used my object variable from a previous step as a source. I mapped my incoming 2 fields to 2 string variables.

Then, inside this container I loop through each record and assign the values of those fields to my other corresponding global variables, which I can use later anywhere in my package.

This description sounds complicated, but the whole idea and process is very simple.

Thanks for your help,

Victor.

 

[Catadmin]

Way to figure it out! Here's a star for including a detailed description.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Catadmin]

victordba,

I've been playing with your solution and have a few questions for you.

1) Do you have a finite # of records? Is that how you're able to values to other global variables?

2) What tasks do you have within the ForEachLoop Container?

3) If you don't have a finite # of records, have you discovered a way of dynamically creating package variables?

I'm thinking another solution to the problem (if a person is only looking for one record in the entire record set) is to set up a Script Task that reads the object variable, pulls the record out of the object and then assigns the appropriate values to the variables, but I haven't tested it yet. I'm still working through your solution.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[victordba]

Hi, Catadmin.

1) Yes, I know exactly how many records in excel file I have. So I had to create package variable for each record.

2) I have only 1 Script Task within the ForEachLoop Container. That's where I go through the records in this Object Variable that contains my recordset from excel, and assign the value from the record to a corresponding variable.

3) I looked everywhere to find a way to programmatically create a new variable for the package within the script, but I couldn't find it. DTS.Variables object has an ADD method, but BOL says that it's for SQL Server's internal use and not intended for us to dynamically create variables. Tecnically you can do that, but only within your VB.NET or C# application, when you load a SSIS package in your code and then you can create variables, or even new tasks.