Executing SQL with parameters in SSIS

Question

How do I execute the SQL script below a SSIS project? I've tried setting up parameters & variables; however.... nothing I do seems to pass the parameters via SSIS

declare @businessunit varchar(255) = 'Test'
declare @advisor varchar(255) = 'Smith'
declare @iuid int = 
(
        select       U.[iuid]
        from         U
        inner join   [dbo].A on u.[ipartyid] = A.[iuserid]
        inner join   [dbo].B on A.[ibusinessunitid] = B.[ipartyid]
        inner join   [dbo].C on u.[ipartyid] = C.[ipartyid]
        inner join   [dbo].D on C.[ipartyid] = D.[ipartyid]
        where        1 = 1
        and          B.[name] = @businessunit
        and          D.[lastname] = @advisor
)

select       HHName 
            ,HHID = h.ihhid
            ,FNAME = 
                case
                    when charindex(',', h.vhhname) > 0 and trim(substring(h.vhhname, patindex('% i%', h.vhhname), len(h.vhhname))) in ('i', 'ii', 'iii')
                    then concat(dbo.Propercase(concat(trim(substring(trim(substring(h.vhhname, charindex(' ', h.vhhname), len(h.vhhname))), 1, charindex(' ', trim(substring(h.vhhname, charindex(' ', h.vhhname), len(h.vhhname)))))), ' ', trim(substring(h.vhhname, 1, (charindex(',', h.vhhname) - 1))))), UPPER(substring(h.vhhname, patindex('% i%', h.vhhname), len(h.vhhname))))
                    when charindex(',', h.vhhname) > 0 and nullif(h.vdescr, '') is null
                    then dbo.Propercase(replace(replace(concat(trim(substring(h.vhhname, (charindex(',', h.vhhname) + 1), len(h.vhhname))), ' ', trim(substring(h.vhhname, 1, (charindex(',', h.vhhname) - 1)))), '+', '&'), ' and ', ' & '))
                    else dbo.Propercase(replace(isnull(nullif(h.vdescr, ''), h.vhhname), ' and ', ' & '))
                 end
            ,RepNo = h.planid
from         [dbo].[HH] h
inner join   
(
    select       u.[usertype]
                ,a.[user_planid]
    from         [dbo].users u
    inner join   [dbo].user_access a    on           u.iuid = a.iuid
    where        1 = 1
    and          u.[usertype] <> 'e'
    and          u.iuid =  @iuid
    group by     u.[usertype],a.[user_planid]
) p
on           h.[planid] = p.[user_planid]

Answer 1

I'm going to assume you already have two SSIS variables that correspond to @businessunit and @advisor and they are being populated with the correct values already.

You can use an Execute SQL Task with parameter mapping to run your query. First thing you want to do is open the task editor, and configure your db connection. Next, hit the three dots next to SQLStatement to pull up the query editor window. Now you can start transposing your query, with a few modifications. I find that the Execute SQL Task works best when you separate variable declaration and assignment statements. You can use the following as your query text:

declare @businessunit varchar(255)
declare @advisor varchar(255)
declare @iuid int

SET @businessunit = ?
SET @advisor = ?

SET @iuid = 
(
        select       U.[iuid]
        from         U
        inner join   [dbo].A on u.[ipartyid] = A.[iuserid]
        inner join   [dbo].B on A.[ibusinessunitid] = B.[ipartyid]
        inner join   [dbo].C on u.[ipartyid] = C.[ipartyid]
        inner join   [dbo].D on C.[ipartyid] = D.[ipartyid]
        where        1 = 1
        and          B.[name] = @businessunit
        and          D.[lastname] = @advisor
)

select       HHName 
            ,HHID = h.ihhid
            ,FNAME = 
                case
                    when charindex(',', h.vhhname) > 0 and trim(substring(h.vhhname, patindex('% i%', h.vhhname), len(h.vhhname))) in ('i', 'ii', 'iii')
                    then concat(dbo.Propercase(concat(trim(substring(trim(substring(h.vhhname, charindex(' ', h.vhhname), len(h.vhhname))), 1, charindex(' ', trim(substring(h.vhhname, charindex(' ', h.vhhname), len(h.vhhname)))))), ' ', trim(substring(h.vhhname, 1, (charindex(',', h.vhhname) - 1))))), UPPER(substring(h.vhhname, patindex('% i%', h.vhhname), len(h.vhhname))))
                    when charindex(',', h.vhhname) > 0 and nullif(h.vdescr, '') is null
                    then dbo.Propercase(replace(replace(concat(trim(substring(h.vhhname, (charindex(',', h.vhhname) + 1), len(h.vhhname))), ' ', trim(substring(h.vhhname, 1, (charindex(',', h.vhhname) - 1)))), '+', '&'), ' and ', ' & '))
                    else dbo.Propercase(replace(isnull(nullif(h.vdescr, ''), h.vhhname), ' and ', ' & '))
                 end
            ,RepNo = h.planid
from         [dbo].[HH] h
inner join   
(
    select       u.[usertype]
                ,a.[user_planid]
    from         [dbo].users u
    inner join   [dbo].user_access a    on           u.iuid = a.iuid
    where        1 = 1
    and          u.[usertype] <> 'e'
    and          u.iuid =  @iuid
    group by     u.[usertype],a.[user_planid]
) p
on           h.[planid] = p.[user_planid]

Hit OK in the query editor window.

The ? in the SET statements tell the task to pull the values from the Parameter Mapping. So now let's configure the parameter mappings.

In the left pane of the Execute SQL Task Editor, click on Parameter Mapping. If your db connection is OLE or EXCEL, then the Parameter Name will start with 0 and increment by one for each additional parameter. If it's an ODBC connection, you'll start with 1 instead. The parameter names match up with the ordinal position of the ?. So in our example here, @businessunit would be the first parameter mapped and @advisor would be the second. Now you're going to add two parameters. Hit the Add button, then change the Variable Name to your first SSIS variable. Leave Direction set to Input, change Data Type to VARCHAR, set the Parameter Name, then set the Parameter Size to 255. Repeat for the second variable. Your paramter mappings should look something like this:

Make sure you hit OK to save all your changes.

Answer 2

For a script as long as this, one solution that may work for you depending on what you are trying to achieve is to use SSIS script tasks.

Script tasks allow you to use C# (Or Visual Basic) in order to execute SQL via the same System.Data.SqlClient class that you would normally use in other C# programs, such as a console or ASP.NET application.

For your SQL above, put it into a stored procedure, and then execute this stored procedure within the script task. You could then use SqlDataReader or SqlDataAdapter to then read and store the result into a model.

From there, you can choose to manipulate the data within the SSIS script task.

For Example:

SqlConnection connection = new Connection("connection string");

using(SqlCommand command = new SqlCommand("Trans-SQL or stored procedure name", connection)
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(/*Add your parameters...*/);

    connection.Open()
    SqlDataReader reader = command.ExecuteReader()
    while (reader.Read())
    {
        //Use reader["name"] in here to read values from response into a model
    }
}

Is a completely valid way of querying data within a SSIS task. If you would rather not deal with a Reader, you can use the SqlDataAdapter and use the Fill() method to store the result(s) in a dataset.

Overall, when dealing with complex data (and where efficiency isn't too much of a concern), I find that completing actions within SSIS script tasks that get triggered by the control logic is the easier way to use SSIS.

You may find this Integration Services Programming Overview documentation site useful as a reference for some of the things you can do with SSIS script tasks.

On a final note, please be aware that script tasks in SSIS do have some limitations, a key one is that there is generally worse support for newer C# features, that cause issues such as not being able to hit debug breakpoints.