Auto 'refresh' of tranformations

[DBAchick]

Summary of what my DTS Package accomplishes:

A stored procedure is executed to populate a table in the database with the data I need using an execute sql task.

Using a transform data task, I tranform the data to a destination text file.

All of these task 'values' are set using the dynamic properties task. That way, I can set up the package once and when the package is executed, all of the 'values' for each task are reset at run time.

Here is the problem...If I add a column to my table & change my stored procedure to populate it, my package does not produce that column in the destination text file unless I go in and 'refresh' the transformations!

(I know that 'refresh' is not the actual term for it, but I was trying to keep this simple.)

Is there some code I could put in an activeX script that would basically reset the transformations before the data is pushed out to the text file?

I am trying to mass produce this solution so the code would have to consider multiple column changes.

Any help is appreciated!

 

[EdwinGene]

Since you are just exporting to a text file, create another table with one column. Make this one column wide enough to hold the longest text file record. In your existing stored procedure, populate each record in this new table with the destination text record by combining the columns from the table you are now exporting, plus any column delimiters you might be using. Then, export this new, single-column table to the destination text file.

The benefits of doing it this way are:

1. If you add any new columns to, or remove any columns from your export data you only need to change your stored procedure to accomodate these changes into the export table record.

2. You can change the width of the column in the new export table without having to modify the transform data task. It will still export to the data file with no problem since it is still only exporting the one column.