Another For Loop Problem 2

[Skittle]

I expected this to be straight forward but I am missing something obvious. I want to read an Excel file and for each row in the Excel file perform an update on an SQL Server table.

I have a package with a single 'Data Flow Task' that on success performs a 'ForEach Loop'.

The 'Data Flow Task' seems to work fine.
I have set the 'Recordset Destination' to store the rows in a VariableName defined on the 'Component Properties' tab. The Variable is defined in my variables list as an Object with Package scope. I have limited the columns selected from the Excel sheet to two from a list of many columns.

This bit seems to work. In debug I can see the number of rows above the pleasently green 'Recordset Destination' task.

What does not work is the interaction between the 'ForEach Loop' and the 'Execute SQL Task' inside it.

ForEach Loop
I have set the Enumerator to 'Foreach ADO Enumerator'.
I have set the ADO Object Source Variable : User:MyRecordSet

Execute SQL Task
I have set the SQL server connection up ( I know this is working ).
I have set up a Direct input command of :-

INSERT INTO MySQlTable (MyColumn) VALUES(?)

I have set the Parameter Mapping to User::MyRecordSet
[/code]


The problems I have start with a failure of the SQL Statement to parse. It does not accept the '?' as a parameter and I don't know why. How should I be refering to the recordset fields within this type of SQL Statement?







Dazed and confused

 

[RiverGuy]

Create a new SSIS variable to represent your parameter. In the VariableMappings of your ForEach container, map the new variable to your ADO recordset. Then, in the ExecuteSQL task, use your new variable for the parameter.

 

[Skittle]

I'm having trouble with this.

I have right clicked the yellow background of the control flow workspace and selected variables. I have added a new
variable of type 'Object' called MyNewVariable.

After selecting 'Variable Mappings' on the 'ForEach Loop'
I have specified the variable name to User::MyNewVariable
and left the index at 0.

I have then edited the 'Parameter Mappings' on the 'Execute SQL Task' and selected the MyNewVariable as a VARCHAR.
This feels wierd to me as I'm only interested in one of the columns in the recordset so I'm not sure how t reference just the one column.

On execution the loop fails with 'MyNewVariable does not contain a valid data object'.



Dazed and confused

 

[RiverGuy]

Skittle,
When I did this, I used a String data type for the column I needed to get. So, in effect, it was taking each row and assigning that column's value to the String variable. Try changing MyNewVariable to String, leaving your recordset variable as Object.

 

[MDXer]

Your second data set can't be an object type of Object. The structure of the object variable type requires a deeper refrence than just the variable name.


The Method I use is say for example I am looping through and processing dates.

Variable 1:
Dates Object

Variable 2:
Date datetime



Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Skittle]

OK, the string variable mapping on the FOREACH loop was a major issue.

As was the fact that variable names are case sensitive.

I now have the darn thing working.

Thankyou...thankyou..thankyou...thankyou.

Dazed and confused