Expression

  • Hi,

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

    Thanks

    Munna

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • Sr SQL developer - Tuesday, December 30, 2008 11:52 AM

    My 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.

    Hi,
    I see you are familiar with the ActiveX script and I want to ask you a question regqrding a problem I am having.
    I have a SSIS package that I run manually everything month end for reporting.
    It ran successfully in last month but this month it is giving an error message below;
    "Error: 0xC00291B1 at Populate And Copy Temp DB, ActiveX Script Task: User script threw an exception: "Error Code: 0 Error Source= ADODB.Connection Error Description: Provider cannot be found. It may not be properly installed"
    Please, can you help to figure out how this error can be resolved.
    Thanks
  • 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