SSIS: Assign a value to Variable using Dataflow Script task

I am not quite sure how many of you have tried to set or change the value of a variable using an SSIS script task, but if you have tried I am sure that you may have ran into a few road blocks.  Recently I tried to do this and I quickly realized that it is not as straightforward as I thought.  To do this configure your package so that you have a source of some type on the data flow design surface.  Drag a script task onto the data flow and connect it to the source.  Typically, you would set the variable you want to read from or write to as a ReadOnly or ReadWrite value on the Custom Properties of the script task.

This is only the case on the Control Flow.  In the case of the data flow this is not required.  I don't claim to be a developer, but I do know how to use the Internet to find a solution to a problem.  After searching for about 10 minutes I found three posts that assisted me in solving the problem.  Each post provided a little snippet of code that added to my solution.  Here is the code:

   1:      public override void Input0_ProcessInputRow(Input0Buffer Row)

   2:      {

   3:          

   4:          IDTSVariables100 vars = null;

   5:          VariableDispenser.LockOneForWrite("intMaxSalesDetailsID", ref vars);

   6:          if(!Row.SalesDetailID_IsNull)

   7:              vars[0].Value = Row.SalesDetailID;

   8:          vars.Unlock();

   9:      }

On line 4 declare an interface that is going to allow us to access the variable.  In the next line we lock the variable for writing.  In line six ensure that the value that is being assigned to the variable from the Input Row is not null.  Then on the next line set the value of the variable to the desired column from the buffer.  Lastly, unlock the variable.  That's all to it.  If you have other method, preferably a simpler approach, send me an email at pleblanc@pragmaticworks.com or post it here.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder TSQLScripts.com and SQLLunch.com.

Baton Rouge SQL Server User Group Meeting Tonight

Last Night (9/14/2010) I had the opportunity to present to the Wisconsin SQL Server User Group.  My topic was SSIS- Configuration Files, Deployment and a Little Performance Tuning.  The presentation was well received and I hope to have the opportunity to speak to the group again on another occasion. 

Tonight(9/15/2010) I will be speaking in front of my local SQL Server User Group in Baton Rouge.  The topic is Loading a Data Warehouse with SSIS.  In addition, I will doing a short presentation (Lightning Round) to the local .Net User group just before the SQL User group begins.  If you are in and around the Baton Rouge area this even stop by.  The details for both groups are as follows:

There will be great prizes supplied by Enta including:

A Nook (plays all three e-reader types), Madden 2011, Windows 7, and many more...

Date: September 15, 2010

Time: 5:45 pm

Location: Lamar Advertising, 5551 Corporate Blvd, Baton Rouge, LA 70808

Sponsored By: User Group Support Services

Agenda:

5:45 - 6:15 pm: Networking and Refreshments

6:15 - 6:30 pm: Lightning Round

6:30 - 7:45 pm: (.Net) Introduction to RIA Services and (SQL) Loading a DataWarehouse with SSIS

7:45 - until: Open Forum for questions and Raffle

Lightning Round (Lamar Kitchen)

Topic: Introduction Data-tier applications

Overview: SQL Server 2008 R2 introduced a new feature called Data-tier applications (DAC). A data-tier application defines the SQL Server Database Engine schemas and objects that are required to support an application. Join Patrick for a brief introduction to this new feature and learn how to author, build and extract the applications from an existing database using Visual Studio 2010 and SQL Server Management Studio.

Speaker: Patrick LeBlanc, SQL Server MVP, MCTS

.Net User Group (Lamar Kitchen)

Topic: Introduction to RIA Services

 Overview: RIA Services and Silverlight 4.0 are recent releases and are tightly integrated with .NET 4.0 and Visual Studio 2010. RIA services is a very powerful productivity tool/framework and can be a bit overwhelming when trying to learn. This session will cover an overview of RIA services and then dive into the details using the new Silverlight Business Application Template in VS.NET 2010. We will cover some tips to modifying the code that is generated by the template to tailor it for most enterprise environments.

Speaker: Mike Huguet

BIO: Company:

Sparkhound Blog: http://geekswithblogs.com/mikehuguet

Mike Huguet is a Solutions Architect for Sparkhound. He is an active member of the community acting as co-leader of the Baton Rouge .NET User Group and committee member for the BR SQL Saturday event. He is also a member of the Microsoft SharePoint Patterns and Practices Advisory team for the recent release for SharePoint 2010. Mike has a software development background and has been working with .NET since Beta 1 of the 1.0 framework.

SQL Server User Group (3rd Floor Board Room)

Topic: Loading a DataWarehouse with SSIS

