SQL Server Integration Services - Wow.. this is all new to me..

Launching it opened a copy of Visual Studio 2008. I had to create a “New Project”, of type “Integration Services Project”. Much the same of creating a Windows or Web Application.

Instantly after creating – I had a completely new Toolbox – with three sections:

1, Data Flow Sources
	These are the different ways to get the “data” to use in the transformation process. For example here are some available to me: “ADO .Net Source”, “OLE DB Source”, “XML Source” and “Excel Source” to name a few.
2, Data Flow Transformations
	These are the different ways to manipulate the “data” before you finish with it. For example: “Copy Column” – pretty straight forward.. same as DTS in previous versions “Lookup” – another which seems pretty obvious. “Merge” – this is a new one introduced in SQL 2008. It requires 2 different Source Inputs (Data Sources) and outputs 1 set of Data.   (this is the one I’m attempting to work with now – its not a very configurable object..very limited in options)
And finally,
3, Data Flow Destinations
	These are the different ways to save/output the “data” after the transformation process. Most are the same connection type as the Source. Some examples are: “ADO .Net Destination”, “Excel Destination”, “SQL Server Destination” – but there are few new ones – “Dimension Processing”, “Data Mining Model Training”, “Data Reader Destination” and “Partition Processing”. There are more – but I’ll limit my list here. Some of those names are interesting – strange but interesting.

Once you have completed building the Data Flow, you must put the Data Flow object somewhere in the Control Flow. This looks like a flow chart type plan which you can have multiple Data Flows happening depending on Tasks.


The Control Flow toolbox contains 2 different sections:

1, Control Flow Items
	These are various tasks relating to handling different tasks such as: “Execute Package Task”, “Execute SQL Task”, “Send Mail Task”, “Transfer Database Task” and “Bulk Insert Task” to name a few.
Last but not least,
2, Maintenance Plan Tasks
	These are various tasks relating to handling maintenance on Data Sources – such as: “Back Up Database Task”, “Check Database Integrity Task”, “Shrink Database Task” and “Rebuild Index Task” to name a few.

Anyway – I don’t know how much of this is actually new in SQL 2008 compared to SQL 2005 – as I have not played with SSIS until now.

Summary
	The SSIS in SQL 2008 is a huge different kettle of fish compared to its old DTS counterpart. In good ways and bad. It can do a whole lot more different things – but with that its so much more complicated.

Anyone please feel free to correct me if any of the detail is incorrect..