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