Overview: With SSIS Data Warehouse developers have a new method of loading their DW. In this session Patrick LeBlanc will show you how to use SSIS to load your Dimensions and Facts. We will discuss different methods that can be used in various environments. He will show you how to fully reload or incrementally load your Data Warehouse.

Speaker: Patrick LeBlanc, SQL Server MVP, MCTS

BIO: Patrick LeBlanc, SQL Server MVP, is currently a Business Intelligence Architect for Pragmatic Works. He has worked as a SQL Server DBA for the past 9 years. His experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. He is also the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Rouge Area SQL Server User Group. Patrick is a regular speaker at various SQL Server community events, including SQL Saturday’s, User Groups and the SQL Lunch. 

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com

Where do you get your motivation and drive?

I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first is my family of course. My wife has this unbridled drive for success that cannot be measured at any level. Whenever anyone in the house says I can’t, I don’t know how to…, You can’t or some variation of those sentences she immediately corrects us. Can’t is not part of our vocabulary. Next it’s my kids, they think I am Superman, Bill Gates, Jay Z, Barack Obama, a barber, a mechanic, a chef, etc.. You get the picture. It's funny, if we are watching TV or we see someone do something, one of my kids will always say, Dad you can do that.  With that long list of expectations a man has to be driven to learn and succeed.

Then there is the SQL Server Community. For me the community is boundless. We learn from each other. I became active in the community about 3 years ago when I started a SQL Server User Group in Baton Rouge. I had just installed SQL Server 2005 and set up a GEO cluster. In the process I learned so many interesting things about the new features of 2005 and I wanted to share them. I talked to the local .Net user group then I spoke with PASS and the user group was born. After the first presentation I became addicted to presenting. I get a rush from sharing what I learn and for some reason not being able to answer a question. This drives me to learn more so I can share more. So I guess my addiction to presenting and the need to share information is a major contributor to my drive.

Another source and probably the biggest factor to my motivation is myself. Everyone morning when I wake up I say, this is going to be a productive day. What does productive mean? I am going to write a new presentation, I am going to get a new speaker for the SQL Lunch, I am going to help someone solve a problem, I am going to learn something new about SQL Server, I am going to read something new today, I am going to help someone start a User Group. I often end the day with a recount of the day. What did I do? Some would call me a GEEK or maybe even a NERD, but these days that equals success. Think back to your youth, and all of the geeks or nerds that you knew. Look them up. What are they doing? I actually talked to a couple of the GEEKS that went to our high recently and they are pretty successful.  I am not saying that all GEEKS are successful, but there seems to be a decent trend in their favor.

Finally, there's GOD and my parents.  We all know that all this that you know as PATRICK would not exist.  So, I definitely have to mention that trio.

So, what drives you? What is your motivation? Share it with us.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com

MDX Puzzle #7 - Solution

Puzzle #7 had an interesting twist.  You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code.  As always, there are a couple of ways to solve this puzzle.  So here is what I started with:

This query satisfies most of the requirements, but one thing you should notice here is the tuple.  I started out here with only the Country and State-Province, which I thought would work.  Then I added the following calculated member:

In the calculation I used the IIf function to dynamically determine whether or not the currency code was US or International.  The function is used in the same way as you would use it in Excel or Reporting Services.  It accepts three arguments.  The second and third arguments are returned based on the evaluation of the first argument, which is the logical expression.  This expression should evaluate to true or false.  If it evaluates to true the second argument is returned and if false the third argument is returned.  In addition, I used the CurrentMember function.  This function obtains a reference to the member identified for a particular hierarchy in the current context. When I coupled the function with my initial query, I thought I was done.  Unfortunately, the calculation evaluated every code to International even when it was USD. 

To solve the problem I had to modify the tuple from the first query to include the Source Currency Code.  After the change was made the results satisfied all of the puzzle requirements.  Here is the solution:

WITH MEMBER [Measures].[Currency Type]

AS

IIF

(

    [Source Currency].[Source Currency Code].CurrentMember=[Source Currency].[Source Currency Code].[USD],

    "US",

    "International"

)

SELECT 

    {[Measures].[Reseller Sales Amount],[Measures].[Currency Type]} ON COLUMNS,

    NONEMPTY(

    {

        (

            [Sales Territory].[Sales Territory Country].[Sales Territory Country],

            [Geography].[Geography].[State-Province],

            [Source Currency].[Source Currency Code].[Source Currency Code]

        )

    }

    )ON ROWS

FROM [Adventure Works]

Tell me what you think and post your solutions.  Let's see how much our solutions differed.  Stay tuned for Puzzle #8.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.