Expression

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Expression

Expression

Rate Topic

Display Mode

Topic Options

Author

Message

munnabhaikidarho

Posted Sunday, December 28, 2008 9:00 PM

Grasshopper

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

Phil Parkin

Posted Sunday, December 28, 2008 9:56 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:28 AM
Points: 4,322, Visits: 9,661

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #626402

ps.

Posted Sunday, December 28, 2008 9:58 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 02, 2013 2:32 AM
Points: 2,236, Visits: 3,620

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

munnabhaikidarho

Posted Monday, December 29, 2008 9:28 AM

Grasshopper

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

Sr SQL developer

Posted Tuesday, December 30, 2008 11:52 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:36 AM
Points: 856, Visits: 636

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

munnabhaikidarho

Posted Friday, January 02, 2009 8:42 AM

Grasshopper

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

Sr SQL developer

Posted Saturday, January 03, 2009 6:18 PM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:36 AM
Points: 856, Visits: 636

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

khushbu

Posted Friday, May 22, 2009 3:17 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, January 21, 2013 11:50 PM
Points: 267, Visits: 695

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

Vishal Singh

Posted Monday, May 25, 2009 1:01 AM

Old 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 »

Permissions