Join me For Expert SSIS Training!

I’m honored to announce Expert SSIS – a course from Enterprise Data & Analytics!

The next delivery is 01-02 Apr 2019, 9:00 AM – 4:30 PM ET.

If you sign up by 31 Jan, you save money. Right now the course is on sale!

Data integration is the foundation of data science, business intelligence, and enterprise data warehousing. This instructor-led training class is specifically designed for SQL Server Integration Services (SSIS) professionals responsible for developing, deploying, and managing data integration at enterprise-scale.

You will learn to improve data integration with SSIS by:

  • Building faster data integration.
  • Making data integration execution more manageable.
  • Building data integration faster.

Agenda

  1. SSIS Design Patterns for Performance – how to build SSIS packages that execute and load data faster by tuning SSIS data flows and implementing performance patterns.
  2. SSIS Deployment, Configuration, Execution, and Monitoring – the “Ops” part of DevOps with SSIS using out-of-the-box tools and the latest utilities.
  3. Automation – how to use Business Intelligence Markup Language (Biml) to improve SSIS quality and reduce development time.

Did someone mention a sale?

Yep! The course is currently on sale until 31 Jan 2019!

I hope to see you there!

PS – Want to Learn More About Azure Data Factory?

My Learning Resources for SSIS

People often ask me, “Andy, where can I learn SSIS or learn more about it?” Well, you’re at one such location – my blog! I’ve been blogging about SSIS for about 10 years as I type this. There’s a lot posts here about SSIS – that’s the good news. The bad news is: There’s a lot of posts here about SSIS.

Some other places you can learn about SSIS from me:

I hope to see you at one of these events. If you read this blog, come up and introduce yourself. I’m the fat guy with a braided beard!

:{>

ETL Instrumentation: Logging SSIS Variable Values

During the December 2017 delivery of Expert SSIS, I was asked if there is an SSIS Catalog Logging Mode that will display the value of variables. I responded that I wasn’t aware of a logging level that accomplishes this, but then – as sometimes happens – I could not let it go. It is an excellent question. So I dug in…

Answer #1: YesBut

Here’s one way to do it: Open the Variables window in SSIS. Click the Grid Options button on the Variables window toolbar:

When the Variable Grid Options dialog displays, check the “Raise event when variable value changes” checkbox:

Click the OK button to return to the Variables window and note the new property: “Raise Change Event.” Set Raise Change Event to True:

Once deployed, the value of the variable will be logged in the SSIS Catalog at startup and each time the variable value changes:

Please note: the VariableValueChanged event is captured using the SSIS Catalog Basic Logging level.

Why isn’t this a resounding “Yes! Yes, you can log all variables values in the SSIS Catalog!”? Well, because this requires developers to open existing packages, open Variables, check the checkbox to even be able to see the property, and then change the property value – for every. single. variable.

Answer #2: Use a Script Task

You can add a Script Task to your SSIS package – similar to the Script Task shown below:

Open the Script Task Editor and click the ellipsis in the ReadOnlyVariables value textbox to open the Select Variables dialog. I like to select the System::PackageName and System::TaskName variables each time I use a Script Task for logging purposes (and I use Script Tasks for logging a lot). Then select the variable(s) for which you wish to log values:

When I design Production Data Integration solutions with SSIS, I log the values of parameters and variables at the start of each execution, and each variable used inside a Loop Container or Foreach Loop Container. Why? I don’t want to guess or dig or rely on defaults; I want the package to tell me what those values were when it executed. I consider this a best practice.

I add C# similar to that shown here to perform variable value logging:

Once deployed and executed, the Information Event raised in the Script Task publishes a message consumed by SSIS Catalog Basic Logging (the default SSIS Catalog logging level) as shown below:

I know many SSIS developers are uncomfortable using Script Tasks (and Script Components). While I understand the discomfort, I urge SSIS developers to learn enough .Net (C# or VB) to become comfortable using Script Tasks and Components in SSIS. In my opinion, .Net easily doubles your data integration capabilities with SSIS.

:{>

Learn more:
Expert SSIS Training (Live, Online)
IESSIS1: Immersion Event on Learning SQL Server Integration Services (In-person)