June 23, 2010 at 1:12 pm
I've got an SSIS package that extracts data from a table. Each record has a SourceTable, DestinationTable column, and a Column name used as a filter called FilterColumn.
With this information, I build expressions to load all of the SourceTables into the Destination tables... filtering out any records where the FilterColumn value on the Source is larger than that of the Destination.
The package looks as follows ...
Variables: DestTableName as String (has valid default value),
SourceTableName as String (has valid default value),
FilterColumn as String (has valid default value),
MaxIDQuery as String (has valid default value, evaluates as expression, is an expression that selects max(FilterColumn) from DestinationTable),
SourceQry as String (has valid default value, evaluates as expression, is an expression turning SourceTableName into a select query)
1. Execute SQL Task ("Get ETL Info"): Returns Result Set with SourceTableName and DestinationTableName mapped to a resultset variable.
2. Foreach Loop Container: Using ForEachADo, mapping SourceTableName variable to Index 0 and DestinationTableNAme to Index 1.
3. Data Flow Task
3.a. Source: OLE DB Source Connection, SQL Command from Variable.
3.b. Destination: OLE DB Destination, Table name or view name variable - fast load, Variable Name for destination.
The columns/types between the Source and Destination tables are the same.
When the ForEachLoop goes to the next record, I get a metadata error:
Column "MT_ID" cannot be found at the datasource.
MT_ID is the first column of the OLE DB Source that I configured.
I used SQL Profiler to trace the activity and saw the correct query was executing on the Source.
It seems as thought the metadata is not matching the results of the query and therefore erroring.
Any ideas on what I should do to fix this?
June 23, 2010 at 1:59 pm
Robert Biddle (6/23/2010)
1. Execute SQL Task ("Get ETL Info"): Returns Result Set with SourceTableName and DestinationTableName mapped to a resultset variable.
I think in the 1st step, we should use Data Flow Task to populate the recordset variable.
Please go through this article on shredding a recordset and let me know if it helped:
http://www.sqlis.com/sqlis/post/Shredding-a-Recordset.aspx
------------------
Why not ?
June 23, 2010 at 2:28 pm
In a nutshell, I'm already doing that. I'm passing a recordset object to my ForEachLoop. The ForEachLoop processes each record and assigns them to a variable.
Edit:
From further research the problem that I've found is that you can't change your metadata in the loop. The metadata initialized when the OLE DB Source and Destination have to remain the same.
I partly can understand this... however I think they should have some sort of support for updating metadata of Source/Destination task at runtime :-/
June 23, 2010 at 3:35 pm
Robert Biddle (6/23/2010)
In a nutshell, I'm already doing that. I'm passing a recordset object to my ForEachLoop. The ForEachLoop processes each record and assigns them to a variable.Edit:
From further research the problem that I've found is that you can't change your metadata in the loop. The metadata initialized when the OLE DB Source and Destination have to remain the same.
I partly can understand this... however I think they should have some sort of support for updating metadata of Source/Destination task at runtime :-/
As I read your post I was guessing your layouts changed from table to table, and no the meta-data and mapping CANNOT change with any of the MS provided components. I *believe* that CozyRoc has a component that *may* do wht you what, I don't know for sure I haven't used it..
Having the metadata change at run-time is pretty tricky for anything other than a fairly straightforward copy.. Which it sounds like you are doing..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply