Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Expression


Expression

Author
Message
munnabhaikidarho
munnabhaikidarho
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 96
Hi,

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

Thanks

Munna
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8356 Visits: 19492
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
ps.
ps.
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2257 Visits: 3668
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
munnabhaikidarho
munnabhaikidarho
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Sr SQL developer
Sr SQL developer
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 692
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.
munnabhaikidarho
munnabhaikidarho
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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
Sr SQL developer
Sr SQL developer
SSC Eights!
SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)SSC Eights! (922 reputation)

Group: General Forum Members
Points: 922 Visits: 692
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.
khushbu
khushbu
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 747
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
Vishal Singh
Vishal Singh
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 517
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search