SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script Component Transformation - SSIS 2005


Script Component Transformation - SSIS 2005

Author
Message
umas
umas
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 227
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.
binko
binko
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 423
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
umas
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 227
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
Attachments
Transformation.doc (23 views, 40.00 KB)
binko
binko
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 423
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
umas
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 227
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
binko
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 423
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
umas
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 227
Thank you very much for helping me with this. I was able to solve the issue using an update statement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search