Write an SSIS Variable to a text file

Question

Write an SSIS Variable to a text file

vince.iacoboni@db.com

Hall of Fame

Points: 3965
More actions
February 15, 2006 at 8:26 am

#189240

This seems simple but I haven't found a clean way to get it done. I have a Script component that creates FK Drop and FK Create scripts for my database, so that I can truncate tables to be loaded. The generated FK scripts go into their variables, User::FkDropScript and User::FkCreateScript. I use those variables as the basis for an Execute SQL task, and it works great.
Now, I want to save those scripts to a file. If, for example, we have an error after the FKs are dropped but before the FKs are recreated, I'll want to recreate the FKs, and I want them in a file that can be easily executed.
So just how do I accomplish this (aside from a Script Task)? Flat File Destinations require a data pipeline from a source component that I don't seem to be able to "dummy up" and replace with the variable.
Ideally, the connection to the text output file to would be set in the package configuration file.
Thanks for any ideas...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 1

^{_{I am not quite sure what you are talking about or how to solve your issue. What I have found to be of value when something blows up in my package is using a Sequence Container. That way if it blows up it will start on the step it blew up at. So you can solve the issue and then run it again.}}

vince.iacoboni@db.com Hall of Fame Points: 3965 More actions · Answer 2

Thanks for replying. I use Sequence Containers liberally, and they are a big help. In the example I mentioned, I also use the "Completed" precedence constraint, so that even if my load fails, the FKs will get put back on the tables.

My problem is really as simple as this: I've created an SSIS User variable, and now I want to put that variable into a file. How?

Isaac Spalter SSC Enthusiast Points: 101 More actions · Answer 3

Hello,

My idea is to create a simple source file with one line. This initiates the data flow. Then you add a Derived Column Transformation object, and place you variable as the added column. The stream includes now the source column (from the input text file), and the variable. On the output you use only the variable.

Isaac