My guess as to your error posted is your connection manager is setup to use the 'OleDB' provider instead of .Net provider

when you do the directcast to a SQLConnection...

if anyone knows of contract doing (c#, .net, asp.net,biztalk, etc.) in seattle area please let me know.

Sorry for shameless self-promotion 😉

Below is another way to skin the cat for ssis,sftp,...

I'm not sure what you are trying to do, but you can execute a stored procedure

in the "Data Flow" tab by using a "OLE DB Source" control and then in...

I think the problem is that you are using a "script component" from the "Data Flow" tab and I'm referring to a "script task" from the "Control Flow" tab.

"g_DBAgilSourceOleDBWin" should be the name of your connection manager.

Someone else had a question and posted their code. I responded back using whatever name they were using for their...

It's hard to know without seeing the error that's being thrown.

My guess is that your DTS connection ("SQLDEVGA01.EBIS1") is not configured using '.Net Providers\SqlClient Data Provider'.

Try this, I think it should work.

Dim mConn As SqlClient.SqlConnection = DirectCast(Dts.Connections("g_DBAgilSourceOleDBWin").AcquireConnection(Nothing), SqlClient.SqlConnection)

Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()

cmd.Connection = mConn

cmd.CommandText = "MyDBName.dbo.myStoredProc" 'note: added MyDBName.dbo. to commandtext and...

The following will do it. It would be better to use a SSIS configured Data Source instead of storing the connection string in a package variable but I have...