• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2353
  • Last Modified:

SSIS Script Component as a lookup

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
ramos_rita
Asked:
ramos_rita
  • 6
  • 5
1 Solution
 
PedroCGDCommented:
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
 
ramos_ritaAuthor Commented:
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
 
PedroCGDCommented:
ok... very good! I see you understand this.
If I can help you let me know!!
Good luck!
Pedro
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ramos_ritaAuthor Commented:
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
 
PedroCGDCommented:
but which is the error? only the output that returns one record?
Explain me better!
Thanks!
0
 
ramos_ritaAuthor Commented:
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
 
PedroCGDCommented:
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
 
ramos_ritaAuthor Commented:
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
 
ramos_ritaAuthor Commented:
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
 
PedroCGDCommented:
also attach the script database (CREATE TABLES:...)
0
 
PedroCGDCommented:
ok! good!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now