Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Update table based on the resultset of a SQL Statement


SSIS Update table based on the resultset of a SQL Statement

Author
Message
WheelsGuy
WheelsGuy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2681 Visits: 1657
Try setting the ParameterName field in the ParameterMapping screen to 0.

Regards
Lempster
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7357 Visits: 15079
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
WheelsGuy
WheelsGuy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
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
------------------------------
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7357 Visits: 15079
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
WheelsGuy
WheelsGuy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
John Mitchell-245523 (3/6/2014)
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


Hi John. I am not sure what you mean by result set contains only one row. The query resuls returned by the 1st Execute SQL Task may return 1 to 100 items with commas added to be used in the IN statement for the 2nd Execute (update) SQL Task. I figured the Foreach would be used to concatenate the result set and save it as a string in the variable.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7357 Visits: 15079
Let me check that I understand. Your first task contains a query like
SELECT ConcatenatedList
FROM SourceTable



from which the result set is
ConcatenatedList
----------------
1,2,3,4,5,6,7,8


Your second task then uses that result set thus:
UPDATE DestinationTable
SET Something = 'Something'
WHERE SomethingElse IN (1,2,3,4,5,6,7,8)



If I've got that right, then the best thing to do is as I suggested. (Parameter placeholder may be different depending on which kind of connection manager you use.)
SELECT ? = ConcatenatedList
FROM SourceTable


and set the ResultSet property to No Result Set. In the Parameters page, output to a variable called CList. For the next task, do away with the loop container and have just the Execute SQL task. Use an expression to set the query - something like this:
"-- **** Expression ****
UPDATE DestinationTable
SET Something = 'Something'
WHERE SomethingElse IN (" + @[User::CList] + ")"


Does that make sense? It's done from memory, so if I've got any syntax, property names etc wrong, I apologise.

John
WheelsGuy
WheelsGuy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
In the 1st Execute SQL Task, the resultset is something like this:

Item
----
"Item1",
"Item2",
"Item3",
"Item4"

My version of SQL Server does not allow for the Concatenate function, so I figured I need to loop through the result set with the Foreach to build the string variable (like StringBuilder in C#) for the IN statement.

WHEELS
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7357 Visits: 15079
In that case, I think I wouid use a Data Flow to dump that result set into a staging table, and then use a MERGE or UPDATE statement in an Execute SQL task to do the processing. It's simpler, and more importantly, it processes the result as a set instead of one row at a time.

John
WheelsGuy
WheelsGuy
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 24
Thank you. I am on vacation this week, but 1st thing on Monday, I will try to implement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search