SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Package Design Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LearningSQLKid
Starting Member

Hong Kong
46 Posts

Posted - 11/14/2012 :  20:28:17  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/15/2012 :  10:35:54  Show Profile  Reply with Quote
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/

Go to Top of Page

LearningSQLKid
Starting Member

Hong Kong
46 Posts

Posted - 11/15/2012 :  16:13:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/15/2012 :  22:24:57  Show Profile  Reply with Quote
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/

Go to Top of Page

LearningSQLKid
Starting Member

Hong Kong
46 Posts

Posted - 11/19/2012 :  22:53:19  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/20/2012 :  02:51:27  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000