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

For Each loop not enumerating recordset correctly Expand / Collapse
Author
Message
Posted Tuesday, July 2, 2013 8:04 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Guys,

I am importing data from a flatfile source which has a header record, some data rows and a trailer record in the format

Total Records: NNNNNNN

Where NNNNNN is a numeric value with leading zeros to pad it out to 7 digits (e.g. 0000005)

I need to get this mumeric value into the database so this is what I have done.

Set up a flatfile source to the file with a single column (WT_STR)
Conditional Split on the first 5 characters (to find the word "Total"): discard lines which don't match
Derived column to replace the "Total Rows: " with nothing: Leaving just the NNNNNN
dataconversion to convert the string to a number, currently using DT_I4
Aggregate column using SUM
Save to Recordset destination: variable = objRowCountTrailer

The recordset should have only one column and one record which is the value I am looking for: A Data viewer indicates that this is so.

In the Control flow, after the dataflow has written to the object variable I am then using a for-each loop to loop through the values of an ADO Enumerator which is set to the objRowCountTrailer. I have a variable intRowCountTrailer of datatype Int32 which I have mapped to Index 0 of the ADO record set

I am then trying to use this value to pass through to a stored procedure.

When I run the package It fails on the foreach loop and I get the following error messages


Error: ForEach Variable Mapping number 1 to variable "User::intRowCountTrailer" cannot be applied.


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


Why am I getting these error messages? I have not tried to map anyting to index 1 and the datatypes should be compatible. I am clearly missing something obvious


Post #1469524
Posted Wednesday, July 3, 2013 7:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:55 PM
Points: 918, Visits: 491
Have you tried using DT_Numeric instead of DT_I4?

Post #1470055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse