EricEyster (2/24/2014)
I don't have a set of screen shots handy, but these are my normal steps:1) create a SSIS variable
2) use a SQL task to query they first database
3) use the results tab of the SQL task to put the output into the variable
4) create the second task (sql command, flow etc) with a ? as the marker for the variable
5) use the parameter tab to bind in the variable
Thnak you Eric. After my online research I did something similar to what you had suggested, but I failed. I am fairly new to SSIS (and even newer to variables). Let me elaborate on what I believe your steps are outlining:
1) Go to Variables, click Add Variable, giveit a name, Scope is Package, and Data Type is String.
2) In the Control Flow (tab) add a Execute SQL task. In the SQLStatement (General section), put the SQL that queries from the table on database 1 (server1), and add the Connection.
3) In the Parameter Mapping Add the Variable Name (User::VarName), Direction set to Output, and DataType set to NVARCHAR. The Add button is disabled under Result Set.
4) I have a Data Flow Task and the (Green) output of the Execute SQL task is going into the Data Flow Task. On the Data Flow tab, I have a OLE DB Source with the SQL that queries from the table on database 1 (server1), going into a Lookup, going into a Conditional Split, and ending up going to a OLE DB Command (table update on database 2 (server2)).
5) Not sure how or where to bind the variable (parameter tab?).
WHEELS