How to set up Dynamic Properties of Tasks in Data Transformation Services

This DTS task allows you to change things like the output path by asking it to pick up the output path from a file.

The contents of this path can then be changed from a job using SQL.

To start with, create a simple DTS package that will Query a table and output the results to a text file.

The query I have used is…

— Use Northwind

SELECT LastName, FirstName, HireDate, City, PostalCode

FROM Employees

The output file I have used to start with is:

C:\OutputFolder\FileNameDoc.txt

Later, we will be changing this property dynamically.

View this property by right-clicking the output text file and going to Properties.

Create a text file locally, on the C:\ drive and change it’s extension to be an INI file.

Use the following text in the INI file:

[File path]

value = C:\08-11-2015FileNameManual.txt

Now use a DTS Dynamic Property Task in the DTS package to point to the contents of this file to use this File Path property.

Back in the DTS package, drag a Dynamic Properties Task to the work surface.

Click the Add button

Expand the left panel out under Connections to Text File.

On the right, double click the DataSource row

For the File box, browse to the INI file just created and select from the dropdowns Section to equal File Path and Key to equal Value.

Click OK a few times to save.

The DTS will now get its output path from the INI file.

To prove this, we can change the text in the INI file to output to a new path. Go to the INI file.

Change the text to:

[File path]

value = C:\10-11-2015FileNameAuto.txt

Run the DTS.

When the DTS completes, a new file will be created in the destination.

This file name will correspond to the new name we specified in the INI file.

Now, to change the contents of the INI file using a job.

It can be changed to use a date using T-SQL.

Using Query Analyser run the following query against the Northwind database:

Set NoCount On

SELECT ‘[File path]’

SELECT ‘value = C:\’ + CONVERT(CHAR(10),GETDATE(), 105) + ‘FileNameAuto.txt’

Put this into a Stored Procedure run against the Northwind database:

CREATE PROCEDURE [dbo].[sp_DynPropDoc] AS

Set NoCount On

SELECT ‘[File path]’

SELECT ‘value = C:\’ + CONVERT(CHAR(10),GETDATE(), 105) + ‘FileName.txt’

Right click on the jobs to make a new job and call it DTSDynamicPropertiesFile

Create a new step and give it a name. Select the type to be a CMD command. Put in this code for the command:

osql -E -o C:\FileNameDoc.ini -d Northwind -Q “sp_DynPropDoc” -w500

Create a schedule for this job and test it out by running it. It will use OSQL to run the

sp_DynPropDoc stored procedure to output to the FileNameDoc.INI file.

We now have the following a job that creates an INI file.

We can run this everyday in the morning and it will change the text in the INI file to reflect the date.

We then have a DTS with a Dynamic Properties Task that can pick up the info from the INI file to make a new output name.

We can run this DTS when ever we want or schedule it to run after our job that creates the INI file.