Script Component Transformation - SSIS 2005

Question

Script Component Transformation - SSIS 2005

umas

Ten Centuries

Points: 1219
More actions
August 21, 2009 at 12:07 pm

#213584

I am trying to convert a DTS to a SSIS package in 2005 and I am stuck with one of the transformations.
I have to check for the value "Credit" in the DocumentType column (destination table) and if it is found, I should map it to the DocumentNo column in the source table. I am not sure if I have to use a Destination Script Component and would like to know how this component would connect to the OLEDB destination table.
I have the following script below used in the DTS.
Function Main()
DTSDestination("DocumentType") = "CREDIT"
Main = DTSTransformStat_OK
End Function
Any guidance or direction on how to solve this issue would be much appreciated. Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

binko Default port Points: 1408 More actions · Answer 1

I'm not sure I understand what exactly you are trying to accomplish, but it sounds like you should look into merge join. if you post ddl and some insert statements, i'll be able to help you more.

umas Ten Centuries Points: 1219 More actions · Answer 2

Thank you for replying.

Please see the attachment that shows the transformation. I need help with the following as I am not familiar with .NET script.

1)How do I rewrite the code (from the attachment) in ActiveX Script Component

2)Should I use a destination or source Transformation

3)How will it connect with the data flow

binko Default port Points: 1408 More actions · Answer 3

umas (8/21/2009)
I have to check for the value "Credit" in the DocumentType column (destination table) and if it is found, I should map it to the DocumentNo column in the source table. I am not sure if I have to use a Destination Script Component and would like to know how this component would connect to the OLEDB destination table.

-are you trying to replace "CREDIT" with DocumentNo in the destination? Is the destination DocumentNo the same as source DocumentNo? (from your attachment)

I have the following script below used in the DTS.
Function Main()
DTSDestination("DocumentType") = "CREDIT"
Main = DTSTransformStat_OK
End Function

-I didn't have to deal with DTS much, but this looks to me like the package is setting DTSDestination("DocumentType") to "credit".

have you ever considered using the functionality ssis offers instead of trying to convert the package 1-to-1?

umas Ten Centuries Points: 1219 More actions · Answer 4

I am trying to map the documenttype to documentno in the source table if the type is "CREDIT".

I don't know which functionality in SSIS I can use to do this type of transformation. Do I have to use a lookup?

binko Default port Points: 1408 More actions · Answer 5

i'm still not sure what exactly you are trying to accomplish. if you are just downloading tables from source to destination, and need to replace type with documentno if type="credit", you could use sql statement in datasource, something like

SELECT COMPANY

,[LINENO]

,DOCUMENTNO

,SELLTOCUSTOMERNO

,CASE

WHEN TYPE = 'credit' THEN DOCUMENTNO

ELSE TYPE

END AS TYPE

,NO

,QUANTITY

,AMOUNT

,POSTINGDATE

FROM

DBO.SOURCE

If you are just trying to update destination table with source data, you could use sql statement in source again, select primary key and document no

SELECT [lineno], documentno, type

FROM source

WHERE (type = 'credit')

(I assume that lineno is your primary key, and then use ole-db command to update the table with

update destination set documenttype=? where [lineno]=?

umas Ten Centuries Points: 1219 More actions · Answer 6

Thank you very much for helping me with this. I was able to solve the issue using an update statement.