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