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 of columns. This is especially true when your columns are based on dates. This sample […]
SSIS Flexible UnPivot Read More »
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 as to the values that it removes. How do you specify which row to remove?
SSIS Removing Duplicate Rows Using Conditions Read More »
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 with the following order Col2, Col3, Col1. Or worse, you get Col2, Col1. How do
Reordering Columns Using PowerShell Read More »
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 this result: cd c:\MyFolder Import-Csv -Path SampleData.csv|Where {$_.Country -eq “USA”} ` | SELECT ID, Name,
Removing quotes from CSV created by PowerShell Read More »
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 in a database. For example at one place I worked we had a set of
Shaping a comma delimited file with PowerShell Read More »
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 file output. Let’s start with a simple file (SampleData.csv): ID,Name,State,Country 1,Russ,Sandys,Bermuda 2,Katie,Texas,USA 3,Gail,Sandys,Bermuda 4,Melanie,Oregon,USA 5,Stephanie,Arizona,USA
Using PowerShell to shape comma delimited file Read More »
“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 VBA and (don’t choke) Visual Basic 6.0. But I think that I have found a
PowerShell as an ETL tool Read More »
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 view? I argue that in this case more really is more. There is a lot
What to include in source view Read More »
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, derived tables, etc. Do you embed the full query in the stored procedure (or data
ETL Source: full query or view Read More »