Thursday, July 16, 2009

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.

image

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.

image

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.

image

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.

image

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. 

image

Now choose MyTable from the table dropdown. 

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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.

image

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. 

image

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

image

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.

27 comments:

Anonymous said...

Excellent tip - I also need to include in the file name a value which is determined from a SQL query each time a file is created - so my file name will be e.g ABC_'value from query'_yymmdd.csv
Any pointers for setting this up?

Adam Haines said...

Sure. You will need to add another variable and a SQL Execute Task to your SSIS Package. Set your SQL Execute Task to return a single row and click the result set tab. Set the result set name to the column name you are selecting from and the variable equal to the variable you just created. You can then build the connection string, as described in this article.

daspeac said...

it seems you have not heard about how to fix sql mdf file

Anonymous said...

Great tip and refreshingly well written.

Anonymous said...

I was searching for the place to link a variable to connection string - would never have found it without this post! Thanks!

Anonymous said...

These instructions are fantastic. I've been trying to do this off and on for a while and you explained it perfectly. Thanks so much!

Anonymous said...

Genio!!! Me salvaste!! Muchas gracias!!!
Saludos desde Buenos Aires, Argentina.

Anonymous said...

me gusta

love you

B

Ces said...

worked like a charm :-)

Anonymous said...

Excellent!!! This was my exact requirement and I found the solution, Thank you very much..

Anonymous said...

I found this wonderfully useful. Thanks for taking the time to create this tutorial!

Tiago said...

Great. Once I get a faster internet connection to my rent apartment in buenos aires I gonna try to do this, it doesn´t look very difficult and looks very powerful and useful.

Java Mann said...

Hello, I discovered this tutorial when ti was first written and just today I stumbled upon it again. Very helpful and a life saver.

However I would like to use things up a notch: How do I add the time?

I modified the code like this:

[code]
@[User::FilePath] + "C:\\myfolder\\myfile_" +
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()) + ".csv"
[/code]

The output looks like this: myfile_07_29_2013 but I would like to add the time, like this: myfile_07_29_2013_1_56am

How can I do that?

Thank you again!

JMAN

Adam Haines said...
This comment has been removed by the author.
Adam Haines said...

Hi Java,

You can use an expression like this:

@[User::FilePath] + "C:\\myfolder\\myfile_" +
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()) + "_" +
((DT_I4)DATEPART("Hh",getdate()) == 0 ? "12" : ((DT_I4)DATEPART("Hh",getdate()) > 12 ? (DT_WSTR,2)(DATEPART("Hh",getdate()) - 12) : (DT_WSTR,2)DATEPART("Hh",getdate()))) + ":" + ((LEN((DT_WSTR,2)DATEPART("mi",getdate())) > 1) ? (DT_WSTR,2)DATEPART("mi",getdate()) : "0" + (DT_WSTR,2)DATEPART("mi",getdate())) + "" + ((DT_I4)DATEPART("Hh",getdate()) > 11 ? "PM" : "AM")

Java Mann said...

Adam, I do not know how much I should thank you but thanks a million. I have been trying to get this done. Now I can run a package every minute if needed and generated a new CSV file.

This is just amazing.

Thanks again!

If you ever need my help, do not hesitate to ask.

Java Mann said...

Hello Adam, I think the excitement got to me. I tested the code and it worked. However, my latest job suddenly failed with the following error message:

------------------------------
Expression cannot be evaluated.
------------------------------
ADDITIONAL INFORMATION:

A truncation occurred during evaluation of the expression.

(Microsoft.DataTransformationServices.Controls)

I am combing through the code but cannot determine what is causing this error.

Thanks for checking!

Javaman

Adam Haines said...

JavaMan try explicitly casting your expression into a str, with a larger size. You are probably hitting an arbitrary default string size limit because of implicit conversion.

Adam Haines said...

Something like this,

(DT_STR,1000,1252) @[User::FilePath] + (DT_STR,1000,1252) "C:\\myfolder\\myfile_" +
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()) + "_" +
((DT_I4)DATEPART("Hh",getdate()) == 0 ? "12" : ((DT_I4)DATEPART("Hh",getdate()) > 12 ? (DT_WSTR,2)(DATEPART("Hh",getdate()) - 12) : (DT_WSTR,2)DATEPART("Hh",getdate()))) + ":" + ((LEN((DT_WSTR,2)DATEPART("mi",getdate())) > 1) ? (DT_WSTR,2)DATEPART("mi",getdate()) : "0" + (DT_WSTR,2)DATEPART("mi",getdate())) + "" + ((DT_I4)DATEPART("Hh",getdate()) > 11 ? "PM" : "AM")

Java Mann said...

Hy Adam,

For some strange reason the expression works at a certain time but the overnight task is failing. Today I tested the expression by running a task every 1 minute. I am thinking this has to do with the time?

This is beyond my reach. What's your recommendation. Should I just forget about the time part?

Java Mann said...
This comment has been removed by the author.
Adam Haines said...

JavaMann,

I would need to know what times specifically the job fails. If this were my project/task, I would probably use a more native time format. Essentially, I would use military time instead of AM/PM. The date is already presented in this format naturally and will convert much easier.

@[User::FilePath] +"C:\\myfolder\\myfile_" +
Right("0" + (DT_STR,2,1252) DatePart("m",getdate()),2) + "_" +
Right("0" + (DT_STR,2,1252) DatePart("d",getdate()),2) + "_" +
(DT_STR,4,1252) DatePart("yyyy",getdate()) + "_" +
Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) + ":" +
Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) + "_" + ".csv"

Java Mann said...

Good morning Adam.

There is only one Job and it Fails as exactly 1am or 12am or after midnight.

Thanks again!

Adam Haines said...

Java Mann,

I have tracked the issue down to a conversion problem. for some reason the 2 digit minute was being truncated even though we specified 2 characters. You can use this instead.

@[User::FilePath] + "C:\\myfolder\\myfile_" +
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()) + "_" +
((DT_I4)DATEPART("Hh",GETDATE()) == 0 ? "12" : ((DT_I4)DATEPART("Hh",GETDATE()) > 12 ? (DT_STR,4,1252)(DATEPART("Hh",GETDATE()) - 12) : (DT_STR,4,1252)DATEPART("Hh",GETDATE()))) + ":" + ((LEN((DT_STR,4,1252)DATEPART("mi",GETDATE())) > 1) ? (DT_STR,4,1252)DATEPART("mi",GETDATE()) : "0" + (DT_STR,4,1252)DATEPART("mi",GETDATE())) + "" + ((DT_I4)DATEPART("Hh",GETDATE()) > 11 ? "PM" : "AM") + ".csv"

Java Mann said...

Great!
I'll let you know how it turns out tonight. I am optimistic that this will work.

Thanks again Adam. You have been extremely helpful.

Java Mann

Java Mann said...

Good morning Adam:

Just needed you to know that I tested the code last night after Midnight and all appeared to have worked very well. I ran continuous jobs from 11pm to about 2am just for testing and the procedures did not fail.

Thanks again, hopefully and I am positive, this is it!

Thanks you my friend.

Java Mann

Allen Jo said...
This comment has been removed by the author.