July 10, 2008 at 11:18 am
Before we get into code, here is what I am attempting to accomplish, so if you have a better idea of how to get this done, we don't just go down the road of how to fix a piece of code or how to align boxes in an IDE. OK?
OK.
Let's say I want to import an excel spreadsheet into a SQL Server 2005 table. Easy to do.
But - I want to load the contents of that sheet for a particular customer ID and location ID (combination), which are NOT included anywhere on the sheet.
So, you say, "create vars for the customerID and locationID, type in the values, and run your import. Get over yourself".
Not so fast.
I want SSIS to do some work for me. . .
The spreadsheet DOES contain proxy values (user supplied values, not the ones used by our data system) that I can use via a crosswalk/lookup table to find the actual customerID and locationID values needed for the import job.
So, I want to write a query that returns those "real" values to me.
That's easy.
The hard part is figuring out how to get my query results to map to a pair of variables so I can use them for additional SSIS processing.
For example, I'd like to have a vars such as: varCompanyID and varLocationID that I can use throughout the package. But, so far nothing has helped me much.
Does anyone have a detailed explanation of how I might go about this?
My best guess so far is to create an execute SQL task that contains my query.
Set the Result Set to "single row" and then do something with the result set - but what?
Folks have written to set the result set to a variable having the "object" datatype, but I don't seem to be able to reference it any further once that setting has been made -- which is why I am asking a question on my own, that some folks may believe has been asked/answered before. It's just not clear to me at this point.
I'd appreciate any assistance on this.
Thanks,
David
July 10, 2008 at 1:48 pm
Returning results from a query in SSIS and putting them into variables is pretty simple, so either your description was not great, or you missed something a bit obvious.
1) Use an Execute SQL Task to run your query
2) Set the ResultSet to Single Row (I assume your query returns one and only one row)
3) In the ResultSet group in the Execute SQL Task editor, add a new result set
4) Set the "Result Name" to 0 and set the variable name to the variable you want to set the first COLUMN in your result set to
5) Set the "Result Name" to 1 and set the variable name to the variable you want to set the second COLUMN in your result set to
Your variables must be of the same type as the returned values, so if your query returns an integer column and a varchar column, you need an integer variable and a string variable.
That's pretty much it.
July 10, 2008 at 4:18 pm
Just needed to dump those training wheels. I missed the paradigm about how to return n number of vars for n number of columns on my first several tries. When reading other posts, I believe other folks were trying to return an entire dataset and load the results into an ADO Recordset, which kind of threw me off when trying to just get one measley row. Issue resolved.
Thank you very much for the assistance,
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply