SSIS Null handling, script task

Question

I am having trouble with MS SSIS. I would like to download and upload stuff to database. I am looking for the best practice, or why i get an error.

I use the control flow (other tasks are called). There are nulls in a column in the data I download. For this I can't use string or integer, or else it exits with error (execute sql task). So I use object. Example: Variable sBorderName is object, the data from a column goes here.

I would like to upload this to an other database table. There can also be null in that colum for example. I cannot upload the object variable, because I get database type missmatch.

When I try to eliminate the null-s with a script task (C#), I use the following: sBorderName is object sBorderNameSafe is string

if (Dts.Variables["User::sBorderName"].Value == null)
{
    Dts.Variables["User::sBorderNameSafe"].Value = "NULL ERROR";
}
else
{
    Dts.Variables["User::sBorderNameSafe"].Value = 
        (String)Dts.Variables["User::sBorderName"].Value;
}

I get runtime error, with no hint on what is wrong. The error originates from the else case. I have double checked the variables, scope, write/read permissions etc.

Can you please help me? What is wrong with this? What is the way to solve the problem if you have a better idea?

Sziro

UPDATE:

Dts.Events.FireWarning(14, "Display Script", "sBorderName is:" + Dts.Variables["User::sBorderName"].Value, "", 0);

does work, it shows "sBorderName is: "

However every casting of the value exits with runtime error

(String)Dts.Variables["User::sBorderName"].Value

How can one handle this correctly?

I'm unclear on your solution. You state you need to "download and upload stuff to database." Do you mean you need to SELECT and INSERT/UPDATE data or are you stating that you need to ftp files to and fro? — billinkc
Yes, plain old select, and insert. I have to use control flow however with execute sql task. (So not the data flow part.) — Sziro
Is there a reason you don't use a data flow for all of this? — billinkc
I need to run tasks based on the data, and have to transform the data. (Like real big switch cases and string and date merging and such ugly things.) — Sziro

Sziro Sziro · Accepted Answer · 2012-07-12T10:45:27

Convert.ToString(Dts.Variables("sBorderName ").Value)

Does the trick, if the variable can be null sometimes.

SSIS Null handling, script task

1 Answers