Call stored procedure inside SSIS Script task

Question

Call stored procedure inside SSIS Script task

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

narendrashar72 Grasshopper Points: 19 More actions · Answer 1

'I am stuggling with same issue. Could you send me complete code with all steps how to output of Ref Cursor from oracle procedure into Temp table for further process using Script component in SSIS 2005

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 2

Unfortunately this Does Not Work.

The Code I have is

Public Sub Main()

'Dts.Variables("MyCaseSensitiveVariableName").Value

Dts.Log(" ************** In Script Main Task ", 0, Nothing)

Dts.Log(" ********************************* The Total Number of Connections :- " & Dts.Connections.Count.ToString, 0, Nothing)

Dts.Log(" ********************************* The Connection (0) :- " & Dts.Connections.Item(0).ConnectionString, 0, Nothing)

Dts.Log(" ********************************* The Connection (1) :- " & Dts.Connections.Item(1).ConnectionString, 0, Nothing)

Dts.Log(" ********************************* The Connection (2) :- " & Dts.Connections.Item(2).ConnectionString, 0, Nothing)

Dim SiteMgrConn As SqlConnection = DirectCast(Dts.Connections.Item(2).AcquireConnection(Nothing), SqlConnection)

Dts.Log(" ********************************* The SiteManager Connection String :- " & SiteMgrConn.ConnectionString.ToString, 0, Nothing)

Dts.Connections("SULPHERDB2008.SiteManager").ReleaseConnection(Nothing)

Dts.TaskResult = ScriptResults.Success

End Sub

The Error I get is

OnError,,,,,,12/1/2010 5:16:01 PM,12/1/2010 5:16:01 PM,1,0x,System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> 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 ST_d6e56b8d2afd41ca93196df38256f429.vbproj.ScriptMain.Main()

--- End of inner exception stack trace ---

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

brady-786864 Old Hand Points: 392 More actions · Answer 3

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 it bombs becuase the provider is OleDB. if my assumption is correct, you can either cast to OleDb connection or change your connection manager configuration to SQLClient provider.

**OleDb**

Dim cn As OleDb.OleDbConnection = DirectCast(Dts.Connections("MyDBConMgrName").AcquireConnection(Nothing), OleDb.OleDbConnection)

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()

or

**SqlClient**

Dim cn as SQlClient.SqlConnection = DirectCast(Dts.Connections("MyDBConMgrName").AcquireConnection(Nothing),SqlClient.SqlConnection)

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

-just make sure whatever you use matches the configured connction manager

**rest of code same regardless of which provider you are using**

cmd.Connection = cn

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "MyDBName.MySchemaName.MyStoredProcName"

cmd.Parameters.AddWithValue("MyParamName",DbType.String).Value = "hello world"

cmd.ExecuteNonQuery()

Dts.Connections("").ReleaseConnection(Nothing)

hopefull i didn't waste your time answering something you weren't asking about 😉

niall.baird Hall of Fame Points: 3468 More actions · Answer 4

Hi Brady, thanks for that - will be able to test this out in a couple of days, but just looking at the code you've posted, I think that will fix my problems.

Mohan.deval Say Hey Kid Points: 713 More actions · Answer 5

Yes. Boss.. Thanx,.... It worked. I found that out mentioned by someone in the list that it works only for ado.net, ...... in a fine print & I was doing the wrong cast... Thanx again... cheers...

nishav2 SSCrazy Points: 2224 More actions · Answer 6

Hi,

I am transferring data from SQL table to Oracle table.

For this I am using a script task in SSIS.This is working fine. Now i need to encrypt a sensitive column before the data gets inserted into the oracle side.

So for this i created a function in oracle.

I need to call this function when i insert/update that particular column

How to achive this in same script task which does the insert.

Example Oracle update:

UPDATE DCDB.CUST_INFO

SET AGNUM =DCDB.CRYPTO_TOOL.encrypt(TRIM(PASS),'EncryptKey');

Can someone thow some light ?

Any help appreciated!!

Thanks

Nisha.V Krishnan

niall.baird Hall of Fame Points: 3468 More actions · Answer 7

CREATE TABLE encrypted_login_details(

ServerName varchar(15)

, username varchar(10)

, password varbinary(100)

)

go

--EncryptByPassPhrase:

INSERT INTO encrypted_login_details(ServerName,username,password) VALUES('MyServer','MyUserName',EncryptByPassPhrase('SSIS Is the future','ThisIsThePassword@1234'))

/*

-Syntax: ENCRYPTBYPASSPHRASE('PASSPHRASE','text')

In this statement, PASSPHRASE specifies the data string to be used to derive an encryption key,

and 'text' data type should be VarBinary.

*/

SELECT * FROM encrypted_login_details

--DECRYPTBYPASSPHRASE:

SELECT ServerName,username, Password FROM encrypted_login_details;

SELECT ServerName,username, DECRYPTBYPASSPHRASE ('SSIS Is the future',password) as Password FROM encrypted_login_details;

SELECT ServerName,username,convert(varchar(10), DECRYPTBYPASSPHRASE ('SSIS Is the future',password)) FROM encrypted_login_details

WHERE username = 'ADMIN';

SELECT ServerName, Username, CONVERT(VARCHAR(10), DECRYPTBYPASSPHRASE('SSIS Is the future',Password)) FROM dbo.Encrypted_Login_Details WHERE USERNAME = 'MyUserName'