How to adjust variable type in a recordset through a foreach loop container using SSIS

3
votes

I got stuck with SSIS. When I try to use a foreach container to loop through one recordset I extract from an Excel file.

Enter image description here

Here are two variables I used:

Enter image description here

The first thing I do is to extract data from an Excel file, then store it into variable "CriteriaResult" as a recordset.

Enter image description here

Enter image description here

Then in the foreach loop, I use an ADO Enumerator to access this recordset:

Enter image description here

Each record in this recordset will be converted to a string variable "CriteriaID":

Enter image description here

Then I will insert this record to a table in SQL Server:

Enter image description here

The parameter setting is the same as the column's data type in the SQL Server table:

Enter image description here

However, I got an error at the foreach loop step:

Enter image description here

Error: 0xC001F009 at Get excel data: The type of the value being assigned to variable "User::CriteriaID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

The raw data in the Excel file is like this:

Enter image description here

It starts with number 1 and 2, then change to 3A, 3B.... I guess that's why it claims the data type changes at a foreach loop step. Could I add any script task to format data within the recordset before looping through it?

The other potential issue could come from the merging cells. However, I tried a single cell before, and it works well.

How can I fix this problem?

1
sql-serverexcelssisetl
I can't publish image due to new user limitation, could anyone please help me edit it? All you need to do is add a "!" in front of image link, thanks. - Verse He
I fixed it! Also it is recommended to read the Tour Page to learn more about asking good questions, accepting answers and other website rules. Also to get your the "informed" badge. - Hadi
Did you try my answer? - Hadi
Yes, Hadi. Unfortunately the error still shows up. It's even not goes to the insert part but just stop at the foreach loop, I have edited my question to make it more clearly with the flow chart. - Verse He
Your data is in column and your sql points to column x. - KeithL

1 Answers

1
votes

Try using expressions instead of passing parameters:

  • Don't specify any parameter
  • Go to the expression tab → SqlStatement property

  • Use the following expression:

    "INSERT INTO dbo.SSIS (CriteriaID) VALUES(" + @[User::CriteriaID] + ")"