Script Task Hang - Had to Re-Compile

Greetings,

This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.

There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?

Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.

Thanks

Read the first post at the top of this forum.|||

Thanks Phil,

Just wanted to give some details about what I understand was our situation here.

We have had SQL Server 2005 SP2 for several months. This morning there was a big push which included Windows SP2, .NET 2.0, and .NET 3.0. So it doesn't surprise me that I'd have to recompile, but according to the article referenced at the top of this forum, it seems that SQL SP2 should have taken care of this. Possibly it was our unique sequence of updates here.

|||I'm surprised .Net 2.0 wasn't already installed. It should've been, I believe. You might have to reinstall SQL Server SP2 to reap its benefits.

I'm not entirely sure on the whole deal. That sounds like a pretty major update though.|||

Yes I was thinking the same thing, that the DBA must have been mistaken when he told me that. I thought .NET 2 was installed with SQL 2005, a prerequisite. Will probably just wait to see if it happens again, and if so then reinstall as you suggested.

Thanks for for your time on this

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.