Blog: Using #Excel Templates in #SSIS

Following up on my previous post, Creating #Excel Templates in #SQLServer #SSIS, this post walks you through how to use that template for new files. I should note that I had to change the data type for the Column_Default from NVARCHAR(4000) to TEXT because Excel didn’t like a column length of 4000 characters.

There are two ways to use a template, with a Script Task or with a File System Task. Today, I’ll cover the File System Task.

File System Task Method –

In your Control Flow, pull in a File System Task (FST) and connect the success precedence constraint to your Data Flow Task. This is important! If the FST fails, you can’t load your data and the package will (mysteriously) fail. If you want other behavior upon FST failure, you can set up error handling or reroute failure precedence constraints to another task.

Edit the FST and set IsDestinationPathVariable to True, click “New variable” in the DestinationVariable box to create the variable for the destination. For now, the variable Name will be DestinationPath, we’ll leave the Namespace as User and the Value type as String, and enter the Value as the same as our Template: D:\Temp\MyDatabaseSchema.xls.

Set OverwriteDestination to True so the package doesn’t error out if the file doesn’t get removed properly. Leave Operation as “Copy file.” Moving will delete the template and we don’t want that. We just want a copy of the template with a new name.

On Source Connection, leave IsSourcePathVariable as False and click “New connection” in the SourceConnection box. Pointed the path to the Excel template file. Click OK.

This is what the final version of the File System Task will look like:

NOTE: You can use a connection for the Destination, but SSIS doesn’t like creating connections for files that don’t exist. The workaround is to manually copy the Template and rename it for this new connection. After you’ve finished creating the package, you can then delete the non-template file and it will work fine. I use a variable so I don’t have to deal with that outside-the-package nonsense, and because I always forget to delete the fake files.

Change the file name of the DestinationPath variable. Right-click the package whitespace and choose Variables (if you don’t already have your Variables window open). You’ll see the following:

This is where it gets funky. You can either name your template file a different name from your regular file (I usually call my template “MyFileTemplate” and the regular file “MyFile”) or add a date / time to the new file name.

If you want to use the date arrangement, click inside the Name area so the variable is highlighted and your Properties window changes to the variable properties. Find the property called “Expression” and click inside the value box (the empty box to the right of the property name). An box with three periods (ellipses) will appear. Click on that and the Expression Builder box will appear.

In the Expression box, enter your path name in double quotes without the file extension. In this case, it would be “D:\Temp\MyDatabaseSchema_”. Then concatenate this with the SSIS version of DatePart() to get the current year, month, and day. Finally, add the “.xls” to the expression, double up your backslashes because that’s an escape character, and you’re set. Your code should look similar to this:

“D:\\Temp\\MyDatabaseSchema_” + (DT_STR, 4, 1252) YEAR( GETDATE()  )  +  (LEN((DT_STR, 2, 1252) MONTH( GETDATE()  ) ) == 1 ? (“0” + (DT_STR, 2, 1252)  MONTH( GETDATE()  ) ) :  (DT_STR, 2, 1252)  MONTH( GETDATE()  ) )  + (LEN((DT_STR, 2, 1252)DAY( GETDATE() ) ) == 1 ? (“0” + (DT_STR, 2, 1252)  DAY( GETDATE()  ) ) :  (DT_STR, 2, 1252)  DAY( GETDATE()  ) ) + “.xls”

Test the code by clicking “Evaluate Expression.” You should see the proper file path and file name, along with your date. If you get errors, you’ll need to fix them or you won’t be able to get out of Expression Builder. Once the code works, click OK, then set the variable’s “EvaluateAsExpression” property to True.

The final thing to do is go fix your Excel Connection Manager to use the new file rather than the template. Highlight the connection manager and click in the Expressions property. A Property Expressions box will pop up. Under Property, choose ExcelFilePath. Under Expression, click the ellipses box. It will take you back to Expression Builder. In the upper left hand corner, click the + sign next to Variables. Double-click or click-n-drag User::DestinationPath down into the Expression box.

If you used the date code above, click evaluate expression, and see the original file name, you forgot to set DestinationPath’s EvaluateAsExpression to True. Don’t panic. You can fix it after this step.

Click OK on the Expression Builder, than on the Property Expressions window. Set the connection’s DelayValidation property to True. Then go back and fix any variable issues you may have encountered.

Save your package–it’s always a good idea to save multiple times during this process–and test it to make sure you didn’t miss anything.

Script Task coming later. I hope this series helps you out.