Looking for a Date – What’s in a Name?

I’ve blogged about using SSIS to create text files with dynamic file names before, but there’s another scenario that keeps coming up time and again. It looks something like this:

"I need to export data from a database into a text file. I have this working, but I need the text file to have a name that’s based on the current date, like MyOutputFilePrefix_YYYY-MM-DD-HH-MM-SS.txt. How do I do this?"

It should come as no surprise that the short answer is "use expressions." Expressions are (in my humble opinion, of course) the single most powerful feature in the SSIS developer’s toolbox, and are the primary mechanism for adding dynamic functionality to SSIS packages.

Bur of course "use expressions" doesn’t give you all the information you need, so here is a quick walkthrough that shows all of the steps involved.

  1. Start off by creating or locating a flat file that has the format that you need. This will give you a head-start for configuring your Flat File Connection Manager later on. Since I’m working on a simple demo, my text file is appropriately simple:

    01

  2. Next, add a Flat File Connection Manager to your SSIS package, and update its properties to reference the sample flat file you created above:

    02

  3. Make sure that the columns you defined in the file are being parsed correctly:

    03

  4. And update the properties (generally the data type) of the columns as needed. In this scenario, we’re setting the data type of the ID column to be a four-byte signed integer:

    04

  5. Next, add an OLE DB Connection Manager (or whatever makes sense for your source database) to your package, configure it as necessary[1], and add a Data Flow task to the package as well:

    05

  6. Inside the Data Flow task, add an OLE DB Source component (or whatever makes sense for your source database) and configure it with your source query:

    06

  7. Next, add a Flat File Destination component to the data flow and connect it to the source component:

    07

  8. Configure the Flat File Destination to use the Flat File Connection Manager and ensure that its column mappings are configured correctly:

    08a
    08b

  9. Run the package to make sure that it works:

    09

  10. And test it by checking the contents of the target text file:

    10

  11. And now we’re ready to start. That’s right, the last 10 steps were included to show how I got to the starting point for adding the dynamic functionality that is the whole point of the post. What we’ve done above is set up a simple package and data flow that exports data to a text file, which is (I assume) where the people who keep asking the question at the top of the post are getting stuck. And since we walked through it step-by-step together, everyone can see that there is no prestidigitation involved.
    So let’s start off by creating three different package variables, Foldername, FileName and FilePath:

    11

  12. Set the value property of the FolderName variable to the path of the folder where you want your files to be created:

    12

  13. Set the EvaluateAsExpression property of the FileName variable to True and set the Expression property to this expression:
  14. "MyOutputFilePrefix_" + (DT_WSTR,4)YEAR(@[System::StartTime]) + "-"
        + RIGHT("0" + (DT_WSTR,2)MONTH(@[System::StartTime]), 2) + "-"
        + RIGHT("0" + (DT_WSTR,2)DAY( @[System::StartTime]), 2) + "-"
        + RIGHT("0" + (DT_WSTR,2)DATEPART("hh", @[System::StartTime]), 2) + "-"
        + RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[System::StartTime]), 2) + "-"
        + RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[System::StartTime]), 2) + ".txt"

    This will evaluate to a filename like MyOutputFilePrefix_YYYY-MM-DD-HH-MM-SS.txt, as requested above. [2]

    13a

    13b 

  15. Set the EvaluateAsExpression property of the FilePath variable to True and set the Expression property to this expression:

    @[User::FolderName] +  @[User::FileName]

    14 

  16. At this point we have a variable, FilePath, that will always evaluate to a fully qualified file path that is dynamically updated to reflect the time the package started executing. This is exactly what we need, right? So the one thing that remains to be done is to use this value to create the files being exported by the data flow. And to do this we just need to update the Flat File Connection Manager to base its ConnectionString property on this expression:

    @[User::FilePath]

    15a
    15b

  17. Then, run the package a few times, and take a look in the output folder:

    16 

Pretty simple, eh? Once we had the base package (the starting point for the whole exercise) set up, it was simply a matter of setting a few property expressions to make the whole thing work as needed.

It’s also worth noting that the technique where there are three variables to contain the file path is completely optional as well – you could just as easily build a single (although admittedly more complex) expression for the ConnectionString property, and skip the variables entirely. I personally prefer having the variables to contain the intermediate values that go into the file path because in most situations there are opportunities for reuse of these intermediate values, and having them stored in variables make that reuse very simple.

And of course, you can easily update the FileName expression as needed to get the file name you require. The SQLIS.com wiki has a page dedicated to date expressions, and I will generally start there whenever I need to write a date expression myself. In fact, you’ll probably notice that the expression I used for the FileName variable was blatantly stolen (and then modified just a little) from one of the examples on the SQLIS wiki site.

So once again we’ve witnessed the power of expressions in SSIS. By setting just a few properties to evaluate as expressions, we’ve quickly and easily added dynamic functionality to a package. What could be better?

 

[1] Since the purpose of this post is to focus on the dynamic file name, I’m being deliberately vague here. This is stuff that you should know already, so if you don’t, check here: New to SSIS- Start Here!

[2] Although this example is using the @[System::StartTime] variable, you can use any date variable (or function, like GETDATE) for your expressions – use the one that makes the most sense for you.

Advertisements

About ssimagine

My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
This entry was posted in BI, SQL Server, SSIS. Bookmark the permalink.

4 Responses to Looking for a Date – What’s in a Name?

  1. Greg says:

    Thanks Matthew. I’m new to SSIS and your post was well written and easy to understand.

  2. Exactly what I was looking for Thank you.Kevin

  3. Mindset says:

    Thanks for the clear and complete example. New to expressions, but loving them already.Mike

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