Getting Started with Variables in SSIS

August 25, 2009 by Michelle Ufford · 9 Comments
Filed under: Business Intelligence, Syndication 

I recently had to create an SSIS package that used variables to pass data between procs, and I thought it would make a good topic for a blog post. There are many scenarios as to the how and why to use variables in SSIS, but we’re going to keep it pretty simple. Let’s assume we have some need to retrieve data from Proc A, pass it to Proc B, and store the results in Table C. First, let’s set up our environment:

USE AdventureWorks;
Go
 
CREATE PROCEDURE dbo.LastOrderGet_sp
AS
SET NOCOUNT ON;
BEGIN
 
    SELECT MAX(SalesOrderID) AS 'LastSalesOrderID'
    FROM AdventureWorks.Sales.SalesOrderHeader WITH (NoLock)
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go
 
CREATE PROCEDURE dbo.ProcessLastOrder_sp
    @LastOrderID INT
AS
SET NOCOUNT ON;
BEGIN
 
    SELECT SalesOrderDetailID
        , ProductID
        , OrderQty
        , LineTotal
    FROM AdventureWorks.Sales.SalesOrderDetail WITH (NoLock)
    WHERE SalesOrderID = @LastOrderID;
 
    SET NOCOUNT OFF;
    RETURN 0;
END
Go
 
CREATE TABLE dbo.testStage
(
      SalesOrderDetailID    INT
    , ProductId             INT
    , OrderQty              SMALLINT
    , LineTotal             NUMERIC
);
Go

Now for the fun stuff!

(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the “how to create a project”, etc. stuff and just going to the pertinent parts).

Inside BIDS (Business Intelligence Development Studio), create a new SSIS project and call it what you will. If your Variable window is not already open, open it now by going to View –> Other Windows –> Variables.

Open Variables Window

Open Variables Window

Now let’s create a variable. To do this, click on the little icon in the upper left-hand corner of the Variables window. Name the variable LastSalesOrderID.

Create a variable

Create a variable

After you create the variable, you should now see it in the Variables window. Make sure the scope of the variable is the name of your project, which is “Blog” in my case (for obvious reasons); this means the variable is defined at the package scope. Once you’ve confirmed that the variable exists, create an Execute SQL task.

(Variables in SSIS, like in other programming languages, can have different scopes. For instance, a package scope means the variable can be accessed anywhere within the package, but a variable with a Data Flow scope can only be accessed within the specified Data Flow task.)

Create Execute SQL Task

Create Execute SQL Task

Double-click on your Execute SQL Task and configure with the following values:

  • Set “Result Set” to Single Row.
  • Set your Connection to your appropriate data source.
  • Set your SQL Statement to: Execute AdventureWorks.dbo.LastOrderGet_sp;
Set up your Execute SQL Task

Set up your Execute SQL Task

Now click on “Result Set” and click on “Add.” You’ll want to put the name of the column that’s returned by the proc in the “Result Name” column; in our case, that’ll be LastSalesOrderID. Click on the Variable Name column and scroll down until you find the appropriate one (User::LastSalesOrderID).

Mapping the results to a variable

Mapping the results to a variable

Go ahead and add a Data Flow task to the designer surface. We don’t need to use a Data Flow task here — for example, we could use another Execute SQL task instead — but this will help demonstrate one way to use variables.

Add Data Flow Task

Add Data Flow Task

Double-click on the Data Flow task and add an OLE DB Source, then double-click on it to open up the properties. Enter the following text in the “SQL Command text” window:
Execute AdventureWorks.dbo.ProcessLastOrder_sp ?
The question mark (?) tells SSIS to expect a parameter.

Edit OLE DB Source

Edit OLE DB Source

Now click on the Parameters button on the left. This is where we map our variable to our parameter. For the “Parameters” value, enter @LastOrderID (the parameter the stored procedure is expecting). In the “Variables” column, click on the drop-down and navigate to the User::LastSalesOrderID variable.

Map Variables to Parameters

Map Variables to Parameters

Finally, set up an OLE DB Destination, and configure the OLE DB Source to load into the testStage table.

Configure OLE DB Destination

Configure OLE DB Destination

At this point, you should be able to successfully execute your package. Upon successful execution, the testStage table will return the following results:

SELECT * FROM testStage;
 
SalesOrderDetailID ProductId   OrderQty LineTotal
------------------ ----------- -------- ------------------
121315             878         1        21
121316             879         1        159
121317             712         1        8

That’s all for now. Hopefully this gives you an idea of how easy and useful it is to work with variables in SSIS.

Tags: , , ,

East Iowa SQL Saturday – Call For Speakers, Open Registration

August 18, 2009 by Michelle Ufford · Leave a Comment
Filed under: PASS, Presentations, Syndication 

A few announcements regarding the East Iowa SQL Saturday:

  • The date has changed to October 17th due to scheduling conflicts. Please update your calendars.
  • We’re still looking for speakers! We currently have 10 submissions, but we’d like to have double that. If you’re even thinking about submitting a session, please do! Who knows, Iowa City may be closer than you think.
  • Registration is open! If you’re planning to attend the East Iowa SQL Saturday, please make sure to register by clicking on the “Register” link and completing the short questionnaire. Seating is limited, so make sure to register soon.

    • Also, if you’re in the area and would be interested in volunteering, please send me an e-mail at michelle at sqlfool dot com.

    Tags: , , ,

    Find Recently Executed Stored Procedures

    August 3, 2009 by Michelle Ufford · 8 Comments
    Filed under: SQL Tips, Syndication, T-SQL Scripts 

    This past weekend, we had an issue where replication fell far behind on one of our databases. The replicated database is used for all sorts of reporting, so the immediate need was to identify processes that may have been affected by the incomplete data.

    Now, there’s hundreds of stored procedures that reference the affected database; the trick is finding out which ones are relevant. To do this, I used the sys.dm_exec_query_stats DMV. This does two things for me. One, it shows me a list of stored procedures in cache, meaning they’ve been executed relatively recently and are probably relevant to the search. Secondly, it shows me the last execution time, which in some cases may have been before the issue, meaning I do not need to worry about re-running those processes.

    Here’s the query I used:

    SELECT DB_NAME(dest.[dbid]) AS 'databaseName'
    , OBJECT_NAME(dest.objectid, dest.[dbid]) AS 'procName'
    , MAX(deqs.last_execution_time) AS 'last_execution'
FROM sys.dm_exec_query_stats AS deqs
Cross Apply sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.[TEXT] Like '%yourTableName%' -- replace
    And dest.[dbid] IS Not Null  -- exclude ad-hocs
GROUP BY DB_NAME(dest.[dbid])
    , OBJECT_NAME(dest.objectid, dest.[dbid])
ORDER BY databaseName
    , procName
OPTION (MaxDop 1);

    This will return results similar to:

    databaseName         procName                       last_execution
-------------------- ------------------------------ -----------------------
AdventureWorks       ufnGetProductListPrice         2009-08-03 09:57:25.390
AdventureWorksDW     DimProductCategoryGet_sp       2009-08-03 09:59:05.820
AdventureWorksDW     DimProductGet_sp               2009-08-03 09:58:38.370

    I want to stress that this is *not* a list of all referencing objects, but rather a list of recently executed stored procedures that are still in memory. This list may not be accurate if your cache has recently been flushed or if you’ve recently rebooted your server.

    Tags: , , ,