Post reply

Execute SQL statement via OLEDB connection inside Script Task

  • November 5, 2014 at 1:07 pm

    #292185

    I've been trying to research this but I can't quite follow the web snippets, many of which refer to ADO connections or vague references to namespaces that I'm not sure how to use.

    I have a defined OLEDB connection manager in my SSIS package that refers to a sql server. Inside my script task, I need to execute a SQL statement. how can I do this?

  • November 13, 2014 at 8:07 pm

    #1758437

    What are you trying to do with your SQL statement?

  • November 14, 2014 at 8:12 am

    #1758611

    Could be executing an Update or a Delete, as required.

    In this case I found the solution! I didn't actually realize just how easy it was to convert my OLEDB connection manager to ADONET...so I went ahead and did that, and then used these 2 imports statements:

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports System.Data.SqlClient

    Then:

    Dim sqlConn As System.Data.SqlClient.SqlConnection

    Dim sqlComm As System.Data.SqlClient.SqlCommand

    Dim strSQL As String = ""

    Dim cm As ConnectionManager = Dts.Connections("ADONETConnection")

    Then:

    [...build up SQL string]

    Then:

    sqlComm = New System.Data.SqlClient.SqlCommand(strSQL, sqlConn)

    sqlComm.ExecuteScalar()

  • November 14, 2014 at 9:55 am

    #1758668

    I believe that there's an SQL Task, isn't there? Why not just skip the proverbial tower of Babel and call a stored procedure?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 14, 2014 at 9:59 am

    #1758672

    For sure, and I usually do that. This is in a situation where the preference (for whatever reason) is toward dynamic sql statement. And usually the reason for preferring that, is due to needing to loop through [an array, for instance] and execute multiple sql statements BEFORE exiting the script task.

    Sure, you could split it out into a bunch of small stored procedures and a whole bunch of execute sql tasks...but there are those who think of that as the long way around. (me included) 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply