Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Expression Expand / Collapse
Author
Message
Posted Sunday, December 28, 2008 9:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 11:39 AM
Points: 10, Visits: 96
Hi,

How I can execute a store procedure using expression in SSIS 2005?

Thanks

Munna
Post #626389
Posted Sunday, December 28, 2008 9:56 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:50 AM
Points: 4,986, Visits: 11,684
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #626402
Posted Sunday, December 28, 2008 9:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
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
--========================






Pradeep Singh
Post #626403
Posted Monday, December 29, 2008 9:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 11:39 AM
Points: 10, Visits: 96
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

Post #626632
Posted Tuesday, December 30, 2008 11:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 904, Visits: 675
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.
Post #627521
Posted Friday, January 2, 2009 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 11:39 AM
Points: 10, Visits: 96
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
Post #628844
Posted Saturday, January 3, 2009 6:18 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:34 PM
Points: 904, Visits: 675
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.
Post #629391
Posted Friday, May 22, 2009 3:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 12:05 AM
Points: 270, Visits: 715
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
Post #721909
Posted Monday, May 25, 2009 1:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 12:30 AM
Points: 353, Visits: 487
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



-Forum Etiquette: How to post Performance Problems

-Forum Etiquette: How to post data/code to get the best help
Post #722859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse