Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SSIS Update table based on the resultset of a SQL Statement Expand / Collapse
Author
Message
Posted Saturday, March 1, 2014 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 10:11 AM
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



Post #1546659
Posted Monday, March 3, 2014 3:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:37 AM
Points: 2,127, Visits: 1,485
Try setting the ParameterName field in the ParameterMapping screen to 0.

Regards
Lempster
Post #1546835
Posted Monday, March 3, 2014 4:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1546840
Posted Wednesday, March 5, 2014 1:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 10:11 AM
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
------------------------------
Post #1547987
Posted Thursday, March 6, 2014 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1548111
Posted Thursday, March 6, 2014 8:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 10:11 AM
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.
Post #1548330
Posted Thursday, March 6, 2014 9:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1548341
Posted Thursday, March 6, 2014 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 10:11 AM
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
Post #1548356
Posted Thursday, March 6, 2014 9:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
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
Post #1548361
Posted Thursday, March 6, 2014 10:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 10:11 AM
Points: 12, Visits: 24
Thank you. I am on vacation this week, but 1st thing on Monday, I will try to implement.
Post #1548373
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse