September 22, 2008 at 12:35 pm
if (DTSSource("crdt_hd_indcr") = "Y") then
DTSDestination("iStatusId") = 101421'Hold
else
DTSDestination("iStatusId")= 328'Active
end if
i've the above ActiveX script transformation script in the Data driven query task of a DTS package.
I'm upgrading the DTS package having the above script to SSIS package.
How can i achieve the above functionality in the SSIS i.e which task should i use in SSIS.
September 22, 2008 at 1:00 pm
You have to use script component transformation for this. Connect o/p source to script component,
then inside script component,
IF trim(Row.crdt_hd_indcr) = "Y") then
Row. iStatusId = value
else
Row. iStatusId = value
like this...
VG
September 22, 2008 at 1:26 pm
Hi VG, thanks for ur reply.
Should i add script component after the OLE db source and connect Script component to it and then connect OLE db destination to the script component
or
connect OLE db destination to OLE db source and then connect script component to that OLE db Destination.
i've the script you have given me to the script component.
It is giving the following error:
'crdt_hd_indcr' is not a member of 'scriptComponect_......Input0Buffer'.
should i declare that before that script you have given me.
If so please give me how to declare it.
please dont mind i'm new to this.
Thanks in advance.
September 22, 2008 at 1:37 pm
First option.
Source - script - destination.
'crdt_hd_indcr' is this a variable or column in a source ? IF it is a source column, the moment you type Row, all the column should pop up (intellisense).
VG
September 22, 2008 at 2:01 pm
Hi VG,
I removed the underscores then that error is gone. I think it doesn't accept underscores. But the error is still with the IStatusId which is a destination column.
I added in the source-script component-destination order as you said.
But how is the script component going to know about the destination columns if we give in that order
Plz help me out.
September 22, 2008 at 2:11 pm
What is corresponding IsStatusID source column ? .....Map this column ( output from script component) to destination column in destination . Script component is transformation between source and destination if you want apply any other logic here...
VG
September 22, 2008 at 2:30 pm
Hi VG,
thankq very much...that worked out for me.
I've one more doubt can you give me the code for the following ActiveX script task in data driven query task of DTS package
DTSGlobalVariables("WINCompanyCode").Value = DTSSource("co_cd")
DTSGlobalVariables("BeaconCompanyId").Value = DTSSource("onyx_co_cd")
where WINCompanyCode and BeaconCompanyId are global variables and co_cd and onyx_co_cd are source columns in the data driven query task
Please help me out.
September 22, 2008 at 2:50 pm
Hi VG,
How can the below script be written in SSIS script component
if (DTSSource("onyx_co_cd") = 0 OR ISNULL(DTSSource("onyx_co_cd"))) then
DTSDestination("vchURL") = "I"
else
DTSDestination("vchURL") = "U"
end if
please help me out
September 22, 2008 at 3:03 pm
FOR GLobal variables:
in script component, "readonly variables" type your 2 variable names...gvVar1, gvVar2
Decalre local variable inside script component,
Dim sCol1 as string = Me.variables.gvVar1
Row.col1 = sCol1 (Col1 is your source column)
2nd solution:
If trim (Row.col1) = "0" or Row.Col1_IsNull = true
then
do this...
else
do this...
VG
September 22, 2008 at 3:25 pm
Hi VG,
thanq very much i hve one more issue
There is a lookup query in the data driven query task of DTS package i'm migrating . That lookup tab in the data driven query task has the following lookups ReferenceExtra and ReferenceChar.
And those lookups are used in the ActiveX script as following
DTSDestination("vchUser7")= DTSLookups("ReferenceExtra").Execute(67,DTSSource("mr_type"))'MR Type
DTSDestination("vchUser8")= DTSLookups("ReferenceChar").Execute(68,DTSSource("dunbrdst"))'Corp Acct
How can i write equivalent script in the Script component of SSIS.
Please help me out.
September 22, 2008 at 3:51 pm
Sorry, I don't know much about this...Ask a seperate question in this forum....
VG
September 23, 2008 at 8:40 am
Hi VG,
U gave the script for global variables as
FOR GLobal variables:
in script component, "readonly variables" type your 2 variable names...gvVar1, gvVar2
Decalre local variable inside script component,
Dim sCol1 as string = Me.variables.gvVar1
Row.col1 = sCol1 (Col1 is your source column)
I think the above script is for inserting a values into the column from global variable but i want to assign value to a global variable from source column.
Can u give the exact script including the declaration for the script below that can be used in the script component of ssis
msgbox ("billing")
DTSGlobalVariables("WINCompanyCode").Value = DTSSource("co_cd")
DTSGlobalVariables("BeaconCompanyId").Value = DTSSource("onyx_co_cd")
please help me out
October 1, 2008 at 7:21 am
Hi VG,
I've the following script in the activeX transformation mapping of the data driven query task of one of the DTS packages i'm upgrading to SSIS.
arr = Split(DTSSource("vchCOntactKey"),"~",3)
DTSGlobalVariables("WINCoCd").Value = arr(0)
DTSDestination("CNTCT_SEQ_NUM") =arr(1) & arr(2)
Can any one give the equivalent script for the above that I can use in the script component of the SSIS.
Thanks in advance.
October 1, 2008 at 7:44 am
You really should be using a derived column component for these two issues. Don't use the script component unless you have to. Splitting and a conditional statement can both be done easily in a derived column expression.
October 1, 2008 at 7:58 am
Hi thanx for your reply. What function should i use in the derived column...Is substring function same as the split function?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply