Month: August 2014

Remove unwanted characters in SSIS with Task Factory!

/ Mitchell Pearson / Leave a comment

Recently I was tasked with removing special characters that randomly showed up in one column of a data set, these characters were coming from a proprietary source. The characters were the hash tag (#) and semi-colon (;). One or both characters would appear at the beginning, end or middle of the column. There are a few different ways you could deal with this type of situation, each of them varying in their degree of complexity and difficulty.

Fortunately for the customer we provided a very easy and quick solution. In this short blog we are going to take a look at the Data Cleansing transform offered in Task Factory. First let’s take a look at a before and after picture so you can visualize the data and end result.

Setting up the component

  1. Pull the Task Factory Data Cleansing Transform into your Data Flow.
  2. Connect your source data to the Data Cleansing transform.
  3. Right click on the Data Cleansing Transform and click Edit.
  4. First select the column you would like to “Replace”.

  5. Next select Replace Specified characters or words with user defined value. (Screenshot 1)

  6. After you select an action you will receive a parameter box where you can specify the characters you would like to replace and what you want to replace them with.

  7. Now we will finish configuring the rest of the component. Please see screenshot below for final settings.

  9. Finally click ok to save your settings and execute the package!

Conclusion:

That’s it. I tried to drag it out as much as I could but some things are just that simple. Take a look at some of the other actions included and enjoy! As always thanks for looking.

Advertisement

SSIS Performance Tuning Webinar–Q & A

/ Mitchell Pearson / 2 Comments

Thank you everyone for attending my webinar on SSIS Performance Tuning, if you missed that presentation you can watch it here: http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/PastWebinars.aspx?ResourceId=683

Below are some of the questions that I received from the Webinar:

Q: Can you give some performance tips in using script tasks too?

A: Yea, don’t use them! Script tasks can be really bad for performance and many times I have been able to replace someone else’s code with native SSIS components. For example, I had situation where the client was using some very complicated .net code to parse out columns based on logic. The package was taking over an hour to process 1 million records. I replaced this with some conditional splits and derived columns and it now runs in 3 minutes.

Q:  I am assuming that the file formats must be the same for all files when using the MultiFlatFile transform, correct?

A: You are absolutely correct. The metadata in each file must match.

Q: PW delivers a ‘TF Advanced Lookup Cache Transform” component.  What are the benefits of using this component over the Cache Transform covered earlier?  It seems that the TF components cannot make use of the same result set when the data set is role based.

A: For basic caching of data I would use the native SSIS cache transform. The major advantage you get from the Task Factory component is you can do very difficult range lookups with ease and  they will perform at a high level. Please see my blog post on this.

Q: What version of SQL Server is being used?

A: I was using SQL 2012, but everything in the presentation is applicable to 2005 and 2008.

Q: With the multi flatfile connection manager can you specify specific types?

A:  Yes, the wild card character can be anywhere in the connection string property. So you could do test*.txt to only pull in text files where the file name begins with test.

Q: Why would you ever not use table or view (fast load) option in the OLEDB Destination?

A: Well I personally would always use that option. However, with the fast load option all records are committed for the entire batch. So if there is a record that is bad and causes the failure you will not know which record caused the error. With table or view option each record is committed individually so you know exactly which record caused the failure.

Q: is the volume on?

A: Sorry can you ask that again, I couldn’t hear you….

Thanks again!

How to solve a a tricky SSIS Problem with the TF Surrogate Key transform!

/ Mitchell Pearson / Leave a comment

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:

image

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:

image

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:

image

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”.

image

Thanks for looking!