Tag Archives: SSIS

Task Factory–File Properties Task

My last video on the Task Factory Compression Task was well received, so I thought I’d build on it with a video on the File Properties Task. It’s a cool little tool that will grab any of the various attributes associated with a file. If you want to see more about Task Factory, you can head on over to my employers website, Pragmatic Works.com

Advertisements

Task Factory–TF Compression

In previous posts I’ve been working through some of the Task Factory components. As many of you know I went to work for Pragmatic Works earlier this year. Since then I’ve been learning all our tools, and since there’s no better way to learn than by teaching am creating blog posts about these tools.

Today I thought I’d look at the TF Compression Task. They say a picture is worth a thousand words, not sure what that converts to in term of video but I feel it must be a lot. So without further delay, here is my video showing how to use the TF Compression Task. You can also find it directly on You Tube at http://bit.ly/tfcompression

Task Factory Null Handler

Nulls. Seems like they are the universal headache among all data based projects. Trying to clean up nulls in SSIS is certainly doable, but not without some work with expressions. An equal headache to null values are blank values. In most cases, you’ll either want these converted to a null, or some specific value. Because null handling is such a common task Task Factory has a transformation specifically designed for working with nulls, the Task Factory Null Handler.

There are three options for handling nulls / blanks within the Null Handler.

  • Convert Null to user defined value
  • Convert blank value to null
  • Convert blank value to a user defined value

Let’s take a look and see just how it works. First, we’ll need some sample data to play with. Here’s a small script I created to do just that.

image

It’s only a few rows, enough to test the various modes Null Transform supports. In the first row, we’ll be converting a null date to a defined value, specifically the date 9999-12-31. In the second row, we’ll convert a null text field to a user defined value, "Was Null”. In the third row, we’ll convert blank text to user defined text. In the final row, we’ll convert a blank to a null.

Now that we have our data in place, it’s time to setup the SSIS package. Create a new package and drop a data flow task on it. Start with a standard OLEDB source, and hook it to the database and NullTestSource table.

Next, drag the Task Factory Null Handler on the display surface, and hook it to the source. When you open it, you’ll see a list of the input columns, to the right they will all be set to ‘No Action”. Expand the drop down for the action beside the SomeDate column. Change the action to “Convert NULL value to user defined value”.

Now click the drop down beside the value. In the Parameter Type drop down are three options, Direct Input, Column, and Variable. These are the various choices you have for providing a replacement value. For this exercise, we’ll just use the Direct Input. Change the replacement value to 12/31/9999.

 

image

Repeat the steps for SomeText1, only for it’s replacement value use “Was Null”. For SomeText2, select “Convert blank to null” for the action. No other configuration is required for this action. Just leave the SomeAction column set to “No Action”. It’s merely present to tell us what action the row is performing.

Time to test. Place a Task Factory Terminator Destination below the Null Handler. Place a data viewer inline with it and run the package. Your results should be:

SNAGHTML6ac505

Well, this looks pretty good, but it’s not quite right. For the SomeText1 column, we wanted to check for two conditions, first if it’s null we wanted it converted to a value, second if it was blank we wanted it converted to a different value. In the first null transform we handled the conversion of null to a specific value, but now how to also check for a blank in the column and replace it? No problem, all we need to do is insert a second Null Transform between the first one and the Terminator destination.

Inside it, beside the SomeText1, select the action “Convert blank value to a user defined value”. For the user defined value, enter “Was Blank”. Now hook it to the destination, and place a data viewer on it. Now we should see the correct results.

SNAGHTML75abcb

There we go. The Null Transform makes dealing with blank and null values very easy and straightforward.

Deep Fried Arcane

At TechEd last year I was interviewed by the Deep Fried Bytes guys, along with another great SQL guy Denny Cherry. The topic of our interview was What Should Developers Know About SQL Server. (Click the link for the show.)

In the interview we cover SQL Server Full Text Search, SQL Server Service Broker, and SQL Server Integration Services. And if you listen, you’ll hear about my favorite deep fried food!

SSIS For Developers at DevLink 2010

I have the honor of presenting at DevLink 2010 today. DevLink is a great conference in Nashville, TN, this year attendance topped 800 people. In my session,  SSIS For Developers, we’ll look at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we’ll also look at how to call your new SSIS job from your .Net application.

There are two code demos used during the presentation, both available at my Code Gallery site. The first is the basic SSIS For Devs demo with the three packages. The second is the more complex example showing how to call SSIS from your .Net application.

SQL Server Integration Services for Developers

Today I presented SSIS For Developers, we looked at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we also looked at how to call your new SSIS job from your .Net application.

There are two code demos used during the presentation, both available at my Code Gallery site. The first is the basic SSIS For Devs demo with the three packages. The second is the more complex example showing how to call SSIS from your .Net application.

CodeStock 2010

It’s June, must be time for CodeStock! For those who don’t know, CodeStock is a conference but on annually by the East Tennessee .Net Users Group. This year I am fortunate to have been selected for two presentations.

The first is The Decoder Ring for Data Warehousing / Business Intelligence. This is a concepts talk in which you’ll learn about the terms and overall design of a Data Warehouse, and what they mean when they say Business Intelligence. While we’ll mention the products SQL Server offers, unfortunately we won’t have time for much in the way of a demo.

My second session of the day is a nice follow on to the above session, but will also work even should you not have been in the first session. In SSIS For Developers, we’ll look at how SSIS, commonly used in Data Warehousing, can also be used by most developers to solve issues that frequently come up in the course of their job. Data conversion and exporting data are two good examples, and we’ll also look at how to call your new SSIS job from your .Net application.

There are two code demos used during the presentation, both available at my Code Gallery site. The first is the basic SSIS For Devs demo with the three packages. The second is the more complex example showing how to call SSIS from your .Net application.