I am using a dts package to move data from a .txt file to
a data table. In that file, there are two columns of
information that I need to combine into one column in the
table. here is an example:

col1 col2
xyz123 <null>
xyz234 <null>
<null> abc333
<null> abc757

If every instance, if there is a value in column 1 there
will be a <null> in column 2. The same is true in the
reverse. I want the data in the table after the move to
look like this:

combined
column
xyz123
xyz234
abc333
abc757

However, when I do the transformation, the package appends
the word null to the field. so my actual data looks like
this:

actual
xyz123null
xyz234null
nullabc333
nullabc757

Here is the activex script:

//************************************************** *******
*************
// Java Transformation Script
//************************************************** *******
***************

// Copy each source column to the destination column
function Main()
{
DTSDestination("CLO") = DTSSource("Col009") +
DTSSource("Col010");
return(DTSTransformStat_OK);
}

Help!! Marcus

Easiest way?

I would import to a working table. You can then use COALESCE() to get the
first NON Null value so

COALESCE(col1,col2) will give you whichever attribute is not null from the
two.

You can insert this to your real table



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"marcus" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan,

Thanks for your help. I have never used COALESCE(). Not
sure where I would use that function.

Is it not possible to modify the ActiveX script
to "ignore" the null? Or, to filter out the word null
during the transformation?

Marcus

It is yes but the problem with the Active Script transform is that it does
everything row*row.

Import the file to a working table.
Can I guess that the file when you say NULL has the word NULL and not a
blank space?

In an Active Script transform you could do


If DTSSource("Col1") = "NULL" THEN
DTSDestination("Col") = DTSSource("Col2")
Else
DTSDestination("Col") = DTSSource("Col1")
End if

This is following the rule that only one of these columns will have a value
you want to use.





--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"marcus" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Allan,

The file i am working with is a .txt file and uses pipe
delimeters. if there is nothing in the column then it
looks like this: || there is actually no space at all
between the pipes. Still picks it up as the word null.

I am not a JavaScript person...so I just copied your code
below, and received a syntax error.

I do agree that setting up a working table would be a good
plan, however, this is a site that was working fine, then
my data provider modified the data I am receiving, and
will not revert back. the site will be retired soon, we
just do not know when, and at this point the site is not
working because of the data change.

I actually wrote a page with ColdFusion that will go in
and take out the "null" after the fact, but I dont want to
invoke the CF server if it isnt necessary at that stage of
the data transformation.