Solved

SSIS Script Component as a lookup

Posted on 2009-04-07
11
2,160 Views
Last Modified: 2013-11-10
I am testing out different way of finding information in our database through SSIS.
I put together a script transform with an ADO.NET connection and just asked it to lookup based on a sql Script similar to what I would have done in a VB application, then used teh Fuzzy Lookup to check agains the persons email and Finally used the Lookup Transform to directly compare the same scenario I used the script transform i.e. the persons employee id + company id.  The Script transform matched roughly 50% and it wasn't consistent like scipping every other row, but the lookup found them all... I want to know what I did wrogn with my script so i can have more options in the future.
Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Data.SqlClient
 

Public Class ScriptMain

    Inherits UserComponent

    Dim connMgr As IDTSConnectionManager90

    Dim sqlConn As SqlConnection

    Dim sqlCmd As SqlCommand
 
 

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

        connMgr = Me.Connections.SQLConnection

        sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

    End Sub
 

    Public Overrides Sub PreExecute()

        sqlCmd = New SqlCommand("SELECT frid FROM frdonmst WHERE (empid = @empid) and (cmpid = @cmpid)", sqlConn)

        With sqlCmd.Parameters

            .Add("@empid", SqlDbType.VarChar, 14)

            .Add("@cmpid", SqlDbType.BigInt, 10)

        End With

        sqlCmd.CommandTimeout = 1234567890
 

    End Sub
 
 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Dim reader As SqlDataReader

        Dim cmpid As Decimal = Row.EnterpriseCompanyID

        Dim empid As String = Row.EmployeeID

        sqlCmd.Parameters("@empid").Value = Row.AccountID

        sqlCmd.Parameters("@cmpid").Value = cmpid

        reader = sqlCmd.ExecuteReader()

        If reader.Read() Then

            'If (reader("Frid").ToString() Is System.DBNull.Value) Then

            '    Row.frid2_IsNull = True

            'Else

            If Row.AccountID <> "" Then

                Row.fridScript = CDec(reader("Frid").ToString())

            End If

            'End If

        End If
 

        reader.Close()

        Row.NextRow()

 End Sub
 

    Public Overrides Sub ReleaseConnections()

        connMgr.ReleaseConnection(sqlConn)

    End Sub
 
 
 

--------------------

And the lookup transform used this 

select * from

	(select * from [dbo].[frdonmst]) as refTable

where RTRIM([refTable].[cmpid]) = ? and RTRIM([refTable].[empid]) = ?
 

Parameter0 = [Enterprise Company ID]

Parameter1 = [Account ID]
 

Again the same query just two different methods of goign about it and two very different results.

Open in new window

FilePrepFridMatchingTest.xls
0
Comment
Question by:ramos_rita
  • 6
  • 5
11 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24088723
Why you want to replace the lookup (optimized component) by script component? DO you think you can get better performance using script component!?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:ramos_rita
ID: 24091765
I don't think i can get better performance.
There is a unique situation that exists with our files in that there are a number of differnt unique identifiers that could exist in our system.  We process files from multiple sources.
The script uses the unique business logic that exists with each scenario to make the comparisons.
i.e. a company could have potentially 3 company ids... this is a fact we can not do away with because each id has different rules associated with them.  
So the script basically does what the lookup transform does but instead of having to put 20 lookups and stringing them together with merges and what not to keep the flow, I can just write a script that accomplishes this task.  But I don't want to do it if I can't get the same accuracy.
Thanks,
Michael
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24095874
ok... very good! I see you understand this.
If I can help you let me know!!
Good luck!
Pedro
0
 

Author Comment

by:ramos_rita
ID: 24096292
Can you tell me if I am doing something wrong in the script above?
It only returns a value some of the time.  I take the same script put it in SQL and execute it and it runs perfectly.
Michael
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24097739
but which is the error? only the output that returns one record?
Explain me better!
Thanks!
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:ramos_rita
ID: 24106764
If the lookup component uses the same sql script and parameters as the script component and the lookup component finds all of the records and the scritp component doesn't (roughly less than half).

Is the way I am calling the reader correct?  Is there something that I am doing in the script component that is messing with my results?

Michael
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24155962
In perfomance I dont believe you can beat Lookup... but if you need this, ok...:-)

Now... could you send me the package, SQL scripts and some data to help you better?!
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:ramos_rita
ID: 24158174
How do I send you the package when the extension is not approved
I am changing it to .txt just change the extension back to .dtsx
FridMatching.txt
0
 

Accepted Solution

by:
ramos_rita earned 0 total points
ID: 24158510
wow...
so in the process of trying to get you some sample data I looked and though to myself... why do I need to have this Row.NextRow() in there... doesn't it send in one row at a time.
So I took out the Row.NextRow() and it seems to be working just fine.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24158944
also attach the script database (CREATE TABLES:...)
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24158963
ok! good!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now