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
– Nick.Mc, Commented Mar 18, 2013 at 0:48

Vitthal · Accepted Answer · 2013-03-15 12:47:35Z

1

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.

answered Mar 15, 2013 at 12:47

Vitthal

5463 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

Attribution: this answer is copied in its entirety from stackoverflow.com/a/13459351/772086

Collectives™ on Stack Overflow

set filepath from database in SSIS variable for logging

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related