ActiveX Transformation in SSIS

Question

ActiveX Transformation in SSIS

S. Kusen

SSChampion

Points: 10936
More actions
March 8, 2007 at 1:54 pm

#369723

All,
I am attempting to convert an ActiveX transformation in DTS to an SSIS data flow task. The background of the task is to import data from a flat file (1.40GB), check the condition of column 65 of the flat file, and then perform an active X (VB Script) transformation on the data.
I have been able to modify the first section of the code to gain access to the package variable, but some of the other functions are not supported in the Script Component task of the Data Flow Task.
Two main questions that I have are:
1. Does anyone know the equivalent to the IsNull() function?
2. Is there an equivalent to the DTSSource() function?
The code below is from the ActiveX transformation:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSGlobalVariables("ColumnCounter") = DTSGlobalVariables("ColumnCounter") + 1
If DTSSource("Col065") = "030" Then
DTSGlobalVariables("ColumnCounter") = 4
Main = DTSTransformStat_SkipInsert
else
If DTSGlobalVariables("ColumnCounter") <= 35 Then
tax_type_cnt = CInt(DTSGlobalVariables("ColumnCounter"))
tax_auth_cnt = tax_type_cnt + 30
tax_amt_cnt = tax_auth_cnt + 31
If IsNull(DTSSource("Col" + Right("000" + CStr(tax_amt_cnt), 3))) Then
Main = DTSTransformStat_SkipFetch Or DTSTransformStat_SkipInsert
Else
If Trim( DTSSource("Col" + Right("000" + CStr(tax_amt_cnt), 3))) <> "." Then
DTSDestination("geocode") = Left(DTSSource("Col001"), 9)
DTSDestination("spa") = Left(DTSSource("Col002"), 4) + Mid(DTSSource("Col002"), 5, 2) + "00" + Mid(DTSSource("Col002"), 7, 3) + "0"
DTSDestination("month_billed") = DTSSource("Col003")
DTSDestination("credit_code") = DTSSource("Col004")
DTSDestination("tax_type_cd") = DTSSource("Col" + Right("000" + CStr(tax_type_cnt), 3))
DTSDestination("tax_auth_cd") = DTSSource("Col" + Right("000" + CStr(tax_auth_cnt), 3))
DTSDestination("tax_amount") = FormatNumber((DTSSource("Col" + Right("000" + CStr(tax_amt_cnt), 3))), 4)
If CStr(DTSSource("Col004")) = "Y" Then
DTSDestination("tax_amount") =CCur(DTSDestination("tax_amount")) * CCur(-1.0000)
End If
Main = DTSTransformStat_SkipFetch
Else
Main = DTSTransformStat_SkipFetch Or DTSTransformStat_SkipInsert
End If
End If
Else
DTSGlobalVariables("ColumnCounter") = 4
Main = DTSTransformStat_SkipInsert
End If
end if
End Function
I'm currently stuck on the following line:
... If IsNull(DTSSource("Col" + Right("000" + CStr(tax_amt_cnt), 3))) Then ...
The DTSSource function is looking at a dynamically named column. I'd rather not change that statement to a series of Select Case statements.
Any ideas would be greatly appreciated. Thanks in advance!!
Steve

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

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

You're going to have to dig into the documentation some more to figure out how to write a script transformation. It's not that difficult, but the object model is completely different from DTS. There are also a lot of websites that have sample code.

The DTSSource function is replaced by automatically-generated BufferWrapper class objects named after the input. Each of these classes has accessor properties for each column, such as <column>_IsNull. The fact that you haven't noticed these objects makes me wonder if you're trying to use a Script Task on the Control page rather than a Script Component on the Data Flow page.

Go to SQL Server 2005 Books Online

SQL Server Programming Reference

Integration Services Programming

Extending Packages with Scripting

Extending the Data Flow with the Script Component

S. Kusen SSChampion Points: 10936 More actions · Answer 2

I saw them but wasn't sure how to utilize them. I'll take a look at the BOL and then try to work something out.

Thanks for the info.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 3

Are you sure that this can't be done using expressions and the conditional split component?

If you really have to use script then, you are going to need a transformation script component.

Drag a pipeline on to it from your flat file connection (or whatever transform is the last) and specify the input columns that are required for logic.
In the inputs and outputs expand the output and add the modified columns which you intend to output.
Go to script > design script. You will see this code inserted for you.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

End Sub

Within the Sub you need to add your logic - something like:

Row.OUTPUT_COLUMN_NAME = WhateverFunctionOrLogicYouNeed(Row.INPUT_COLUMN_NAME)

You will see an extra column in the pipeline once you have set this up.

Hope this helps

Kindest Regards,

Frank Bazan

S. Kusen SSChampion Points: 10936 More actions · Answer 4

Thanks for the reply, Frank. If I understand this correctly, this will be exactly what I need to to.

The conditional split component solved the first check where I check the value of column 65. The rest is where adding the column to the output.

So, if I add a column called "compiled_output" to the output and I want the value of that column to be a combination of the values in columns 10 and 11, my code would look similar to this:

row.compiled_output = row.column10 & row.column11

Please let me know if my interpretation of your post is correct.

Thanks,

Steve

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

Sounds about right, but if that is what you're doing your easiest solution would be a derived column component.

Simply drag the pipeline from the conditional split into a derived column component. When you edit the component, add a column called compiled_output and use an expression

[compiled_output] == [column10] + [column11]

Hope this helps

Kindest Regards,

Frank Bazan