February 14, 2008 at 12:17 pm
I would like to call stored procedure inside Script task.
Any example out there?
Thanks
March 7, 2008 at 2:31 pm
I know no one replied on this forum, but did you find anything? I'm trying to do the same thing but have had no success.
March 7, 2008 at 4:39 pm
Do you only want to run the sp? If so just execute the sp in a tsql task.
Drag an execute tsql task to the pane. Open it and change the sql source type to direct input. The just execute your procedure.
e.g. exec myprocedurename
If you procedure has a result set, you can specify that in the task too.
March 8, 2008 at 3:28 am
No, that's not all I want to do. I run several that way. What I want to do is take a date output parameter, update two variables, and alter four data reader sqlcommand properties so that the where clause is programatically changed to reflect the new dates. It's the requirement to do the later that's causing the need to use a script, unless you have a way around that.
March 8, 2008 at 8:42 am
Have you considered using an Execute SQL Task within a For Loop container? You can create a variable that contains you sqlcommand and change that each time through the loop. If you r data source is changing also you can even use a variable for that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 1, 2008 at 3:08 am
We can use Script Component to execute Oracle stored procedures with ref cursor as parameter(s)
Add the output columns to the component.
For eg: I had a oracle package Get_Employees.GetEmployees which takes an put put parameter of type ref cursor
I added 2 such columns EmpID and EmpName and then override CreateNewOutputRows() as
Public Overrides Sub CreateNewOutputRows()
Try
Dim dr As OracleDataReader = oracleCmd.ExecuteReader()
While dr.Read
Output0Buffer.AddRow()
Output0Buffer.EmpID = CDec(dr(0).ToString)
Output0Buffer.EmpName = dr(1).ToString
End While
Catch ex As Exception
Me.ComponentMetaData.FireError(-1, "InitExtract", ex.Message, String.Empty, 0, True)
End Try
Output0Buffer.SetEndOfRowset()
End Sub
Prior to that we need to set oracle command object in PreExecute subroutine as:
Dim plsql As String = "BEGIN Get_Employees.GetEmployees(:curEmployeesbyID);END;"
oracleCmd = New OracleCommand(plsql, oracleConn)
With oracleCmd
.CommandType = CommandType.Text
.Parameters.Add(":curEmployeesbyID", OracleType.Cursor).Direction = ParameterDirection.Output
End With
August 21, 2008 at 2:48 pm
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 not figured out how to do that yet.
Dim cn As OleDbConnection = New OleDbConnection()
Dim cmd As OleDbCommand = New OleDbCommand()
cn.ConnectionString = Dts.Variables("User::DBConnection").Value
cn.Open()
cmd.Connection = cn
cmd.CommandText = "MyDB.dbo.MySp"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("parameter1", DbType.String).Value = "what ever"
cmd.Parameters.AddWithValue("createdon", DbType.DateTime).Value = DateTime.Now
cmd.Parameters.AddWithValue("success_OUT", DbType.Boolean).Direction= ParameterDirection.Output
cmd.ExecuteNonQuery()
August 21, 2008 at 5:43 pm
Yeah it's better to create a connection manager and then use that in the script task.
Something like this
'Get the connection manager we have created in the package
Dim mConn As SqlConnection = DirectCast(Dts.Connections("MyDBConnMgr").AcquireConnection(Nothing), SqlConnection)
'Now use mConn as normal
'Release the connection
Dts.Connections("MyDBConnMgr").ReleaseConnection(Nothing)
August 25, 2008 at 7:08 pm
I am trying to do something similar. I want to call a stored procedure (with a parameter) and have the stored procedure return 2 output parameters.
Hope someone can help. My DB is SQL 2005
August 26, 2008 at 1:12 am
You just need to call the stored procedure as normal and then inspect the command object for the appropriate output parameters. You can then get their values.
February 11, 2009 at 12:59 pm
Hi everyone,
I am trying to do something similiar like SSC-Enthusiastic. To call the stored procedure inside script task.
I tried to use your example to use the connection manager but when I put in SqlConnection it asks for the type so I chose sqlClient
but my script has error on run time
"Error: The script threw an exception: 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."
below are my code. Can you please help
Public Sub Main()
Dim mConn As SqlConnection = DirectCast(Dts.Connections("g_DBAgilSourceOleDBWin").AcquireConnection(Nothing), SqlClient.SqlConnection)
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
cmd.Connection = mConn
cmd.CommandText = "myStoredProc Parm1, Parm2"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("Parm1", DbType.String).Value = "SLS"
cmd.Parameters.AddWithValue("Parm2", DbType.String).Value = "Cat"
cmd.ExecuteNonQuery()
'Now use mConn as normal
'mConn.
'Release the connection
Dts.Connections("g_DBAgilSourceOleDBWin").ReleaseConnection(Nothing)
Dts.TaskResult = Dts.Results.Success
February 11, 2009 at 2:16 pm
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 removed Parm1, Parm2 from statement.
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("Parm1", DbType.String).Value = "SLS"
cmd.Parameters.AddWithValue("Parm2", DbType.String).Value = "Cat"
cmd.ExecuteNonQuery()
'Release the connection
mConn.Close()
mConn.Dispose()
Dts.Connections("g_DBAgilSourceOleDBWin").ReleaseConnection(Nothing)
Note: make sure your connection named "g_DBAgilSourceOleDBWin" provider is '.Net Providers\SqlClient Data Provider'
if your connection is set up for OLE DB provider replace first two lines of code with the following
Dim mConn As OleDb.OleDbConnection = DirectCast(Dts.Connections("MyDBConnMgr").AcquireConnection(Nothing), OleDb.OleDbConnection)
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
February 20, 2009 at 4:11 pm
Hi Brady, I tried this code and still showing error, this the line that cause the problem -most important-
Dim mConn As SqlClient.SqlConnection = DirectCast (Dts.Connections("SQLDEVGA01.EBIS1").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
I tried with Ctype and didn't work, this is the code that I'm using to make connection, do you see anything wrong ? :
Dim mConn As SqlClient.SqlConnection = DirectCast(Dts.Connections("SQLDEVGA01.EBIS1").AcquireConnection(Nothing), SqlClient.SqlConnection)
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand()
cmd.Connection = mConn
cmd.CommandText = "MyDB.dbo.MySP"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("carrier", DbType.String).Value = "MyValue"
I appreciate your suggestions.
Thanks
February 20, 2009 at 4:41 pm
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'.
February 20, 2009 at 4:48 pm
Thank you so much for all your help! I got it to work finally. Wendy
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply