Dynamic incremental load in SSIS

Question

Dynamic incremental load in SSIS

mitra.itha

SSC-Addicted

Points: 463
More actions
May 3, 2016 at 3:12 am

#310330

Hi All,
I have an urgent requirement in SSIS Package , I want to get the table list from a configuration table and that table's data has to be moved to source to destination.
For Example:
I have 10 tables this tables data i need to move from source to destination,without hard coding the table names we need to move data is this possible in SSIS package.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

mitra.itha (5/3/2016)
Hi All,
I have an urgent requirement in SSIS Package , I want to get the table list from a configuration table and that table's data has to be moved to source to destination.
For Example:
I have 10 tables this tables data i need to move from source to destination,without hard coding the table names we need to move data is this possible in SSIS package.

This is not supported natively in SSIS, which needs to know column names and data types at design time, rather than run time.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Smendle Hall of Fame Points: 3969 More actions · Answer 2

You can do this within SSIS. Its a populate SQL from variable, while the column names don't have to be the same I do believe the number and data type per ordinal position do matter (otherwise you may have to go through a data conversion step)

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

Smendle (5/3/2016)
You can do this within SSIS. Its a populate SQL from variable, while the column names don't have to be the same I do believe the number and data type per ordinal position do matter (otherwise you may have to go through a data conversion step)

Please expand on what you are suggesting, because I don't get it.

Assume

SELECT * FROM T1

is your source, in DB1 on server 1.

How are you going to incrementally load this to table T2, in database DB2, on server 2? You don't even know what the PK is, so how will you code the UPSERTs? How will you know which data to select in the first place? (This is incremental, so by definition not a full reload.)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Smendle Hall of Fame Points: 3969 More actions · Answer 4

Phil Parkin (5/3/2016)
Smendle (5/3/2016)
You can do this within SSIS. Its a populate SQL from variable, while the column names don't have to be the same I do believe the number and data type per ordinal position do matter (otherwise you may have to go through a data conversion step)
Please expand on what you are suggesting, because I don't get it.
Assume
SELECT * FROM T1
is your source, in DB1 on server 1.
How are you going to incrementally load this to table T2, in database DB2, on server 2? You don't even know what the PK is, so how will you code the UPSERTs? How will you know which data to select in the first place? (This is incremental, so by definition not a full reload.)

It doesn't work from SELECT * but the populate table from SQL variable does work with

SELECT col1,col2,col3,col4... FROM T1

Where you define the number output columns(col1,col2,col3,col4) and then at runtime you can create a different SQL to populate those 4 columns with completely different column names.

SELECT 'Car','Boat','MotorCycle','Scooter' FROM T1 This will work in the populate from Variable in the SSIS package as long as your 4 columns are VARCHAR based.

Again if you need to you could pump the output to a data conversion step to handle things like '10005' needing to be a number etc....

While the number of columns is a limitation its not a stretch to create separate package paths to handle differing column total ordinal positions.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

Smendle (5/3/2016)
Phil Parkin (5/3/2016)
Smendle (5/3/2016)
You can do this within SSIS. Its a populate SQL from variable, while the column names don't have to be the same I do believe the number and data type per ordinal position do matter (otherwise you may have to go through a data conversion step)
Please expand on what you are suggesting, because I don't get it.
Assume
SELECT * FROM T1
is your source, in DB1 on server 1.
How are you going to incrementally load this to table T2, in database DB2, on server 2? You don't even know what the PK is, so how will you code the UPSERTs? How will you know which data to select in the first place? (This is incremental, so by definition not a full reload.)
It doesn't work from SELECT * but the populate table from SQL variable does work with
SELECT col1,col2,col3,col4... FROM T1
Where you define the number output columns(col1,col2,col3,col4) and then at runtime you can create a different SQL to populate those 4 columns with completely different column names.
SELECT 'Car','Boat','MotorCycle','Scooter' FROM T1 This will work in the populate from Variable in the SSIS package as long as your 4 columns are VARCHAR based.
Again if you need to you could pump the output to a data conversion step to handle things like '10005' needing to be a number etc....
While the number of columns is a limitation its not a stretch to create separate package paths to handle differing column total ordinal positions.

OK. This relies on the number of columns being known in advance and the data types also being known.

SELECT 'Car','Boat','MotorCycle','Scooter' FROM T1

This returns a row of data containing the literals (car, boat, motorcycle, scooter), repeated for each row in T1 – why would you ever do this?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Smendle Hall of Fame Points: 3969 More actions · Answer 6

OK. This relies on the number of columns being known in advance and the data types also being known.
SELECT 'Car','Boat','MotorCycle','Scooter' FROM T1
This returns a row of data containing the literals (car, boat, motorcycle, scooter), repeated for each row in T1 – why would you ever do this?

It was just an example of saying ANYTHING could work for populating the SQL from a variable.

We use this exact technique to determine DBA jobs that an SSIS package will run having differing paths for the variable column ordinal positions. Again this is a limitation of completely dynamic SQL but it CAN be done.

I should have ask the OP. Does your configuration table always pull the same number of columns?

Phil Parkin SSC Guru Points: 246695 More actions · Answer 7

Smendle (5/3/2016)
OK. This relies on the number of columns being known in advance and the data types also being known.
SELECT 'Car','Boat','MotorCycle','Scooter' FROM T1
This returns a row of data containing the literals (car, boat, motorcycle, scooter), repeated for each row in T1 – why would you ever do this?
It was just an example of saying ANYTHING could work for populating the SQL from a variable.
We use this exact technique to determine DBA jobs that an SSIS package will run having differing paths for the variable column ordinal positions. Again this is a limitation of completely dynamic SQL but it CAN be done.
I should have ask the OP. Does your configuration table always pull the same number of columns?

This is from the original post:

I want to get the table list from a configuration table and that table's data has to be moved to source to destination.

When I read this, I understood that the config table contains a list of the names of tables to be transferred.

Perhaps you interpreted it differently.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Smendle Hall of Fame Points: 3969 More actions · Answer 8

I took it to meant a configuration table with column names, however you are correct if its table names and the underlying column count can not be known ahead of time it would be difficult to implement what I suggest....

Instead I would just use the Transfer Database Object setting it up for table only and passing it the name from the configuration table.

mitra.itha SSC-Addicted Points: 463 More actions · Answer 9

Yes, I can get table name, column name(s), and column count ahead of the SSIS execution.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 10

Perhaps this would be of interest. Note that I have no connection with this company.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

mitra.itha SSC-Addicted Points: 463 More actions · Answer 11

Yes, I can get table name, column name(s), and column count ahead of the SSIS execution.