Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script Component Transformation - SSIS 2005 Expand / Collapse
Author
Message
Posted Friday, August 21, 2009 12:07 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, 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.
Post #775306
Posted Friday, August 21, 2009 2:49 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:34 PM
Points: 70, 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.
Post #775439
Posted Friday, August 21, 2009 3:11 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, 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





  Post Attachments 
Transformation.doc (13 views, 40.50 KB)
Post #775451
Posted Saturday, August 22, 2009 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:34 PM
Points: 70, 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?
Post #775569
Posted Saturday, August 22, 2009 6:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, 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?
Post #775613
Posted Tuesday, August 25, 2009 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 20, 2012 3:34 PM
Points: 70, 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]=?

Post #776631
Posted Tuesday, August 25, 2009 8:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 2:57 PM
Points: 81, Visits: 227
Thank you very much for helping me with this. I was able to solve the issue using an update statement.
Post #776715
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse