SSIS – Starter kit for the uninitiated – Dynamic File Paths

Continuing from our previous post here we explore how to configure tasks to behave dynamically. More specifically how to configure the file connection manager to accept dynamic paths to store the output of our web service task.

Navigate to the connection managers in the bottom pane of SSIS and right click the Connection manager we created when defining the output for the web service task and go to properties.
In the properties window typically located in the right hand side of the screen. Click the ellipse button under Expression as shown in the screenshot.
Find the property called connection string. This property defines the file path for the output file.

Enter the text as shown below.

Note that Double Backslash is required for filepaths and we are using a variable highlighted in red as the filename so that each country gets a file of its own.

 

Press OK

Once done there should be an fx in the symbol for the connection manager, this lets you know that connection manager is configured dynamically at run time.

However we do not have a filepath right now and so you might see a red X on the task for the web service.

 

You can delay validating filepaths by right clicking the task with the validation error and navigating to its properties.

Under the properties there is an entry for Delay Validation set it to true and the error will disappear until runtime.

If at the time of executing this task the path is still not defined you will get an error else it will pick up the value automatically.

In the next post we use PowerShell to format the data properly using the Execute Process Task