Posts Tagged ‘ssis’

SSIS: How to add a timestamp to a Excel File output path.

// September 22nd, 2008 // 15 Comments » // sql, SQL Server, Systems Integration

I have been working with SSIS lately and I have been having some troubles adding a timestamp to the output excel file name. I have found several sources on how to do it, the best one (except for the video’s size which is too small) is: tutorial.

It did help, but it wasn’t what I wanted, because you have to hardcode the file path in the expression, making the developer or deployer hard code the path in two places. And I didn’t want the deployment team to add the path into the actual expression box, any mistakes or typos and that might break the package. So I wanted to extract the actual file path, from the timestamp generation expression.

So this is my solution:

1) Create a Package-wide variable called OutputFilePath

2) Set the Variable’s Value to the expected file path without the timestamp, like a template.

3) Go into the Excel data source and enter the following expression, linking the expression to the ExcelFilePath property:

REPLACE(@OutputFilePath, “.xls”, “_”+ REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(), “-“,””)+REPLACE((DT_WSTR, 10)(DT_DBTIME)GETDATE(), “:”,””)+”.xls”)

4) Run your package, and test.

Voila!