set filepath from database in SSIS variable for logging

Question

I have some paths in my database table like this:

ID          PATHXML              PATHPICT               PATHSONG
1         D:\XML\Here        D:\Picture\Image      D:\Blur\Song2
2         D:\XML\File        D:\Picture\X-Files    D:\IRONMAIDEN\Fearofthedark

I want to switch on logging in SSIS and save this log result to PathSong eg. D:\Blur\Song2\eventlog.log

I created a variable to get this path. But when I create an expression using this variable, it doesn't work. So what can I do to fix this?

I suggest you never use the words `doesn't work` when describing the issue you are having. — Nick.Mc, Mar 18 '13 at 00:48

score 1 · Accepted Answer · answered Mar 15 '13 at 12:47

Accessing package variables in a Script Component (of a Data Flow Task) is not the same as accessing package variables in a Script Task. For a Script Component, you first need to open the Script Transformation Editor (right-click on the component and select "Edit..."). In the Custom Properties section of the Script tab, you can enter (or select) the properties you want to make available to the script, either on a read-only or read-write basis: enter image description here

Then, within the script itself, the variables will be available as strongly-typed properties of the Variables object:

// Modify as necessary
public override void PreExecute()
{
    base.PreExecute();
    string thePath = Variables.FilePath;
    // Do something ...
}

public override void PostExecute()
{
    base.PostExecute();
    string theNewValue = "";
    // Do something to figure out the new value...
    Variables.FilePath = theNewValue;
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    string thePath = Variables.FilePath;
    // Do whatever needs doing here ...
}

One important caveat: if you need to write to a package variable, you can only do so in the PostExecute() method.

Regarding the code snippet:

IDTSVariables100 varCollection = null;
this.VariableDispenser.LockForRead("User::FilePath");
string XlsFile;

XlsFile = varCollection["User::FilePath"].Value.ToString();

varCollection is initialized to null and never set to a valid value. Thus, any attempt to dereference it will fail.

Attribution: this answer is copied in its entirety from http://stackoverflow.com/a/13459351/772086 — Mike, Sep 30 '14 at 16:55

set filepath from database in SSIS variable for logging

1 Answers1