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

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 2

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 3

All it takes is one line of code in a simple Script task. No other dependencies, such as a connection manager, are needed.

Here's what it would look like in C#:

public void Main()
{
    string variableValue = Dts.Variables["TheVariable"].Value.ToString();
    string outputFile = Dts.Variables["Path"].Value.ToString();

    System.IO.File.WriteAllText(outputFile, variableValue);

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

Obviously the most important line here is the one containing the WriteAllText function call.

The Path variable should contain a full path + filename for the output file.