SSIS – Dynamically Naming Destination Output Files

When I am not wearing my DBA hat, I put on my BI (Business Intelligence) hat.  BIDs (Business Intelligence Development Studio) is a very powerful and scalable business intelligence software package that was introduced in SQL Server 2005.  Within BIDs, I will focusing on SSIS (SQL Server Integration Services) projects.  SSIS projects are designed to extract, transform, and load data, which is more commonly known as ETL.  In this post, I will be focusing on extracting data and loading it to a destination, there is no need for me to transform the data.  I will start by creating a new SSIS package.  You can launch BIDs by browsing to Start –> Programs –> SQL Server 2005/2008 –> SQL Server Business Intelligence Studio. Once BIDs has launched, you should click New Project and select Integration Services Project, as shown below.

We will name our project DynamicDestinationFileName.  Once the project is created, we will switch over to SSMS and create a sample table, in tempdb.  Log into your instance of SQL Server and execute the code below.

SET NOCOUNT ON;

GO

USE [tempdb]

GO

IF EXISTS(SELECT 1 FROM sys.tables WHERE NAME = 'MyTable')

BEGIN

    DROP TABLE dbo.MyTable;

END

GO

CREATE TABLE dbo.MyTable(

Id INT IDENTITY(1,1) PRIMARY KEY,

Col CHAR(1)

);

GO

INSERT INTO dbo.MyTable VALUES ('A');

INSERT INTO dbo.MyTable VALUES ('B');

INSERT INTO dbo.MyTable VALUES ('C');

INSERT INTO dbo.MyTable VALUES ('D');

INSERT INTO dbo.MyTable VALUES ('E');

GO

We have just created our SSIS data source, which means this is the table that will feed our destination flat file.  Switch back to BIDs and drag a “Data Flow” task to the designer, from the toolbox. You screen should look like below.

Now we can double-click the data flow task to configure it, or you can click the data flow task tab above the canvas.  We will need to drag a OLE DB data source and a flat file destination, from the toolbox.  Your canvas should look like below.

Select the OLE DB Source and drag the green arrow to the flat file destination.  Now, let’s configure the source.  Double-click the source and click New.

Click new again and then type in the database server where you just ran the scripts to create the table.  In the database dropdown select tempdb.  Your connection should look like below.  You can test your connection by clicking the test connection button.  After testing the connection, click ok to apply the changes and then ok again. 

Now choose MyTable from the table dropdown. 

Once you select the table, click the columns tab to assign columns to the data source.  Click Ok and were down with the data source!!!! Now let’s move onto the destination.

Double-click the flat file destination and create a new connection.  When you click “New” to create the destination connection a window will popup and ask for the flat file destination format. 

The options include delimited, ragged right, fixed-width etc.  The differences between these types is not relevant to the task at hand, and will not be covered here.  BOL has a lot of information about the differences between output types.  Okay, lets move on by selecting delimited and ok.

Now we will need to define the properties of the destination file. Click the browse button to choose where the destination file will be created.  I will be creating my file to the C:\MyTable.txt.  you may be screaming, HUH!!!!, YOU SAID THE DESTINATION NAME WOULD BE DYNAMIC!!!!!!  Do not fret it will be and I will show you how to transform the static destination to a dynamic one, in a few minutes.

Okay now, click the columns tab on the left to make sure the columns look correct.  You should see the columns Id and  Col.  If everything looks good, click ok.  Note: there should not be any data because the flat file is empty at present, but the columns should still be correct.

Now we should be in the destination editor window.  Click the mappings tab, as shown below and make sure everything aligned properly. If so, click ok.

I hope you are still with me :-).  Finally we can get to the meat of the post.  Select the Flat file connection manager in the connection managers pane –> right-click and choose properties and click the ellipses next to “expressions”, as shown below.

Choose connection string from the dropdown and then click the ellipses in the expression box.  You will have to build the expression to create the destination connection string.  The expression editor uses VB coding, so that may be a benefit for some of you out there.  I have provided the code below to build the expression.  Click the evaluate button to see what the connection string will evaluate too. Note: I had to use double backslash because it is required because the backslash acts as a escape character, in the expression editor.

Note: I like to use variables to store file paths because it makes the code cleaner and is easier to modify the package, as you only need to update the variable in once, whereas, you may have to update lots of expressions, if they are hardcoded.

"C:\\MyTable_" +

Right("0" + (DT_STR,4,1252) DatePart("m",getdate()),2) +

Right("0" + (DT_STR,4,1252) DatePart("d",getdate()),2) + 

(DT_STR,4,1252) DatePart("yyyy",getdate()) + ".txt"

Here is what the expression looks like and should evaluate too.

Now lets, run our package and test it out. Click the green play symbol to run the package.  When you run the package all the tasks should turn green, which indicates they succeeded.  If yours turned red, something went wrong. 

Now we just have to make sure our file is where we put it and the name contains today’s date in MMDDYYYY format. 

We are good, the package did everything we wanted it too.  So there you have it…. a method to create a destination file, with a dynamic name.  It is really quite easy to do, the tedious and mundane part is creating and setting up the package itself.  I hope you learned something new and can use this now or somewhere down the road. 

Stay tuned, I will be posting a lot more BI solutions, tips,  and tricks.