SSIS Package Design Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Package Design Help

Author

Topic

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2012-11-14 : 20:28:17

Hi Guys

I need your help in desigining an ETL package Using SSIS. I need to find out an efficient way of doing the following

1- I have Excel file contains list of tables along with source and destination server and databse names which should be loaded to database

Fo instance

SrNo TableName SRCServer SRCDB DESServer DesDB
---------------------------------------------------
1 Table_1 Svr_1 DB_1 Srv_2 DB_2
2 Table_2 Svr_1 DB_1 Srv_2 DB_2
3 Table_3 Svr_1 DB_1 Srv_2 DB_2

2- I need to pick each table from the file one by one to load data into table (to destination)

3- Table names in source and destination are the same

Any idea, how would i dynmically do it ?

Appreciate help and quick reply

Thanks

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 10:35:54

yep

Create a variable of type object in SSIS package

Add a data flow task with Excel Source to point to Excel file and ADO Recordset destination to point to object variable.

Then use a For Each Loop to loop through ADO .NET recordset and then use individual variable to hold servername,dbname etc. If tables are all the same structure then you can use same data flow for data transfer else you need to use separate data flow one for each table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2012-11-15 : 16:13:37

quote:
Originally posted by visakh16

yep

Create a variable of type object in SSIS package

Add a data flow task with Excel Source to point to Excel file and ADO Recordset destination to point to object variable.

Then use a For Each Loop to loop through ADO .NET recordset and then use individual variable to hold servername,dbname etc. If tables are all the same structure then you can use same data flow for data transfer else you need to use separate data flow one for each table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Visak

Thanks for you reply.
Is it possible to make this overall process a dynamic process. For instance if i update Excel file with new tables and then i would have to add a new data flow task to the solution (not so much of work), any thoughts around making it dynamic so that the support gusy update the file and let the SSIS package do the rest ?

Thanks Heaps

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 22:24:57

quote:
Originally posted by LearningSQLKid

quote:
Originally posted by visakh16

yep

Create a variable of type object in SSIS package

Add a data flow task with Excel Source to point to Excel file and ADO Recordset destination to point to object variable.

Then use a For Each Loop to loop through ADO .NET recordset and then use individual variable to hold servername,dbname etc. If tables are all the same structure then you can use same data flow for data transfer else you need to use separate data flow one for each table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Visak

Thanks for you reply.
Is it possible to make this overall process a dynamic process. For instance if i update Excel file with new tables and then i would have to add a new data flow task to the solution (not so much of work), any thoughts around making it dynamic so that the support gusy update the file and let the SSIS package do the rest ?

Thanks Heaps

Select Knowledge from LearningProcess

Its not a problem adding new rows to excel so far as the metadata (columns) does not change.

But for actual data flow you've to have as many data flow paths as tables so long as tables have different structure.
SSIS cant change metadata at runtime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2012-11-19 : 22:53:19

Thanks visakh

If Dat flow task could have changed the meta data dynamically , it would have made SSIS more strong.

Thanks for you help my friend :)

Cheers

Select Knowledge from LearningProcess

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 02:51:27

quote:
Originally posted by LearningSQLKid

Thanks visakh

If Dat flow task could have changed the meta data dynamically , it would have made SSIS more strong.

Thanks for you help my friend :)

Cheers

Select Knowledge from LearningProcess

Thats against the basic ETL assumption which assumes source to be fixed. Any changes that have to done has to be preset by means of transformations in data flow and it cant vary at runtime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources