12/11/2011

SSIS Package to Excel Dynamic File Name

Well, on my part time job, I was requested to create an SSIS package to dynamically create a TEXT file with the file name to include the date run.

Sure enough, I did this.

Then the user changed the export file type from Text to Excel.

I thought this was a no brainer.

Until I got into the guts of the code.

I found this link as an example:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bda433aa-c8f8-47c9-9e56-efd20b8354ac

It ran in development and all was well.

Until the next day, the package would not compile.

Why?  Because the file was not already created with the next day's date.

So I read and re-read the article above and it just didn't make sense.

Until, poof, I figured it out.

You have to create the Execute SQL Script at the beginning of the package, referencing the Excel Destination, in order to create the spreadsheet in advance.

I then deleted all the Excel files used in testing, re-ran the package, and sure enough the new Excel file with correct file name appears.

And so it goes!

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Thoughts to Ponder