Monthly Archives: March 2016

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

Removing quotes from CSV created by PowerShell

In a previous post, I demonstrate how to reshape a comma delimited file (CSV) using PowerShell. I noted that the results put all of the column values in double quotes: "ID","Name","State" "5","Stephanie","Arizona" "4","Melanie","Oregon" "2","Katie","Texas" I used this code to produce … Continue reading

Posted in ETL, PowerShell | Leave a comment

Shaping a comma delimited file with PowerShell

Here is a use case for what I am thinking of. You have comma delimited file that you need to load into SQL Server. It is a very large file that you need to join to an equally large table … Continue reading

Posted in ETL, PowerShell | Leave a comment

Using PowerShell to shape comma delimited file

PowerShell has power to handle many ETL tasks (PowerShell as an ETL tool). In this post I illustrate how you can use PowerShell to shape a comma delimited file. That is, how to remove columns, filter rows and sort the … Continue reading

Posted in ETL, PowerShell | Leave a comment

PowerShell as an ETL tool

“If Your Only Tool Is a Hammer Then Every Problem Looks Like a Nail.” My hammer has been SSIS for most of the last 8 years. I have reluctantly used scripting language like .Net framework (C# and VB.Net) and even … Continue reading

Posted in ETL, PowerShell | Leave a comment

What to include in source view

In a previous post (ETL Source: full query or view) I suggest that you create a view (or function) to use as the source for your data load. In this post, I ask what should you include in your source … Continue reading

Posted in ETL | 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