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.

37 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 Man 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 Man 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 Man 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 Man 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 Man 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 Man 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 Man 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 Man 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

Unknown said...
This comment has been removed by the author.
21st Century Software Solutions said...

Datamodelling online training - 21st Century Software ...
http://www.21cssindia.com/courses/datamodelling-online-training-24.html
ఈ పేజీని అనువదించు
Datamodelling Online Training , Datamodelling Training , Datamodelling Corporate Training, Best Datamodelling Training, Expert Datamodelling Training, ...
course contents, biztalk admin enquiry, ...Courses at 21st Century Software Solutions
Talend Online Training -Hyperion Online Training - IBM Unica Online Training -
Siteminder Online Training - SharePoint Online Training - Informatica Online Training
SalesForce Online Training - Many more… | Call Us +917386622889
Visit: http://www.21cssindia.com/courses.html

Anonymous said...

A great thank for this tip !

Anonymous said...

Thanks for the post, it got me exactly what I needed, but not consistently. I am using the code below, with the variable result pulling from a query and being '2013111'. Upon creation of the expression and starting the project, my file exports with the correct name - EE_Upload_1757_20131111_20151216_1900.csv. I make no changes and run it again the next minute and the variable falls out and I get - EE_Upload_1757__20151216_1901.csv. I'll play with certain things and it will come back here and there, but never runs properly twice in a row. What am I doing wrong? Any insight is appreciated, thanks again!

(DT_STR,1000,1252) "C:\\...\\EE_Upload_1757_" +

(DT_STR,1000,1252) @[User::tkEndDate] +

"_" +

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

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

"_" +

RIGHT("0" + (DT_STR,4,1252)DATEPART("hh",getdate()),2) +

RIGHT("0" + (DT_STR,4,1252)DATEPART("mi",getdate()), 2) +

".csv"

Abhijeet said...

Very good article and very nicely explained. Just to add one thing.
If anyone wants to set the folder name where the files will be dumped, then add Package level variable and add this into your expression. For example, if I want to put all the files under folder "F:\MSBI\CODE_SAMPLE\OUTPUT_FILES\" I will create Package level variable "FilePath" of String type and set it's value to "F:\MSBI\CODE_SAMPLE\OUTPUT_FILES\" {quotes removed}. Then in Flat File Connection Manager add ConnectionString property add below:

@[$Project::FilePath] + "PersonData_" + (DT_STR, 4, 1252) DATEPART( "YYYY", GETDATE() ) + RIGHT( "0" + (DT_STR,4,1252) DATEPART( "MM" , GETDATE() ), 2) + RIGHT( "0" +

(DT_STR,4,1252) DATEPART( "DD" , GETDATE() ), 2) + RIGHT( "0" + (DT_STR,4,1252)DATEPART( "HH" , GETDATE() ), 2) + RIGHT( "0" + (DT_STR,4,1252)DATEPART( "MI" , GETDATE

() ), 2) + RIGHT( "0" + (DT_STR,4,1252)DATEPART( "SS" , GETDATE() ), 2) + ".txt"

This will create value as "PersonData_20161112185512.txt" and file location will be

F:\MSBI\CODE_SAMPLE\OUTPUT_FILES\

Anonymous said...

This is great! I went with the final example you sent to Java Mann. Thanks for the step-by-step directions - with pictures, no less! I see this has been here since 2009 with comments up through November 2016 so far. It is proof you found an insight lots of us really need. This is not self-evident anywhere in the flat file connection manager properties.

Fabio said...

Hello Adam,

Great tip!, just what I was looking for

Thank you so much!

Sathya said...

wow very well information you had shared here. This is important thing because when we are running a business first we have to make our focusing area and what we are ready for serve among the people. And really i am much inspired with this 10 steps. Surely it will be useful for craeting the better mindset among our business. Thank you and i am looking forward your more information here

MSBI Training in Chennai

Informatica Training in Chennai

Dataware Housing Training in Chennai

Nambr said...

Works like a charm!
Thank you!

Anonymous said...

I'm  here to share my testimony of what a good trusted loan company did for me. My name is Nikita Tanya, from Russian and I’m a lovely mother of 3 kids I lost my funds on trying to get a loan it was so hard for me and my children, I went online to seek for a loan assistance  all hope was lost until one faithful day when I met this friend of mine who recently secured a loan from a very honest man Mr, Benjamin. She introduced me to this honest loan officer Mr, Benjamin  who helped me get a loan in within 5 working days, I will forever be grateful to Mr Benjamin, for helping me get back on feet again. You can contact Mr Benjamin via email: 247officedept@gmail.com    they do not know I’m doing this for them, but i just have to do it because a lot of people are out there who are in need of a loan assistance  please come to this honest man  and  you can be save as well .WhatsApp:(+1 989-394-3740)

James Zicrov said...

I can say that is a very common but important question in SSIS and its relatable components but I guess this was a very informative and detailed blog post.

SSIS Upsert