ActiveX Script Problems
-
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
-
One option is to use some logic to check the source columns before you concatenate them.
Eg:
If DTSSource("Col009") is null
DTSDestination("CLO") = DTSSource("Col010")
elseif DTSSource("Col010") is null
DTSDestination("CLO") = DTSSource("Col009")
--------------------
Colt 45 - the original point and click interface
-
phillcart - thanks for the post! I have tried several variations of your suggestion and come up with syntax errors.
In the ActiveX Script window it shows that I am using JavaScript. I am not an expert at JavaScript...do you know how to insert your logic into the following:
//*********************************************************
*************
// Java Transformation Script
//*********************************************************
***************
// Copy each source column to the destination column
function Main()
{
DTSDestination("CLO") = DTSSource("Col009") +
DTSSource("Col010");
return(DTSTransformStat_OK);
}
-
How about something like this:
function Main()
{
if (DTSSource("Col009") == null)
{
DTSDestination("CLO") = DTSSource("Col010");
}
else
{
DTSDestination("CLO") = DTSSource("Col009");
}
return(DTSTransformStat_OK);
}
Mike
-
Are these columns really Null?
I would suggest:
function Main()
{
if (DTSSource("Col009") == "")
{
DTSDestination("CLO") = DTSSource("Col010");
}
else
{
DTSDestination("CLO") = DTSSource("Col009");
}
return(DTSTransformStat_OK);
}
-
mkeast and D'Arcy Irvine -
Thank you for your help!!!!! This partially works. I am getting the values now from column 009 without the null appended to it, but i am not getting the values from column 10 at all.
If i change the code to look at column 010 first, then i get the reverse. I am not able to get all the values at one time.
Thanks again for your help. This is a major break through!!!
-
D'Arcy had asked the question if these columns are really null...
They are null. In the text file in one record column 8,9,10,11 look like this: |WWW|IW555124||jm9999|
the next record looks like this: |WWW||ACS3411190|kb4040
-
Actually,I'd use both checks to be safe:
function Main()
{
if (DTSSource("Col009") == null || DTSSource("Col009") == "")
{
DTSDestination("CLO") = DTSSource("Col010");
}
else
{
DTSDestination("CLO") = DTSSource("Col009");
}
return(DTSTransformStat_OK);
}
Mike
-
mkeast - I tried your last suggestion, same results. I went into the .txt file and typed n/a into the null field on the first record that wasn't coming across, and then adjusted the code to read the n/a and then it worked.
for some reason, the transformation is not reading that there is a null value, it must think there is a value in there, however, there is no space between the pipes.
unbelievable. so close....this has been driving me crazy for two months.
-
Hmm..that is strange. Is DTS returning the string "null"? Maybe you could try something like this:
function Main()
{
var c9 = DTSSource("Col009")
var c10 = DTSSource("Col010")
if (c9 == null || c9 = "null")
c9 = "";
if (c10 == null || c9 = "null")
c10 = "";
DTSDestination("CLO") = c9 + c10;
return(DTSTransformStat_OK);
}
Mike
-
mkeast -
this new one gives me a syntax error. yes, it is giving me these results:
IW555124null
nullACS3411190
I modified your last code to this:
function Main()
{
var c9 = DTSSource("Col009")
var c10 = DTSSource("Col010")
if (c9 == null)
c9 = "";
if (c10 == null)
c10 = "";
DTSDestination("CLO") = c9 + c10;
return(DTSTransformStat_OK);
}
And i get the null appended to my values again. That tells me that the transformation is actually creating that string. it is not actually picking the string up from the txt file. i am beginning to think it is not possible to accomplish this feat. Something I thought I would never say!
-
Sorry, I typed the assignment operator ( = ) instead of the equals operator ( == ). Corrected code is shown below:
function Main()
{
var c9 = DTSSource("Col009")
var c10 = DTSSource("Col010")
if (c9 == null || c9 == "null")
c9 = "";
if (c10 == null || c9 == "null")
c10 = "";
DTSDestination("CLO") = c9 + c10;
return(DTSTransformStat_OK);
}
Mike
-
Check for nonprinting characters as I had text file similar that had a CRLF inserted in one text field which caused it diplay as blank but it was not null. I finally figured it out when I checked the length of the field.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply