Script Question: Getting data from SQL Server

I am trying to fill a DataTable with values from SQL server in SSIS. I just can't get the hang of the connections inside SSIS. I tried taking the example in BOL for "Script component [Integration Services], source components" and adapting to my needs but sqlReader doesn't seem to have the ability to use fill to populate a DataTable - so I tried changing to sqlAdapter and can't get it to work. (I get error about connection not being closed or object reference not set to instance of object) Thanks in advance for all the help...

Imports System

Imports System.Data

Imports System.Math

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports System.Xml

Public Class ScriptMain

Inherits UserComponent

Dim connMgr As IDTSConnectionManager90

Dim sqlConn As SqlConnection

Dim sqlQuery As SqlCommand

Dim sqlDataHolder As SqlDataAdapter

Dim dtLookup As DataTable = New DataTable()

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.ADOLOOKUP

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

End Sub

Public Overrides Sub PreExecute()

Dim LookupSql As String

LookupSql = "SELECT "

LookupSql += "* "

LookupSql += "FROM "

LookupSql += "Table "

LookupSql += "WHERE "

LookupSql += "TypeID = " & Me.Variables.TypeID

sqlConn.Open()

sqlQuery.CommandText = LookupSql

sqlQuery.Connection = sqlConn

sqlDataHolder.SelectCommand = sqlQuery

sqlDataHolder.Fill(dtLookup)

sqlConn.Close()

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

' Lookup stuff here

End Sub

End Class

Connections are not the problem here. Creating objects is.

You've created variables of type SqlCommand and SqlDataAdapter, but are not instantiating them.

Try something like the following and you'll get a lot closer to what you're looking for.

If Not sqlConn.State = ConnectionState.Open Then

sqlConn.Open()

End If

sqlQuery = New SqlCommand()

sqlQuery.CommandText = LookupSql

sqlQuery.Connection = sqlConn

sqlDataHolder = New SqlDataAdapter()

sqlDataHolder.SelectCommand = sqlQuery

sqlDataHolder.Fill(dtLookup)

sqlConn.Close()

By the way, is the reason for this component to overcome the fact that you can't parameterize a fully cached lookup (without a lot of schenanigans involving stage table, or views, or TVFS)?

|||

The reason is I have a large dataset (around 1 million rows) and a relatively small lookup table (depending on the variable between 3 and 1500 rows). The problem is the lookup is range based on date time (time >= lookup.starttime and time < lookup.endtime). Basically a record comes in and I have to assign it to a "timeslice" based on the lookup (the size of the timeslice is stored in the variable you see in the lookup).

If I use the standard SSIS lookup and change the sql within, its hideously slow as its making 1 million sql calls - so I am attempting to pull the whole lookup table into memory and instead lookup on that.

Thanks for the info on instantiating... that did the trick.