SSIS Execute a Stored Procedure with the parameters from .CSV file SQL Server 2005

Question

I'm learning SSIS and this seems like an easy task but I'm stuck.

I have a CSV file Orders.csv with this data:

ProductId,Quantity,CustomerId
1,1,104
2,1,105
3,2,106

I also have a stored procedure ssis_createorder that takes as input parameters: @productid int @quantity int @customerid int

What I want to do is create an SSIS package that takes the .csv file as input and calls ssis_createorder three times for each row in the .csv file (the first row contains column names).

Here is what I have done so far.

I have created an SSIS package (Visual Studio 2005 & SQL Server 2005).

In Control Flow I have a Data Flow Task.

The Data Flow has a Flat File source of my .csv file. All of of the columns are mapped.

I have created a variable named orders of type Object. I also have variables CustomerId, ProductId, & Quantity of type int32.

Next I have a Recordset Destination that is assigning the contents of the .csv file into the varialbe orders. I'm not sure about how to use this tool. I'm setting the VariableName (under Customer Properties) to User::orders. I think that now orders holds an ADO record set made up of the contents from the original .csv file.

Next I'm adding a ForEach Loop Container on the Control Flow tag and linking it to the Data Flow Task.

Inside of the ForEach Loop Container I'm setting the Enumerator to "ForEach ADO Enumerator". I'm setting "ADO object source variable" to User::orders". For Enumeration mode I'm selecting "Rows in the first table".

In the Variable Mapping tab I have User::ProductId index 0, User::Quantity index 1, User::CustomerId index 2. I'm not sure if this is correct.

Next I have a Script Task inside of the ForEach Loop Container.

I have ReadOnlyVariables set to ProductId.

In the Main method this is what I'm doing:

 Dim sProductId As String = Dts.Variables("ProductId").Value.ToString

 MsgBox("sProductId")

When I run the package my ForEach Loop Container turns Bright Red and I get the following error messages

Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::ProductId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::ProductId" cannot be applied.
Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::Quantity" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 2 to variable "User::Quantity" cannot be applied.
Error: 0xC001F009 at MasterTest: The type of the value being assigned to variable "User::CustomerId" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 3 to variable "User::CustomerId" cannot be applied.
Warning: 0x80019002 at MasterTest: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (12) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
     Dts.TaskResult = Dts.Results.Success

Any help would be appreciated

Answer 1

One of my coworkers just give me the answer.

You don't need the the ForEach Loop Container or the RecordSet Container.

All you need is the Flat File Source and an OLE DB Command. Connect to your database and inside the OLE DB Command select the appropriate connection.

In the Component Properties enter the following SQLCommand:

exec ssis_createorder ?, ?, ? 

The "?" are place holders for the parameters.

Next under the Column Mappings tab map the .csv file columns to the stored procedure parameters.

You are finished go ahead and run the package.

Thanks Gary if you were on StackOverFlow I would give you an upvote and accept your answer.

Answer 2

If I understand correctly, what you want to do is execute a stored procedure 3 times for each row in the data source.

What if you just create a data flow with a flat file data source and pipe the data through 3 execute sql command tasks? Just map the columns in the data to the input params of your stored procedure.

Maybe I'm not seeing it correctly in your question and I'm thinking too simple, but in my experience you need to avoid using the foreach task in SSIS as much as possible.

Answer 3

I suspect that you need to look at your Data Flow task. It's likely that the values from the source CSV file are being interpreted as string values. You will probably need a Derived Column component or a Data Conversion component to convert your input values to the desired data type.

And, I think @StephaneT's solution would be good for executing the SP.

Answer 4

I'm not sure if this answers your question. But I was looking to do this and I achieved it using the BULK INSERT command. I created a staging table with all of the columns in the csv file, and instead of a stored procedure I used a INSTEAD OF INSERT trigger to handle the logic of inserting it into many tables.