Category Archives: SSIS

SSIS Script Component – Last Row

One problem that you might encounter with data is where you are gathering information from multiple lines and want to output only one line. If you are going to simply aggregate the results, then you can use the Aggregate transformation. … Continue reading

Posted in ETL, SSIS Script | Leave a comment

SSIS Pivot Transformation with Multiple Set Keys

When you pivot data, there are three dimensions that you need to set: the Set Keys, the Pivot Keys and the Pivot Values (to use the SSIS terms). In Excel it is very easy to select multiple Set Keys (ROWS), … Continue reading

Posted in ETL, Pivot, SSIS | Leave a comment

SSIS Flexible UnPivot

One of the problems I have encountered is trying to unpivot a file that has a variable number of columns. There are a set of columns that are fixed, that represent the Row keys. And then there are variable numbers … Continue reading

Posted in ETL, Pivot, SSIS, SSIS Script | Leave a comment

SSIS Removing Duplicate Rows Using Conditions

Sometime your data source has duplicates. SSIS has an out of the box tool to remove these duplicates. The Sort transformation has a “Remove rows with duplicate sort values” property (https://www.mssqltips.com/sqlservertip/3036/removing-duplicates-rows-with-ssis-sort-transformation/). But it has a problem. It is very random … Continue reading

Posted in ETL, SSIS, SSIS Script | Leave a comment

Reordering Columns Using PowerShell

One of the regular issues that is discussed on the SSIS forum is what to do when a source file changes the order of columns. You build your SSIS package to handle Col1, Col2, Col3 and you get the files … Continue reading

Posted in ETL, PowerShell, SSIS | Leave a comment

ETL Source: full query or view

When copying data, you generally need to shape the source data before loading it. This may be as simple as joining business keys to dimension tables to get dimension keys. Or the query can be quite complex, involving multiple joins, … Continue reading

Posted in ETL, SSIS | Leave a comment

Another argument for using command source in SSIS

You are building an SSIS package and carelessly create a data flow with a source that connects to a SQL Server table. You simply use the defaults in the OleDB source and select a table name. Then some slacker changes … Continue reading

Posted in SSIS | Comments Off on Another argument for using command source in SSIS