Hi everybody,

I'm trying to make a dynamic data transfer with Integration Services.
I have a loop on many tables and for each of them I fill variables :
- SQLScript : whitch contains the SQL select script
- DestinationTableName : witch contains the name of the destination
table

In my data flow, I have an OLEDB Source and an OLEDB Destination.

In my source I set Data access mode to "SQL command from variable" and
select my SQLScript variable.
In my destination I set the Data access mode to "Table name or view
name variable" and select my DestinationTableName variable.


When I execute the package, I get an error
"Warning: 0x800470C8 at Transfer Data, OLE DB Destination [121]: The
external metadata column collection is out of synchronization with the
data source columns. The column "ISOAlphaCode" needs to be added to the
external metadata column collection."

I think it comes from the columns mapping. So I tried to set the
"ValidateExternalMetadata" property to false for Source and
Destiantion. But another error came.
"Error: 0xC0202005 at Transfer Data, OLE DB Source [1]: Column
"CountryID" cannot be found at the datasource.
Error: 0xC004701A at Transfer Data, DTS.Pipeline: component "OLE DB
Source" (1) failed the pre-execute phase and returned error code
0xC0202005."

More informations:
- I dont have the insert script with the right columns names
- The result of the select has at least the same number of columns and
the same name for them as the destination Table.

What should I do?
Should I try to make the mapping with a script task befor ? And how ?
Or should I do this differenlty?

Thanks for your answers.



Luca Schneller
Switzerland

Hello luca.schneller (AT) gmail (DOT) com,


You can certainly do what you want to a certain degree. The ExternalMetaDataColumn
collection is used to validate the incoming columns with the columns in the
input. This can be very useful when working disconnected.

In your instance it looks as though the input --> ExternalMetaDataColumn
mappings cannot find the column ISOAlphaCode.

Doing this in a script task beforehand is now no longer possible as you cannot
see outside of your own taskhost.

I am interested in your methodology though. You are looping over n tables
and populating n tables. Are they all the same structure?
Do you use many dataflow tasks to do this and call the right one at the right
time?

Simply telling the DataFlow task "Here is a source table and here is a destination
table" will not work.


Allan

Hi Allan, You understood exactly my problem and what I need to do.

I am looping over n tables using inner joins and then populating n
other tables. In my destination structure I have fewer columns then in
my select and all columns of the destination table will have an
equivalent in the select with the same name.


Exemple :
The select returns (Firstname, Lastname, Address, City, Country,
Nationality)
The destination table structure is (Firstname, Lastname, Address, City,
Country)

What I currently try to do is to make only 1 dataflow task and changing
the select query and the destination table. So I'm sad to see that it
is not possible.

Do you have a solution for this problem? Should I try to create
dynamically one dataflow per table before the execution of the package?
Do you know a good documentation that will help me to write that?

Hello luca.schneller (AT) gmail (DOT) com,


You can certainly do this.

If you do not need the extra column from the source then remove it.


Here's how I see it working for you


ForEach loop container loops over a recordset containing the tables you want
in the source(Column Names are static) and the corresponding destination
table name.
You read the name of the tables into variables
You have another variable with has its EvaluateAsExpression property set
to something like

"SELECT <column list> FROM " + @[User::SourceTableName]

You then have your OLEDB Source Adapter get its data from this variable

The destination OLEDB destination adapter is set to use the variable containing
the destination table name


Remember. If this is going to be 1 * Data Flow Task then you must not alter
the metadata. All source table --> destination table structures should be
the same.





Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com