January 14, 2008 at 3:40 am
HI,
I have a date field like 2008-01-09 15:12:57' and for every row, I must execute a sql for get a value from a function in my DB.
I must increaze the fileld belove row.IDgiornoMisurazione for output in any row.
--------------------------
Script above always have a cast type error....
--------------------------
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 sqlConn As SqlConnection
Dim strSQL As String
Dim sqlCmd As SqlCommand = New SqlCommand(strSQL)
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
sqlConn = CType(Connections.ObjConnection.AcquireConnection(Transaction), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
With sqlCmd
.Connection = sqlConn
.CommandTimeout = 30
.CommandType = CommandType.Text
End With
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
strSQL = "SELECT dbo.getIDgiornoMisurazione('" & Row.DateTime & "')"
Row.IDgiornoMisurazione = CInt((sqlCmd.ExecuteScalar()))
End Sub
Public Overrides Sub ReleaseConnections()
If sqlConn IsNot Nothing Then
Connections.ObjConnection.ReleaseConnection(sqlConn)
End If
End Sub
End Class
-----------------
My Err
----------------
Error at Data Flow Task [Script Component [1457]]: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error at Data Flow Task [DTS.Pipeline]: component "Script Component" (1457) failed validation and returned error code 0x80004002.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Alren Italy
January 30, 2008 at 1:34 pm
I find It amazing that all of the xamples I find end up with the same error and nobody anewhere has a solution. This is either something pretty trivial or no body really cares
January 30, 2008 at 3:13 pm
Alen cappelletti (1/14/2008)
HI,I have a date field like 2008-01-09 15:12:57' and for every row, I must execute a sql for get a value from a function in my DB.
What is the value you are trying to get? What is your goal? Describe in detail. Seems like you might only need a simple (or little bit complex) T-SQL command instead of long scripting language.
------------
🙂
March 19, 2008 at 4:28 pm
You must use an ADO.NET connection manager rather than an OLE DB or SQL Native connection manager. I just figured this out today, having run into the exact same error message:
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
December 22, 2009 at 1:03 pm
Thank you for providing an actual answer.
December 22, 2009 at 1:39 pm
rdowty (3/19/2008)
You must use an ADO.NET connection manager rather than an OLE DB or SQL Native connection manager. I just figured this out today, having run into the exact same error message:http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
Thanks, this is good to know.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply