Set bit based on source data in Data transformation Task 1

[Glowworm27]

Hello gurus,

I have a DTS Package I am designing, and I am importing text files, into temp table.

From this temp table I am populating another table, here is the question.

IN the temp table I have Firstname and LastName fields.
If the Firstname field is NULL it is a buisness customer.

I want to import the data into our Customers Table, and as it is importing check the Firstname field to see if it is NULL, if it is, set the ISbusiness Bit to a 1 if its NOT NULL Set the ISBusiness Bit to a 0.

Can this be done during the Transform Data Task? IF so How.

thanks in advance.



George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[JohnDTampaBay]

Yes, if you use an ActiveX script transformation. Make the Source column Firstname and the Destination ISBusiness. Specify an ActiveX transformation. Your script will probably look something like this:

Function MAIN
  If IsNull(DTSSource("Firstname")) = 1 THEN
    DTSDestination("ISBusiness") = 1
  Else _
    DTSDestination("ISBusiness") = 0
  End If

  Main = DTSTransformStat_OK
End Function

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Glowworm27]

JohnDTampaBay,


Thanks, I had a feeling it was that simple. Im in the Tampabay area too, in Clearwater actually

Keep up the good work! Star for you man!

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

Hey JohnDTampaBay,

I had to modify the Activex Script Slightly to get it to work. Its correct for the most part, but in the "If Then" Statement

The IsNull(<<Expresion>>) returns True or False, not an Integer Value so it should be written like this...

Function MAIN
  If IsNull(DTSSource("Firstname")) THEN
    DTSDestination("ISBusiness") = 1
  Else _
    DTSDestination("ISBusiness") = 0
  End If

  Main = DTSTransformStat_OK
End Function

Thanks it was just what I needed!!!!


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips