Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

For Each loop not enumerating recordset correctly Expand / Collapse
Posted Tuesday, July 2, 2013 8:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, November 14, 2016 3:09 PM
Points: 558, Visits: 879

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