SSIS multiple table loads using single DFT

Question

Post reply

SSIS multiple table loads using single DFT

vipin_jha123

Hall of Fame

Points: 3085
More actions
December 11, 2014 at 2:55 am

#293392

Hi,
I am new to the SSIS. I am trying to load multiple tables from my staging database to multiple tables in the data warehouse. My staging database is nothing but a database with all the tables from all my source databases copied to it. I found that each data flow task only allows to connect one source table to one destination table.
I am having 100 of SSIS package of 100 Tables.
I want to make single SSIS package to laod all the 100 tables usning single DFT.
I have a filmaster table which containing the list of source table and destination table name, and input will be the filename , on the basis of filename my DFT will take the source and derstination table and run the package accordingly.
Is this approach is correct, or is it possible, is dynamically column mapping is possible.
Please suggets some ideas on above concern
Regards,
Vipin Jha

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

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

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

IMO, your suggested approach is not optimal.

A better model is one package per table, with some sort of controller/master package which executes them all.

If your loading methodology is similar for all tables, consider auto-generating the packages using BIML.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

Hi ,

Thanks for your suggestion about BIML.

Can I load the source and destination table name using BIML Dynamically ?

Regards,

Vipin jha

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

vipin_jha123 (12/15/2014)
Hi ,
Thanks for your suggestion about BIML.
Can I load the source and destination table name using BIML Dynamically ?
Regards,
Vipin jha

BIML generates packages, it does not load anything.

BIMLScript might be able to generate those packages, if you feed it the list of table names.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 4

Hi ,

My Concern is , is single DFT will take the Table name in both Source as well as destination dynamically and is dynamic column mapping is possible in OLEDB Destination ?

Regards,

Vipin Jha

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

vipin_jha123 (12/17/2014)
Hi ,
My Concern is , is single DFT will take the Table name in both Source as well as destination dynamically and is dynamic column mapping is possible in OLEDB Destination ?
Regards,
Vipin Jha

No.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 6

Hi ,

I have created simple BIML Script file which generating a SSIS Package.

It Contanis Single Data Flow Task ,OLEDB Source and OLEDB Destination.

Now my Concern is that my new SSIS package getting input from Master package , on the basis of that my new SSIS Package will be function.

Now I don't know how to schedule the BIML Script file.

My package flow will be like

1-Master Package:-It will pass the input which is the tablename to the New SSIS package

2-Biml Script:-It will create SSIS package on fly

3-New SSIS Package:-It will accept the filename from the Master package and change the Source table and destination table accordingly.

please suggest me the best solution.

Thanks & Regards,

Vipin jha

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

vipin_jha123 (12/18/2014)
Hi ,
I have created simple BIML Script file which generating a SSIS Package.
It Contanis Single Data Flow Task ,OLEDB Source and OLEDB Destination.
Now my Concern is that my new SSIS package getting input from Master package , on the basis of that my new SSIS Package will be function.
Now I don't know how to schedule the BIML Script file.
My package flow will be like
1-Master Package:-It will pass the input which is the tablename to the New SSIS package
2-Biml Script:-It will create SSIS package on fly
3-New SSIS Package:-It will accept the filename from the Master package and change the Source table and destination table accordingly.
please suggest me the best solution.
Thanks & Regards,
Vipin jha

BIML script can (in theory) create the packages, but not "on the fly".

Your master package will simply run these generated packages.

There is nothing dynamic about this solution. The packages are generated at design time, not at run time.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 8

Thanks Phil,

I was in search of a way where I can schedule a BIML file to generate SSIS package.

Would it be possible ?

Phil Parkin SSC Guru Points: 246978 More actions · Answer 9

vipin_jha123 (12/18/2014)
Thanks Phil,
I was in search of a way where I can schedule a BIML file to generate SSIS package.
Would it be possible ?

Possible? Perhaps, though I don't think many people have tried. I've only ever seen people use the BIDS Helper GUI.

Have a look here for some ideas.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 10

Hi all, I am trying to automate a data flow with BIML. I am using an expression to build my SQL dynamically based on input parameter. Sparing details of the use case, I need this flexibility in my project. I am having 1 master table which consists of file name and source query.

I want to use SQL Command from Variable Data access mode in OLEDB Source.

On the basis of input file source query need to change automatically .However; my query is not being evaluated when the package is generated. The query will populate after package generation, when I open the source and set access mode SQL Command, but I cannot seem to get this configured automatically as desired. This is preventing me from doing transformations in the script.

Please help me to achieve the above scenario

Below is my BIML Script.

</Connections>

<Variable Name="V_Archivequery" DataType="String" EvaluateAsExpression="true">SELECT a.*, b.BBxKey as Archive_BBxKey, b.RowChecksum as Archive_RowChecksum FROM dbo.ImportBBxFbapp a LEFT OUTER JOIN Archive.dbo.ArchiveBBxFbapp b ON a.Col001 = b.BBxKey Where (b.LatestVersion = 1 OR b.LatestVersion IS NULL)

</Variable>

<Variable Name="v_Src_FileName" DataType="String" >FBAPP</Variable>

<Tasks>

</OleDbSource>

</Transformations>

</Dataflow>

</Tasks>

</Container>

</Tasks>

</Package>

</Packages>

</Biml>

Regards,

Vipin jha