Script Task Error?

I have the following code inside a Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections

Public Class ScriptMain

Public Sub Main()

Dim nm As String = "ship_metrics_rpt_*.csv"

Dim files As ObjectModel.ReadOnlyCollection(Of String) = My.Computer.FileSystem. _

GetFiles(CStr(Dts.Variables("MainPath").Value))

If files.Count > 0 Then

Dim dic As Generic.SortedDictionary(Of Date, String)

For a As Integer = 1 To files.Count

If files(a) Like nm Then

Dim nfo As System.IO.FileInfo = My.Computer.FileSystem.GetFileInfo(files(a))

dic.Add(nfo.CreationTime, files(a))

End If

Next

If dic.Count > 0 Then

Dim FinalFiles(dic.Count - 1) As String

Dim count As Integer

For Each kvp As Generic.KeyValuePair(Of Date, String) In dic

FinalFiles(count) = kvp.Value

Next

End If

End If

Dts.TaskResult = Dts.Results.Success

End Sub

The following exception gets thrown at "If files.Count > 0 Then"

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Why?

Thanks for your help

Has MainPath been passed in as a variable? It is scoped properly?|||

Yes it's been passed. You can insert "msgbox files.count" before "if files.count > 0" and get a count just fine. I don't understand it.

|||There are a number of problems here (easily fixed though)

1 . Fence post condition. The ReadOnlyCollection index starts at 0, not 1.
Change

For a As Integer = 1 To files.Count

To

For a As Integer = 0 To files.Count -1

2. Unitialized Variable:

Change

Dim dic As Generic.SortedDictionary(Of Date, String)

To

Dim dic As Generic.SortedDictionary(Of Date, String) = New Generic.SortedDictionary(Of Date, String)

|||Ah - I didn't catch the uninitialized part - that should fix it - thanks.

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.

Script Question inside a DataFlow

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

gsell wrote:

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

Well, you COULD do that but, trust me, it would be much much slower than what you are already doing. To loop over the data this would need to be an asynchronous (sometimes called a blocking) component and these are slow.

The correct way to do this is what you are already doing.

-Jamie

Script Component in loop tries to evaluate connection

I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.

The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.

I hope this makes sense...

Set the DelayValidation property to True for the connection (and data flow task if required).

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

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

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

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

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.