December 28, 2008 at 9:00 pm
Hi,
How I can execute a store procedure using expression in SSIS 2005?
Thanks
Munna
December 28, 2008 at 9:56 pm
Create an OLEDB connection to your database.
Add an Execute SQL task - specify the above connection.
Click on Expressions in the task editor and then expand the Expressions item to get the Property Expressions Editor. Click on the Property drop-down and find the SqlStatementSource property - enter the required expression against it (eg "exec dbo.usp1").
That should be enough.
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 28, 2008 at 9:58 pm
Do you want this?
--===========================
declare @a varchar(100)
set @a='myproc' -- This SP doesnt accept any parameter, myproc is an SP
exec @a
--========================
--===========================
declare @a varchar(100)
declare @param1 varchar(100)
set @a='myproc' -- This SP accepts a parameter
set @param1='1' -- setting the parameter value
exec @a @param1 -- executing the SP with parameter
--========================
December 29, 2008 at 9:28 am
Thanks. This was really helpful. Actually I'm migrating my DTS package to SSIS 2005. The DTS performs the following tasks:
It Truncates table using Execute SQL task, then in ActiveX task it runs the below ActiveX code, and then it executes a stored procedure using Execute SQL task. When I create a ActiveX task in SSIS 2005 and other Execute Tasks to truncate table and run stored procedure, I get an error "Function not found....". Now I'm not sure which function is not found.
I would appreciate all your help in guiding me how to convert this DTS to SSIS.
Thanks
Munna
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Change the value of the file name on the connection
Set oPackage = DTSGlobalVariables.parent
Dim sSQLString
Dim sDate
Dim dDate
Dim strResult
dDate = GetRevalDate
sDate = DateToName(dDate)
'SQL String
sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _
"@RevalDate = '" & sDate & "'"
DTSGlobalVariables.Parent.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask.SQLStatement = sSQLString
Main = DTSTaskExecResult_Success
End Function
Function GetRevalDate()
Dim dDate
dDate = date
If Weekday(dDate) = 1 Then
GetRevalDate = dDate + 1
Else If Weekday(dDate) = 7 Then
GetRevalDate = dDate + 2
Else
GetRevalDate = dDate
End If
End If
End Function
Function DateToName(dDate)
'Create a name based on a date
Dim sYear
Dim sMonth
Dim sDay
sYear = Year(dDate)
If Month(dDate) < 10 Then
sMonth = "0" & Month(dDate)
Else
sMonth = Month(dDate)
End If
If Day(dDate) < 10 Then
sDay = "0" & Day(dDate)
Else
sDay = Day(dDate)
End If
DateToName = sYear & sMonth & sDay
End Function
December 30, 2008 at 11:52 am
My solution is as follows:
1. Create a user variable sSQLString
2. Create a Script Task (not ActiveX Script Task because they are deprecated in SQL 2008) as follows:
Name = Set SQL Command
PrecompileScriptIntoBinaryCode = False
ReadWriteVariables = sSQLString
Design Script is based on your ActiveX script:
----------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim sSQLString As String
Dim sDate As String
Dim dDate As Date
Dim strResult As String
dDate = GetRevalDate
sDate = DateToName(dDate)
'SQL String
sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _
"@RevalDate = '" & sDate & "'"
Dts.Variables("sSQLString").Value = sSQLString
Dts.TaskResult = Dts.Results.Success
End Sub
Function GetRevalDate() As Date
Dim dDate As Date
dDate = Now()
If Weekday(dDate) = 1 Then
GetRevalDate = DateAdd(DateInterval.Day, 1, dDate)
ElseIf Weekday(dDate) = 7 Then
GetRevalDate = DateAdd(DateInterval.Day, 2, dDate)
Else
GetRevalDate = dDate
End If
End Function
Function DateToName(ByVal dDate As Date) As String
'Create a name based on a date
Dim sYear As String
Dim sMonth As String
Dim sDay As String
sYear = CStr(Year(dDate))
If Month(dDate) < 10 Then
sMonth = "0" & Month(dDate)
Else
sMonth = CStr(Month(dDate))
End If
If Day(dDate) < 10 Then
sDay = "0" & Day(dDate)
Else
sDay = CStr(Day(dDate))
End If
DateToName = sYear & sMonth & sDay
End Function
End Class
----------------------------------------------------------
3. Create an Execute SQL Task as follows:
Name = Execute SQL Command
ConnectionType = ADO.NET
Connection = {your ADO.NET connection name}
SQLSourceType = Direct input
SQLStatement = {empty}
IsQueryStoredProcedure = False
Expressions:
Property = SqlStatementSource
Expression = @[User::sSQLString]
4. Connect the workflow from Set SQL Command to Execute SQL Command and test.
January 2, 2009 at 8:42 am
Thanks so much for such a great detailed answer.
When I creat a variable "sSQLString", should I leave the DataType as default and Value as well?
Thanks
Munna
January 3, 2009 at 6:18 pm
Create the variable "sSQLString" as type String. The value is not relevant (I deleted the default -1) because you will change it in the Script Task.
May 22, 2009 at 3:17 am
Hi
Can you pls help me replace the class. I have migrated the dts to SSIS
The Active x script was executing the another task in the same package. How can I do this in SSIS.
below is the script;
sSQL = DTSGlobalVariables.Parent.Tasks("Task_TransProduction").CustomTask.SourceSQLStatement
May 25, 2009 at 1:01 am
Hi,
There are some DTS class ommited from SSIS. you can take ref from below:
Incase you are a SQL Administrator it will be better to choose one of your .NET developer to convert the code snippest in .NET
What doesn't work in SSIS ActiveX Script Task.
DTS Object Model Calls
Examples:
Set pkg = DTSGlobalVariables.Parent '// Will not work in SSIS
Set conn = opkg.Connections("MyConnection") '// Will not work in SSIS
Set stp = opkg.Steps("DTSExecuteSQL_1") '// Will not work in SSIS
DTS Variable Assignment to Int64 or Single DataType variable. You will have to do proper casting or change variable datatype to fix this error. You may receive the following error(s) in some cases when you try to execute ActiveX Task in SSIS
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001868CC.
-- or --
[ActiveX Script Task] Variable uses an Automation type not supported in VBScript
July 6, 2017 at 1:58 pm
Sr SQL developer - Tuesday, December 30, 2008 11:52 AMMy solution is as follows:1. Create a user variable sSQLString2. Create a Script Task (not ActiveX Script Task because they are deprecated in SQL 2008) as follows:Name = Set SQL CommandPrecompileScriptIntoBinaryCode = FalseReadWriteVariables = sSQLStringDesign Script is based on your ActiveX script:----------------------------------------------------------' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain' The execution engine calls this method when the task executes.' To access the object model, use the Dts object. Connections, variables, events,' and logging features are available as static members of the Dts class.' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.' ' To open Code and Text Editor Help, press F1.' To open Object Browser, press Ctrl+Alt+J.Public Sub Main() Dim sSQLString As String Dim sDate As String Dim dDate As Date Dim strResult As String dDate = GetRevalDate sDate = DateToName(dDate) 'SQL String sSQLString = "exec st_extract_populate_vega_swaption_work_table " & Chr(13) & _ "@RevalDate = '" & sDate & "'" Dts.Variables("sSQLString").Value = sSQLString Dts.TaskResult = Dts.Results.SuccessEnd Sub Function GetRevalDate() As Date Dim dDate As Date dDate = Now() If Weekday(dDate) = 1 Then GetRevalDate = DateAdd(DateInterval.Day, 1, dDate) ElseIf Weekday(dDate) = 7 Then GetRevalDate = DateAdd(DateInterval.Day, 2, dDate) Else GetRevalDate = dDate End If End Function Function DateToName(ByVal dDate As Date) As String 'Create a name based on a date Dim sYear As String Dim sMonth As String Dim sDay As String sYear = CStr(Year(dDate)) If Month(dDate) < 10 Then sMonth = "0" & Month(dDate) Else sMonth = CStr(Month(dDate)) End If If Day(dDate) < 10 Then sDay = "0" & Day(dDate) Else sDay = CStr(Day(dDate)) End If DateToName = sYear & sMonth & sDay End FunctionEnd Class----------------------------------------------------------3. Create an Execute SQL Task as follows:Name = Execute SQL CommandConnectionType = ADO.NETConnection = {your ADO.NET connection name}SQLSourceType = Direct inputSQLStatement = {empty}IsQueryStoredProcedure = FalseExpressions:Property = SqlStatementSourceExpression = @[User::sSQLString]4. Connect the workflow from Set SQL Command to Execute SQL Command and test.
July 7, 2017 at 5:15 am
Holy thread revival, Batman!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply