Category Archives: ETL

Getting metadata from query using sp_describe_first_result_set

One of the tasks that eats a lot of my ETL development time is documenting the views and procedures I write. I need to know where exactly the data is coming from. sp_describe_first_result_set provides a flag for outputting the source … Continue reading

Posted in Documentation, ETL, Testing, TSQL | Leave a comment

Using view as source for application

Recently someone posed a puzzling question on the MSDN forums (How to reorder a column in a table which also has a foreign key). They needed to be able to add columns to a table in a specific order. If … Continue reading

Posted in ETL, Testing, TSQL | Leave a comment

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

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