SSIS save string variable to text file

Question

It seems like it should be simple but as of yet I havent found a way to save the value stored in an SSIS string variable to a text file. I've looked at using the flat file destination inside of a data flow but that requires a data flow source.

Any ideas on how to do this?

Answer 1

Use a script task.

---

I just tried this. I created a File connection manager, with the connection string pointing to the file I wanted to write to. I then created a string variable containing the text to write.

I added a Script Task, specified my string variable in the Read Only Variables list, then clicked Edit Script. The script was as follows:

    public void Main()
    {
        ConnectionManager cm = Dts.Connections["File.tmp"];
        var path = cm.ConnectionString;
        var textToWrite = (string)Dts.Variables["User::StringVariable"].Value;
        System.IO.File.WriteAllText(path, textToWrite);

        Dts.TaskResult = (int)ScriptResults.Success;
    }

This worked with no problems.

Answer 2

It's possible to use a Derived Column transformation to write the value of a variable into a column. The problem is that it needs a source to drive it, and there's no stock data source you can use that just spits out a null row onto the pipeline.

So, either you repurpose a single-row source to drive the derived column transformation, or you do what another answer suggests, and do it with a Script source.

Answer 3

Here's a little sample of some code that worked in a SQL CLR in C#. You'll need to use VB if you're on 2005 I believe. The script task also needs the read variable property set to MyVariable to make the value of your variable available to it.

            // create a writer and open the file

            TextWriter tw = new StreamWriter("\\\\server\\share$\\myfile.txt");

            // write a line of text to the file

                tw.WriteLine(Dts.Variables("MyVariable").Value);

            // close the stream
            tw.Close();

Answer 4

I did it the way you described. I already had a oledb connection manager defined so I used an OLE DB Source and used the SQL Command data access mode. I used a simple query

select getdate() as dt

to just get it out of the way plus now I know the date of my variable pull. Then I used a Derived Column Transform to make my package variables available and wrote it out to a flat file.

Elegant...no but it gets the job done.