One of the things that messes up processing delimited files is when the delimiter character is actually part of the data rather than acting as a delimiter. So look at the following: The second line has 4 commas but the first has only 3 commas. The second comma is actually part of the data: One […]
Selective text qualifiers in SSIS Read More »
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. But what do you do if you want to create simply append one row’s value
SSIS Script Component – Last Row Read More »
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), multiple Pivot Keys (COLUMNS) and even multiple Pivot Values (Values). However, using the SSIS Pivot
SSIS Pivot Transformation with Multiple Set Keys Read More »
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 »
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 »
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 the table definition, not removing or adding columns, but simply carelessly renaming a column, changing
Another argument for using command source in SSIS Read More »
One of the things that I like about providing training is the kind of questions that come up. Many times they are issues that I have thought about but been just plain too lazy to examine. This week one of my students asked if it were possible to lookup at record in another table and
SSIS: Using Lookup to not only find records, but add them too Read More »
You must be logged in to post a comment.