Extract Variable Records to the Same File

Hello my faithful reader(s).  I have been away for a while but do not despair:  I am back and 2010 promises some really cool posts.  Today’s post centers on extracting variable record types to a single flat file.  This applies to data warehouse type files that have  header, detail & trailer records somewhat similar to those I have seen for terradata and other warehouse applications.  There will also be a future post in this blog that will describe how to load this data type into your warehouse along with some built in intelligence.

First, you will want to create a data flow task to create a header record for this file.  Open your OLE DB Source. Choose your OLE DB Connection and choose SQL command as your data access mode.  From there, you can create a simple header record something similar to the one below:

SELECT ‘HDR|’ + CONVERT(VARCHAR(8), GETDATE(), 112) + ‘|FILENAME|’ + CONVERT(VARCHAR(10), GETDATE(),120)+’/’+CONVERT(VARCHAR(8), GETDATE(),114) as HDR

Verify that  a single column is being displayed for that connection manager.

Next, drag and drop a flat file connection into the dataflow and set it up as you would a normal flat file. 

NOTE:  Make sure you check overwrite file in the Flat File Destination Editor.  This will ensure that your file is fresh each time this job is executed.

Your first data flow should look like this:

Now create a second data flow task and just as before create an OLE DB Source. Choose your OLE DB Connection and choose SQL command as your data access mode.  The SQL command here will merely extract all of the records out of a particular table.

Verify that columns are being displayed properly:

Once the extraction is complete, lets utilize the nifty RowCount transformation.  Connect the OLE DB Source to a RowCount transformation.  Before you set up your RowCount transformation, you will need to create a variable at the package level called Records (type Int32).  When you set up your RowCount transformation, choose the newly created variable as your VariableName.

Just as you did before, drag and drop a flat file connection into the dataflow and set it up as you would a normal flat file.  HOWEVER, make sure that the flat file is the exact same file that your header record is being extracted to.

NOTE:  This time. make sure you uncheck overwrite file in the Flat File Destination Editor.  This will ensure that your detail records will append to your header record.

Your second data flow should look like this:

Lastly,  create a third data flow task but this time choose a Script Component Data Flow Transformation type Source.  Setup your Script Component Output with a single column called DummyColumn.

Click on the script tab and choose Design Script…

Copy and paste the below script into the script window.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()
        ‘
        ‘ Add rows by calling AddRow method on member variable called “<Output Name>Buffer”
        ‘ E.g., MyOutputBuffer.AddRow() if your output was named “My Output”
        ‘
        With Output0Buffer
            .AddRow()
            .DummyColumn = 1
        End With
    End Sub

End Class

This essentially creates a dummy source so that we can now utilize our variable ‘Records’ in the data flow.  Once you have completed the script task, create a derived column transformation and create two new columns for that transformation: T1 & T2.

Set field T1 =  “TRL|”

Set field T2 = @[User::Records]

Just as you did before, create a second derived column transformation and create a  single column for that transformation: TRAILER.  Set the TRAILER expression equal to T1 + T2 which will concatenate “TRL|” and the number of detail records in your extraction similar to TRL|5.

Drag and drop a flat file connection into the dataflow and set it up as you would a normal flat file.  Again, make sure that the flat file is the exact same file that your header record is being extracted to.

NOTE:  Also, make sure you uncheck overwrite file in the Flat File Destination Editor.  This will ensure that your trailer record will appends to the end of your file.

Now your third data flow will look like this and you will have successfully extracted variable record types to a single file.

Your extract should resemble something similar to below the data below:

HDR|20100122|FILENAME|2010-01-22/12:04:32
1|2|3|4|5|6|7|8|9|10
2|2|3|4|5|6|7|8|9|11
3|2|3|4|5|6|7|8|9|12
TRL|3
Advertisements

Tags: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: