SSIS Test Project First Phase Tested and Working...

Alas, it is only a simple but nice example of it working. The complete SSIS package will require quite a few of these, plus more complex flows:

Picture of Example Flow
	Above shows the simple flow in 3 stages.   1 - Get the Data   2 - Perform Lookup to see if row specific columns exist   3 - Write rows that failed (didn't exist in new table) to the new table.
	The red line shows the path for failed result (no row found in lookup).

However, according to the Online Help of SSIS (see here) i found this snippet that you must set the Error Output Event: You can do this by selecting one of the following options:   > Ignore the failure and direct the rows to an output - Direct the rows anyway to the actual output (the Green line).   > Redirect the rows to an error output - Redirect the Source Rows of data to the Error Output (the Red line).   > Fail the component - Fail the component if any error happens.

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..

First Blog Post - Geekzone Pizza Evening and Upcoming .Net Project :)

Firstly, if you haven't already - please register here for the Geekzone Freeview Pizza Evening 2010 - whether you are in Auckland, Wellington or Christchurch.. it will be alot of fun - and there are so many prizes to give away!

Snippet:

Venues and dates: Wellington: 13th February 2010 (One Red Dog Blair St, registration for WLG) Auckland: 6th March 2010 (venue TBD, registration for AKL) Christchurch: 20th March 2010 (Spagalimis Victoria St, registration for CHC) Sponsors: - 2degreesmobile (three $200 SIM + three $150 SIM or topup if existing customer) - Altaro (21 licenses for Oops!Backup for Windows) - Data centre limited (3x 1U colocation plus unlimited bandwidth for one year - you must register your interest at www.datacentre.co.nz before the meeting, drinks AKL) - DTV Solutions (Homecast HT9200DTR WLG, three DTVS-1B satellite receivers) - NETGEAR (three EVA2000 ) - Nutshell Leather Cases (one leather case matching each of the Telecom handsets below) - NZ Internet Shop (Aeropress coffee maker WLG) - NZS.com (online advertising packages) - Quay Corporate (24" LCD WLG) - sniff (drinks AKL/CHC/WLG) - Sony Ericsson (three W705 mobile phones, suitable for XT network) - Stickemen Studios (concept art for King Fu Funk, Doc Clock, Dragon Master Spell Caster and Shadow Rising videogames) - Telecom New Zealand (two sierra wireless 885u USB mobile broadband modem, two Samsung Ultra Touch S8300T, one Sony Ericsson W995 and one Sony Ericsson W705)

Secondly, have been secretly working on a .Net project to help build my skills as a Security Consultant. :-)

More detail about the 2nd feature later...

Have a fun afternoon :)