SSIS - 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 2005 Forums

SSIS and Import/Export (2005)

SSIS

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-09-05 : 10:36:48

Hi,

What's the difference between Control Flow and Data Flow?
I need to create a package that consists of transferring data between tables and databases. For example my first task should be called something like CreateDim and the sql statement something like:

use iib_data
drop table iibdw_2005.dbo.candidate
SELECT     Candidate.Candidate_id, Candidate.SurName  + '  ' + Candidate.Given_Names   as Candidate_Name ,
isnull(Candidate.city,'unknown in ' + Countries.Country) as city, Countries.Country,Regions_sys.Description as Region, 'Global' as Global
into iibdw_2005.dbo.Candidate
FROM         Countries INNER JOIN
                      Candidate ON Countries.Country_id = Candidate.Country_id INNER JOIN
                      Regions_sys ON Countries.Region_id = Regions_sys.Region_id

The task that must follow afterwards (Create Interviewer) will contain sql similar to the above and so on.

I have no idea where to start. Could someone please help me? Is there perhaps a good tutorial that explains how to do the above. What i found were basic tutorial with one task and one connection. I need something more complex.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-05 : 10:51:26

Maybe the MSDN tutorials would help?

http://msdn2.microsoft.com/en-us/library/ms170419.aspx

Future guru in the making.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-09-06 : 05:43:12

Control flow is concerned with stringing together a number of actions potentially in a specified order, accounting for the outcome of prior actions (precedence).
Data flow is purely focused on moving data from one or more sources to one or more destinations, most often transforming it on route.
If your requirements are simple, assuming that you're dealing solely with SQL Server, you could accomplish this with stored procedures.
Otherwise, you might want to get hold of Wrox's Expert SQL Server 2005 Integration Services (as it sounds like you're putting together the ETL to populate a dimensional model, and this book offers quite a few practical pieces of advice in this area.)

Mark

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-09-06 : 09:22:34

what you need is a single package with multiple data flow tasks. one for each extract and load.
one data flow source, transformatins if required and then one data flow destination.

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-09-11 : 08:46:28

Personally, I wouldn't go down that route, Vasu4us. I've found it far preferable to maintain separate packages for each destination object, with higher-level control packages to manage precedence between these. This gives you greater flexibility and control over testing, deployment, and to isolate any changes that you make.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources