title: SSIS Execute a Stored Procedure with the parameters from .CSV file SQL Server 2005
body: <p>I'm learning SSIS and this seems like an easy task but I'm stuck. </p>
  
  <p>I have a CSV file Orders.csv with this data:</p>
  
  <pre><code>ProductId,Quantity,CustomerId
  1,1,104
  2,1,105
  3,2,106
  </code></pre>
  
  <p>I also have a stored procedure ssis_createorder that takes as input parameters:
  @productid int
  @quantity int
  @customerid int</p>
  
  <p>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). </p>
  
  <p>Here is what I have done so far. </p>
  
  <p>I have created an SSIS package (Visual Studio 2005 &amp; SQL Server 2005). </p>
  
  <p>In Control Flow I have a Data Flow Task. </p>
  
  <p>The Data Flow has a Flat File source of my .csv file. All of of the columns are mapped. </p>
  
  <p>I have created a variable named orders of type Object. I also have variables CustomerId, ProductId, &amp; Quantity of type int32. </p>
  
  <p>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. </p>
  
  <p>Next I'm adding a ForEach Loop Container on the Control Flow tag and linking it to the Data Flow Task. </p>
  
  <p>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". </p>
  
  <p>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. </p>
  
  <p>Next I have a Script Task inside of the ForEach Loop Container. </p>
  
  <p>I have ReadOnlyVariables set to ProductId. </p>
  
  <p>In the Main method this is what I'm doing:</p>
  
  <pre><code> Dim sProductId As String = Dts.Variables("ProductId").Value.ToString
  
   MsgBox("sProductId")
  </code></pre>
  
  <p>When I run the package my ForEach Loop Container turns Bright Red and I get the following error messages</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any help would be appreciated</p>