Recently I was able to solve a really tricky problem in SSIS using the TF Surrogate key transform. Let’s discuss the business problem:
The client was using some complicated .net code to loop through each line in a file and look for a very specific string of code. Once the string was found they then wanted to return all the values found six rows below that row. This process continues through the file. A picture is worth a thousand words:
So now that we understand the business problem we can solve this with SSIS. The solution is actually quite simple. Essentially we need to get the string we are searching for (5909045743) and the row with the values on the same row so we can let SSIS work its’ magic. This is where the Surrogate Key transform comes in.
To solve this problem I used not one but two Flat File source components in my data flow and I brought the same file in twice. I then used the surrogate key transform to assign row numbers to the rows coming from each flat file source component.
- For the first flat file source I started the row number at 6 and then incremented by one.
- For the second flat file source I started the row number at 1 and incremented by one.
- Setting up the surrogate key transform is extremely easy, 3 easy steps, see below:
Now I use the merge join component in SSIS and join the two data sets on the newly created SK_Columns. This will put our search string on the same row with the values we were looking for.
Below is a picture of the data flow thus far:
And here is a screenshot of the new result set after the Merge Join transform, notice how the two rows are now on the same row. From here we can do some basic SSIS operations to split out all the records we don’t need and then we can use a derived column transform to parse out the values in “Column 2”.
Thanks for looking!
Working with SSIS and the BI Stack from Microsoft I have discovered a few tips and tricks that come in quite handy. To my surprise a lot of these are not very well know in the BI World. In this series I am going to try and post one or two SSIS Tips and Tricks on a weekly basis.
As you are well aware the SELECT * syntax in SQL Server should not be used inside of SSIS. This is because the metadata in SSIS is very rigid and if you add or remove columns to the table in your select statement it can affect other transforms in the pipeline that have been defined with very specific metadata properties.
So if you are like many people who use SSIS it’s likely you alt + tab over to SQL Server Management Studios and then view the table and write your select syntax there with all the applicable columns for your query. Then you copy that syntax and alt + tab back over to BIDS / SSDTs and paste.
It’s unfortunate that there isn’t some kind of tool inside SSIS that will assist us with our incredibly difficult predicament. Just kidding, of course there is, if there wasn’t I wouldn’t be blogging about it! Let’s demonstrate.
First – Open up your OLE DB Source component in your data flow and write your Select * from table statement. For this example I used SELECT * FROM SALES.CUSTOMER in the AdventureWorks2012 database.
Next click “Build Query…” found on the right side of the OLE DB Source Editor.
Click ok and you are done! Thanks for looking and check back for more time saving tips and tricks.
I, like everyone, get an incredible amount of email on a daily basis. I recently learned some awesome tricks for searching my outlook that I wanted to share!
In the past when I wanted to search for something I would just type that item into the search box. For example ‘John Doe’. Unfortunately this returned a lot emails that John Doe not only might have sent me but also was cc’d or bcc’d in. It also appeared that if his name was in the subject that email would be returned. Well when you are working on a project in which multiple people are cc’d on every email this results in an overwhelming number of results.
So now to the good part!! Want to search for emails that you received from John Doe? Try this
From:John Doe (Note you don’t have to fully fill out his name.)
From:Joh – This will also return emails from John Doe, but of course anyone else with Joh as well.
Subject:Analytics (This will search the full subject string for the word ‘Analytics’
These are just a couple of the options available. For a full list please check out the website below:
Learn to narrow your search!