February 24, 2014 at 1:39 pm
Good afternoon.
I have two different databases (on two different servers) and I would like to use SSIS to update a table on server 2 / database 2 with the resultset from a SQL statement from server 1 / database 1. The result set from the query will be used in the IN clause of a WHERE statement.
I have a OLE DB Source to a Lookup to a Conditional Split to a OLE DB Command (upddate), but I am having trouble getting it to work. Could someone tell me what components I should be using, and in what order? I read online to use a For Each to create a string variable of the resultset.
Thank you, WHEELS
February 24, 2014 at 5:15 pm
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
February 25, 2014 at 8:10 am
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
February 25, 2014 at 8:22 am
WHEELS,
I hve BIDS open to refresh my memory on this. On task 1, the select from the source, look for ResultSet and select "single row". This will enable the Result Set section. Parameter mapping = output would only be used if you have a stored procedure with and output parameter, which is not the case here.
February 25, 2014 at 8:43 am
Getting closer. I set ResultSet to Single row, and I was able to add the variable in the Result Set section. In the Data Flow (OLE DB SOurce) I put SQL command from variable in the Data access mode, and selected the variable in the Variable name drop-down. When I click Preview or OK, I get Error at Data Flow Task: Command text was not set for the command object.
February 25, 2014 at 8:51 am
close.
the statement from variable would be used if you have a script task that assembles the full query text. That is not the case here. Use "Direct Input" and paste the select into the SQLStatement block. It should look like
select *
from test
where c1 = ?
next, go to parameter mappings and add a new param, pick you variable and data type.
February 25, 2014 at 8:58 am
EricEyster (2/25/2014)
close.the statement from variable would be used if you have a script task that assembles the full query text. That is not the case here. Use "Direct Input" and paste the select into the SQLStatement block. It should look like
select *
from test
where c1 = ?
next, go to parameter mappings and add a new param, pick you variable and data type.
I'm a bit confused (too much multitasking). In the Execute SQL task, I have Direct input, and the SQLStatement I need to grab the variable data. Would the example SQL you listed be in the OLE DB Source of the Data Flow or somewhere else?
February 25, 2014 at 9:04 am
WheelsGuy (2/25/2014)
EricEyster (2/25/2014)
close.the statement from variable would be used if you have a script task that assembles the full query text. That is not the case here. Use "Direct Input" and paste the select into the SQLStatement block. It should look like
select *
from test
where c1 = ?
next, go to parameter mappings and add a new param, pick you variable and data type.
I'm a bit confused (too much multitasking). In the Execute SQL task, I have Direct input, and the SQLStatement I need to grab the variable data. Would the example SQL you listed be in the OLE DB Source of the Data Flow or somewhere else?
Sorry, multitasking myself.
Your second step: is it an update statement or a data flow? If you are updating or inserting a record on the second system, you can use a ExecuteSQL task. Data flow would be for pulling many records to the second system.
February 25, 2014 at 10:41 am
EricEyster (2/25/2014)
WheelsGuy (2/25/2014)
EricEyster (2/25/2014)
close.the statement from variable would be used if you have a script task that assembles the full query text. That is not the case here. Use "Direct Input" and paste the select into the SQLStatement block. It should look like
select *
from test
where c1 = ?
next, go to parameter mappings and add a new param, pick you variable and data type.
I'm a bit confused (too much multitasking). In the Execute SQL task, I have Direct input, and the SQLStatement I need to grab the variable data. Would the example SQL you listed be in the OLE DB Source of the Data Flow or somewhere else?
Sorry, multitasking myself.
Your second step: is it an update statement or a data flow? If you are updating or inserting a record on the second system, you can use a ExecuteSQL task. Data flow would be for pulling many records to the second system.
My second step is a Data Flow Task on the Control Flow tab. Then on the Data Flow tab it is a OLE DB Source. Just looking to do an update. Are you stating that I can simply use two Execute SQL tasks on the Control Flow tab?
February 25, 2014 at 11:07 am
Yes, SSIS does not have any requirements as far as what tasks you use. We have a few packages that do nothing but call stored procedures and run VB script tasks. It was just easier to wrap them in a package than compiling an executable.
If you are going to run an update on the second DB, write a simple stored procedure with a parmater. It will be easier to maintain down the road and not require any changes to the package.
March 1, 2014 at 12:10 pm
I have two Execute SQL tasks. The one for populating the variable. I have the variable in the ResultSet and the settign is Single Row. THe second Execute SQL task is for the update. I put the User::variable name in the variable name field, Input in the direction field, VARCHAR in the Data Type field, and the variable name in the Parameter Name field. I have the ResultSet set to none, but it fails no matter what ResultSet I put in.
I am getting the followiing error: [Execute SQL Task] Error: Executing the query "update Table
set DataField =..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Where am I going wrong?
WHEELS
March 3, 2014 at 3:35 am
Try setting the ParameterName field in the ParameterMapping screen to 0.
Regards
Lempster
March 3, 2014 at 4:00 am
When you assign a result set to a variable, you're using a special type of variable (I think the type is called Object) that can hold a result set. All other types in SSIS are scalar - they only hold a single value. You're mixing your variables. If you want to save your result set to a variable, you will then need to have a task that loops through the rows in the result set (even if there's only one). But if you use output variables in the parameter mapping tab instead, you can use those values as inputs to your next Execute SQL task.
I hope that makes sense.
John
March 5, 2014 at 1:02 pm
I have the 1st Execute SQL Task pulling a list of items (separated by commas). I have the resultSet set to Single Row, 0 in the Result Name, and the variable in the Variable Name. The output goes into a For Each Container, the Enumerator is set to Foreach ADO Enumerator. The ADO object source varaiable is the item, Rows in the first table is selected, I put the varaiable in the Variable Mapping with a 0 index, and in the expression under Foreach emulator the variable name is listed. The output goes into the secong Execute SQL Task (all in the Control Flow). In the parameter mapping I set the variable name, Input, VARCHAR, parameter name (should it be 0 instead?), and -1. I am receiving an error:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Foreach Loop Container: The result of the expression "@[User::MyItem]" on property "ForEachEnumerator" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error at Data Flow Task [OLE DB Command [1331]]: Parameters are not bound. All parameters in the SQL command must be bound to input columns.
Error at Data Flow Task [SSIS.Pipeline]: "component "OLE DB Command" (1331)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
March 6, 2014 at 2:09 am
You're making things too complicated. Your result set contains only one row, so change the result set to No Result Set and use output parameters in the Parameter Mapping page instead. Then you can use the variables in your next Execute SQL task without the need for a For Each Loop container.
John
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply