THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is a Data Philosopher at Andy Leonard Consulting, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

  • Leaving Linchpin People

    I’m sad and excited to announce I am no longer with Linchpin People.

    I’m sad because I will miss working with and leading the awesome team at Linchpin People. I’m excited to return to independent consulting as Andy Leonard Consulting!


  • Presenting at BIG PASS 10 Dec 2015!

    I am honored to announce I will be presenting (remotely) “I See a Control Flow Tab. Now What?” at BIG PASS in Lake Forest, California 10 Dec 2015 at 6:30 PM PT!

    This demo-intense presentation is for beginners and developers just getting started with SSIS. Attend and learn how to build SSIS packages from the ground up.

    Register today!


  • SSIS Catalog Compare 3-Minute Drill

    The actual time is about 3:15…

    In a new video, I demonstrate how to use SSIS Catalog Compare to deploy an SSIS Project and all configurations metadata from one SSIS Catalog to another. I call it the 3-Minute Drill.



    Learn more:

    Catalog Compare 0.5 Browsing the Catalog
    Catalog Compare 0.5 Comparing Catalogs
    SSIS Catalog Compare

    Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 - 10, 2015
    IESSIS1: Immersion Event on Learning SQL Server Integration Services
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Stairway to Integration Services

    SSIS Design Patterns

    Andy Leonard Consulting – I am here to help.

  • Announcing Three Free Biml and SSIS Webinars

    Thanks to everyone who attended the Biml 101 webinar! The recording and demo files for Biml 101 are available here.

    I’ve scheduled three additional webinars in the series:

    Other folks from the Biml Community will be joining me for presentations beginning in 2016. Since there’s so much going on, I’ve started Andy's Biml mailing list to help keep you aware of Biml training from Andy Leonard Consulting. I hope you’ll join and I promise to keep the email to a minimum (and you can always unsubscribe if it gets to be too much). Consider joining today!


    Learn more:

    Related Training
    Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 - 10, 2015
    IESSIS1: Immersion Event on Learning SQL Server Integration Services
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles
    Stairway to Integration Services
    Stairway to Biml

    Related Resources
    Join Andy's Biml mailing list!

    Related Books
    SSIS Design Patterns

    Andy Leonard Consulting – I am here to help.

  • PASS Summit 2015 Feedback

    We are part of an awesome community. The SQL Server Community or SQL Family, as many call it, is astounding. Our annual Big family reunion is the PASS Summit. I received feedback from attendee evaluations the other day and I would like to share some of the feedback for one reason: Many people spoke at the PASS Summit 2015 for the very first time in October 2015. Presenting at the largest SQL Server conference on the planet is daunting. Speaking there the first time is flat-out intimidating.

    My primary audience for this post is presenters, especially that handful of people who spoke for the first time at the PASS Summit 2015. But I think a broader audience of speakers with limited experience or people who desire to become speakers may also benefit from these words (that’s why I decided to blog about it!).

    “How’d You Do at the PASS Summit 2015, Andy?”

    I’m glad you asked. I had a great time presenting! Why is that my first response? Who cares about how I felt, for goodness sake? I was there to do a job, right? Well, yes and no. In exchange for presenting, I am allowed to attend the PASS Summit for free. Paying attendees are there to listen to me deliver my presentation, so I am working for them. As a Data Philosopher at Andy Leonard Consulting, part of my job is to find gainful employment in the form of training or consulting gigs. In that sense, presenting at the PASS Summit 2015 (or anywhere) is at least partially work-related.

    But work is not why I present.

    I present because I love learning. I get excited about learning new stuff, whether it’s technology or something else. I always have. I suspect I always will. Why? I don’t know. But there’s no denying I thoroughly enjoy learning new stuff.

    Presenting is one way to share this love with others. And sharing the love is why I present.

    What makes for a great time presenting?

    1. I know the material.
    2. I enjoy the material.
    3. I have a story to tell.
    4. I have “a good story-telling day.”

    That last one is tricky so I will explain. I suffer from have ADHD. Sometimes ADHD is a blessing. When presenting, though, I need to be as focused as possible. ADHD does not promote focus.

    “What Feedback Did You Receive From Summit Attendees, Andy?”

    Ah, you’ve learned well. It’s always best to ask Data Philosophers with ADHD specific questions. I feel the ratings and feedback were perhaps the highest and best I have ever received from attendees of the PASS Summit.

    I’m a huge fan of the 3-point rating system employed by the PASS Program Committee for evaluations this year. There were only four questions on the evaluation. I admire those decisions greatly. Kudos to the PASS Summit Program Committee!

    The questions asked were:

    1. Overall Session - Did the title, abstract, and session level align to what was presented?
    2. Session Content – Was the content useful and relevant?
    3. Presentation - Was the speaker articulate, prepared, and knowledgeable on the subject matter?
    4. Promotions - Did the session contain blatant sales material or self-promotion such as blogs, company, or services?

    My “nummers” (a Hazzard-ism):

    Use Biml to Automate SSIS Design Patterns

    • Overall Session: 2.96
    • Session Content: 2.85
    • Presentation: 2.92
    • Promotions: 1.23

    A sample of comments:

    • Speaker was good.  Not a level 400 session.
    • Very good content but a little bit hard to follow at times.  The concepts did get through though.
    • I enjoy sessions with Andy.  That said, he treads a fine line with promoting BIML the product versus the usefulness of using BIML. 
    • Lots of info efficiently presented, straight to the point. Great brewing of ideas to automate package creation using patterns, possibilities through the roof when you follow standards and guidelines.
    • This is the best session I have seen Andy do; SQL Server conferences are awash with introductory material on BIML, but very few presenters are addressing the complexities that arise in non-trivial BIML projects.
    • Best session so far. Fantastic information and presentation.
    • Loved it. Very excited to use moving forward. He did say, "Tough", though.

    My thoughts:

    I’m not as good as the positive comments indicate. The first clue is the negative comments. I’m always fascinated by comments on the level of a session. Paul Randal and Adam Machanic touch on the topic of Level in their excellent posts (click the links and read if you’re a presenter or want to be a presenter). Peeves make lousy pets. That said, comments about level are a pet peeve. Why?


    Three points…

    Every single session I deliver is going to contain material that is below the session level. Every. single. one. I was an instructor before I started working with database technology. Instruction always involves ramping up to a topic – starting with the familiar and working into the unfamiliar. Unless you’re marketing. I don’t do marketing – at least not intentionally.

    I’m not sure how to fix the complaint in the comment. I’m not even sure what the complaint is. Is the issue that the session was really a 500-level? a 300-level? I’m going to assume the complaint is that the session didn’t rise to the advertised level of 400, which brings me to…

    Your “400” is different from the “400” of others. In addition (pun intended), my “400” on SSIS and Biml is different from my “400” on C#. The session level is not static. Questions from attendees can impact the overall level of the material delivered. Each time I’ve followed up on similar comments, I’ve learned the person is communicating, “I expected to learn more than you shared.” Now that’s a useful comment! I either need to deliver more or do a better job communicating my intentions in the abstract (see Adam’s awesome post). As a data professional, I have way too much respect for numbers to see them bandied about in this manner. It bugs me. (Can you tell?)


    The last comment is a light-hearted jab. You will have to listen to the session recording to get the inside joke.

    Hacking the SSIS Catalog

    • Overall Session: 2.90
    • Session Content: 2.87
    • Presentation: 3.00
    • Promotions: 1.14

    A sample of comments:

    • I did pick up a couple interesting things, but I left the session feeling frustrated.  This felt more like a 200 level session instead of 400 level that I was expecting.
    • My only issue was that the session built on a previous (biml) session that Andy had done previously. It wasn't needed to understand the content but it would have been nice to list that as a helpful pre-requisite, since he used content from that previous… (comment was truncated)
    • Andy had a couple of issues with his demo, but handled it like the professional that he is.  He moved on and continued presenting the next point in his presentation.  He even poked fun at himself which I feel was a good add for the attendees.
    • I barely understood most of what this was explaining, but it was one of my favorite sessions!  The style and pacing was just right to keep me caught up on a subject I know very little about.
    • I thoroughly enjoyed the presentation and especially the speaker. Easily gets my vote for the best speaker of the week!  That was fun learning. Thank you so much!
    • Andy seems like a "mad genius"! His presentation was my favorite of the whole conference. The material was truly one-of-a-kind and extremely useful. Andy's delivery of the material was both confident and hilarious. I won't miss the chance to attend another
    • Andy does what he wants and did an outstanding job on how simply add some custom tables, procedures, and views to easily look and manage dw load

    My thoughts:

    The comment on session level is actionable. (I’ve beat that horse enough for one blog post.) The comment about connecting material from the two sessions is very fair. The truth is, I made the decision to connect them after delivering the first session. As the commenter points out, it didn’t matter which SSIS packages I used to demonstrate customizing SSIS package execution within the SSIS Catalog. But it’s fair to point out that I didn’t disclose I would be using SSIS packages from the other session demos prior to the beginning of this presentation. I had issues with a demo because I built something on-stage in front of everyone without rehearsing it first. My friends who are fantastic presenters tell me I should not do this. I respectfully disagree. I am sharing about software development (Biml and SSIS are software development). I will continue to build ad hoc demos in front of people during presentations – and fail – because I want everyone in the room to understand that failure is a normal (and good) part of software development.

    Again, the last comment contains an inside joke from the session that will make more sense if you listen to the recording.


    It is always an honor to present. I sincerely appreciate good feedback because it helps me improve. Thanks to everyone who attended my sessions and to everyone who will listen to the recordings when they become available!


  • Do You Need to Update Your PASS Profile or SQL Saturday Speaker Profile?

    I recently needed to update my PASS and SQL Saturday speaker profiles. I emailed my good friend and Awesome Community Person Karla Landrum for help, and Karla kindly responded with detailed instructions that even I could follow. I decided to capture and share my experience updating my PASS and SQL Saturday profiles.

    Updating Your PASS Profile 

    Log into


    Click on your name in the upper right corner of the PASS web site.


    Click the “myProfile” link on the left side of the page:


    Edit your profile!


    Updating You SQL Saturday Speaker Profile

    You may update your SQL Saturday Speaker Profile by logging into the SQL Saturday website and clicking any SQL Saturday event:


    Click the Speaker link at the top of the page:


    Click the Speaker Profile link:


    Edit your speaker profile!



    If you need to update your PASS profile or SQL Saturday speaker profile, I hope this information helps.


  • Learn to Code SSIS: The Execute SQL Task

    Note: This article was first published at SQL Authority.

    With “SQL Server” included in the name of SQL Server Integration Services (SSIS), it is easy for people to assume SSIS is simply a database tool or accessory. Let there be no mistake, SSIS is a software development platform. To give you a better understanding of how to use SSIS as a development platform, I’ll write a series of blogs that step through how to use Control Flow tasks. This first article will cover the Execute SQL Task.

    When developing solutions with SSIS, I use a handful of Control Flow tasks:

    • Execute SQL Task
    • Data Flow Task
    • Script Task
    • Execute Package Task
    • File System Task
    • Execute Process Task

    This is a list of the which tasks I use most – from most-used to least-used. In this article I provide a basic example of configuring the SSIS Execute SQL Task:


    Three things are required to configure an Execute SQL Task:

    1. Connection Type
    2. Connection Manager
    3. SQL Statement

    Connection Type

    The default Connection Type is OLE DB:


    I configure Execute SQL Tasks to execute SQL statements like:

    • truncate a table
    • update or insert a single row of data
    • call a stored procedure

    I typically use OLE DB or ADO.NET connection types; but I occasionally use ODBC connection types. When executing parameterized statements I find ADO.NET offers a cleaner interface.

    After selecting a connection type, selecting the Connection Manager is next. The Execute SQL Task Editor filters the list of available connection managers, displaying only connection managers of the (connection) type configured in the previous step:


    The last required step to configuring an SSIS Execute SQL Task is to supply an SQL Statement. There are two properties for this: SQLSourceType and SQLStatement. The SQL Source Type property specifies the source of the SQL Statement as one of the following:

    • Direct Input
    • File Connection
    • Variable

    The SQL Statement can be entered manually (Direct Input). It can be stored in a file (File Connection) or the SQL Statement can be stored in an SSIS variable (Variable). In most cases you will manually enter the query:


    Right-click the task and click “Execute Task” to test the task configuration. Or simply press the F5 key to execute the entire SSIS package:


    You now know the basics of configuring an SSIS Execute SQL Task. Go code!



    Learn more:

    Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 - 10, 2015
    IESSIS1: Immersion Event on Learning SQL Server Integration Services
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Stairway to Integration Services

    SSIS Design Patterns

    Andy Leonard Consulting – I am here to help.

  • Biml 101 Recording and Files Are Now Available!

    I had an awesome time sharing about Biml (Business Intelligence Markup Language) earlier today! Thank you to everyone who registered and turned out for the live presentation.

    For those unable to make it to the live presentation, the recording is now available for viewing.

    I’ve posted the Mist and SSIS solutions source code. You can get them here:

    Right after I finished the webinar, I realized I did not demonstrate importing a package using BimlOnline. Bad me! So I created a short video (~1 minute) to demonstrate. You can view it here.




    Learn more:

    Biml 102: Using Biml as an SSIS Design Patterns Engine
    Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 - 10, 2015
    IESSIS1: Immersion Event on Learning SQL Server Integration Services
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Stairway to Integration Services
    Stairway to Biml

    Join Andy's Biml mailing list!

    SSIS Design Patterns

    Andy Leonard Consulting – I am here to help.

  • Managing SSIS Catalog Project Parameter Values

    One of the coolest features of the SSIS Catalog is Catalog Environments. Catalog Environments offer a compelling and rich solution for Catalog Project Parameter value externalization and configuration management, whether the parameters are connections or parameter values. Configuring and using Catalog Environments involves four steps:

    1. Create the Catalog Environment
    2. Create and Configure the Catalog Environment Variables
    3. Reference the Catalog Environment from a Catalog Project
    4. Map Catalog Environment Variables to Catalog Project Connections and Parameters

    Create the Catalog Environment

    Create an SSIS Catalog Environment by connecting to an SSIS Catalog using SQL Server Management Studio (SSMS) Object Explorer. Expand the Integration Services Catalogs node, the SSISDB node, and the desired folder node. Right-click the Environments virtual folder and click “Create Environment…”:


    Give the environment a name and (optional) description:


    Click OK to create the Catalog Environment.

    Create and Configure the Catalog Environment Variables

    Return to SSMS Object Explorer and double-click the Catalog Environment to open it for editing. Add and configure Catalog Environment Variables on the Variables page:


    Click OK to save the Catalog Environment configuration changes.

    Reference the Catalog Environment from a Catalog Project

    In SSMS Object Explorer, right-click a Catalog Project and click “Configure…” to create a reference between the Catalog Project and Catalog Environment:


    When the Configure window opens, add a reference to a Catalog Environment on the References page. Catalog Environments are scoped at the SSIS Catalog Folder level. You can reference Catalog Environments in the same Catalog Folder as the Catalog Project or in a different Catalog Folder:


    Once referenced, the Catalog Environment will appear in the Reference grid on the Catalog Project Configure References page. The “.” in the Environment Folder column indicates the Catalog Environment resides in the same Catalog Folder as the Catalog Project:


    Map Catalog Environment Variables to Catalog Project Connections and Parameters

    On the Parameters page of the Configure window, you can map Catalog Environment Variables to values by clicking the ellipsis on the Parameter grid row that corresponds to the parameter you wish to configure:


    Clicking the ellipsis opens the Set Parameter Value dialog. Click the “Use environment variable” option and select the Catalog Environment Variable from the corresponding dropdown:


    The dropdown list is filtered to only display Catalog Environment Variables of a data type compatible with the Catalog Project Parameter value.

    Successful mapping is indicated by the underlined name of the Catalog Environment Variable in the Parameter Value column of the Parameters grid:


    Catalog Project Parameter Manual Overrides

    You can also manually override the value of a Catalog Project Parameter by clicking the ellipsis and select the “Edit value” option:


    When a Catalog Package Parameter value is manually overridden using the Edit value function, it displays in Bold font in the Parameter Value column of the Parameters grid. Catalog Package Parameter values that are not mapped to a Catalog Environment Variable or manually overridden are configured to execute with their design-time default values and the text in the Parameter Value column of the Parameters grid is not decorated:



    SSIS Catalog Environments provide a rich and robust solution for parameter externalization.


  • November 2015–Learn SQL Server and Visual Studio

    A reader emailed to ask what I recommend for learning current software and database development. I responded with these links:

    I believe the software is all free, though it is a combination of community and (time-limited) technology previews. Save your code in a shared folder on your hard drive (VirtualBox supports shared folders – learn how to use them).

    This will get you started.

    Happy learning!


  • Precon - SSIS Design Patterns and BIML: A Day of Intelligent Data Integration–8 Jan 2016, Alpharetta Georgia

    PackageSVG        BimlWheel

    I’m excited to announce I will be delivering a precon 8 Jan 2016 in Alpharetta Georgia for SQL Saturday 477 – Atlanta BI Edition titled SSIS Design Patterns and BIML: A Day of Intelligent Data Integration! This precon is all about DILM (Data integration Lifecycle Management).


    SSIS Design Patterns and Biml: A Day of Intelligent Data Integration

    Join Andy Leonard, SSIS trainer, BimlHero, co-author of SSIS Design Patterns, the Stairway to Integration Services and Stairway to Biml series at SQL Server Central, and Managing Geeks, for a day of training focused on intelligent data integration. Andy is an experienced SSIS consultant, but he also led an enterprise team of 40 ETL developers on projects that spanned 2.5 years. And delivered.

    The target audience for this course is data integration developers and architects who want to learn more about SSIS performance, DevOps, execution, best practices, code reuse, and automation.

    Attendees will learn:
       - a holistic approach to data integration design.
       - a methodology for enterprise data integration that spans development through operational support.
       - how automation changes everything. Including data integration with SSIS.

    Topics include:

    1. SSIS Design Patterns
       Data Flow Performance
       ETL Instrumentation

    2. Executing SSIS in the Enterprise
       The SSIS Catalog - the good, the bad, and the ugly.

    3. Custom SSIS Execution Frameworks

    4. DevOps and SSIS
       A (Condensed) Sprint in the Life of a Data Integration Solution
       Version Control and SSIS

    5. Business Intelligence Markup Language
       A Brief Introduction to Biml in BimlStudio

    6. SSIS Design Patterns + Biml
       Putting the pieces together.

    7. SSIS Design Patterns + Biml + Custom SSIS Execution Frameworks
       Executing the new combinations.

    8. SSIS Design Patterns + Biml + Custom SSIS Execution Frameworks + DevOps
       Enterprise-class data integration with SSIS.

    Early-bird pricing is in effect until 30 Nov 2015.

    Register here today!

    I hope to see you there.


  • An Overview of SSIS Variables–featured at SQL Server Central

  • SSIS 2016 Supports Single-Package Deployment

    One of the most anticipated features of SQL Server 2016 Integration Services (SSIS 2016) is support for deploying a single SSIS package from an SSIS project.

    “Why Is This A Big Deal, Andy?”

    I’m glad you asked. Consider the following scenario:

    • You deploy version 1 of an SSIS Project containing 20 SSIS Packages to Production.
    • You begin working on version 1.1 of the project, making changes to several of the packages and some tables.
    • A bug is discovered in one of the packages in Production.
    • Now what?

    Hopefully you’ve been using source control and checking in regularly. If you have been using source control, you check in your current changes, perform a Get Specific Version to retrieve the version last deployed to Production. After you find and fix the bug – and test the fixes – you check in those changes and re-deploy to Production.

    If you haven’t been using source control, you can export the project from Production and begin there.

    There’s No Free Lunch

    After checking in and deploying the Production fix you perform a Get Specific Version to retrieve the version of the Project that contains the code you were working on before you stopped to fix Production. And then you need to make a decision:

    You are in an open field west of a minor version update to a data integration project. There is an SSIS Package here. Three paths diverge from your current location. The path to the north begins by manually re-applying the changes you just made to the SSIS package. The path to the south begins by manually re-applying the version 1.1 updates to the SSIS package you just updated and deployed to Production. The path east is marked with a sign that reads “MERGE CODE” but a hidden pit filled with hungry grues lies in that direction. </Zork>

    • You can start with your latest version of the SSIS package and manually re-apply the fix code.
    • You can start with a copy of the freshly-updated SSIS package (the one that had the bug previously) and manually re-apply the v1.1 updates.
    • I recommend you not try to branch and merge SSIS packages in source control.

    What’s Different with SSIS 2016?

    SSIS 2012 and 2014 required you to deploy the entire SSIS project to the Catalog in order to update a single SSIS package. SSIS 2016 allows developers to deploy only the impacted SSIS package. You can now check in the current version of your SSIS project, grab a Production version of the single SSIS package from source control, fix the bug, test it, check it it, and deploy only that package to Production – without impacting any of the other SSIS packages in your SSIS project.

    One thought before moving on:

    There are two types of developers: those who use source control and those who will. – Andy, circa 2015


    To demonstrate, I created a test SSIS solution in SQL Server Data Tools for Visual Studio 2015 (October release) in SSIS 2016 (SQL Server 2016 CTP3). I started with a single SSIS package named Child1.dtsx and deployed the project to the SSIS Catalog.

    I next added another package named Child2.dtsx. When I right-click Child2.dtsx in Solution Explorer, there’s a new (and awesome) context menu item – “Deploy Package”:


    As in the past, the integration Services Deployment Wizard starts:


    The Review page indicates we’re only deploying the Child2.dtsx package:


    I was curious about how single-package deployment impacts the SSIS Catalog’s notion of SSIS Project version history. I made a small change to the Child2.dtsx SSIS package and redeployed it alone, again. Viewing the SSIS Catalog History for the project, I see three versions. This matches my expectations:

    1. Initial deployment of the single-package solution;
    2. Deployment of Child2.dtsx package; and
    3. Re-deployment of the Child2.dtsx package.



    I like the new functionality – a lot! Kudos to the Microsoft Integration Services Development Team for this nifty new feature!


    I am here to help:
    Contact Andy!

    Learn more:
    Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 - 10, 2015

    Stairway to Integration Services
    Stairway to Biml

    SSIS Design Patterns

  • Biml 101 – A Free Webinar 18 Nov 1:00 PM EST


    If you’ve heard the buzz about Business Intelligence Markup Language (Biml) but never used it, you may be curious about how Biml works and how it can help reduce the amount of time required to develop SSIS packages. In this 100-level presentation, BimlHero Andy Leonard demonstrates how to use Biml to build an SSIS package.

    Register here.

    I hope to see you there!


  • The SYNCHRONIZED SSIS Execution Parameter

    I was reminded today (again) that I need to blog about the SYNCHRONIZED SSIS execution parameter. I mentioned it last week while presenting at the PASS Summit 2015 and made a mental note to blog soon. Earlier today, as I was discussing features of the SSIS Catalog Compare utility, the topic came up again.

    Credit where credit is due: I first learned about the SYNCHRONIZED SSIS execution parameter from Phil Brammer over at Phil wrote Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters way back in the summer of 2012. (Thank you, Phil!)

    Synchronous SSIS Package Execution

    I can hear you thinking, “What does the SYNCHRONIZED SSIS execution parameter do, Andy?” I’m glad you asked. When you right-click a package in the SSIS Catalog and then execute the package, the SSIS runtime grabs the XML, validates it, and executes it. If the runtime can find the package and (I believe) start the package execution, the caller will receive a “Success” from the SSIS runtime. The package may continue running for a long time afterwards. Worse, it may fail. You’ll have to review the logs to glean how long the package actually executed and whether it succeeded or failed.


    Flipping the Bit

    So, how does one set the SYNCHRONIZIED SSIS execution parameter? One way is to script the package execution by clicking the “Script” button on the Execute Package window, as shown here:


    Clicking the Script button produces at least three T-SQL statements – calls to stored procedures in the SSISDB database’s catalog schema. I reformatted the text and it’s shown here:


    Here’s a listing of the code you can copy and paste, edit, and use to execute SSIS packages on your server:

    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution]
    , @execution_id=@execution_id OUTPUT
    , @folder_name=N'FrameworkTestFolder'
    , @project_name=N'FrameworkRestartabilityTest'
    , @use32bitruntime=False
    , @reference_id=Null

    Select @execution_id

    DECLARE @var0 smallint = 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    , @object_type=50
    , @parameter_name=N'LOGGING_LEVEL'
    , @parameter_value=@var0

    EXEC [SSISDB].[catalog].[start_execution] @execution_id

    There are three steps in the T-SQL listed and shown above:

    1. Create an Intent to Execute – accomplished via the catalog.create_execution stored procedure, which returns an execution_id value.
    2. Configure the Intent to Execute – accomplished here by a single call to the catalog.set_execution_parameter_value stored procedure and used to configure the LOGGING_LEVEL SSIS execution parameter for this execution (1 = Basic).
    3. Execute – accomplished by the call to the catalog.start_execution stored procedure.

    If you execute these stored procedures an SSIS package may execute (I write “may” because the parameters have to be properly configured to find and execute an SSIS package on your server, and I cannot see your machine from this blog… yet). If I view the SSIS Catalog’s Overview report for this package’s last execution, I can see the execution parameters in the “Parameters Used” tablix as shown here:


    The SSIS execution parameters are named in upper case. For this reason, I do not name any of the other parameters in SSIS in all upper case. I want to be able to glance at this tablix and learn the values of the execution parameters (or the non-execution parameters). Note SYNCHORINZED is set to False.

    But if we add code to the Configure the Intent to Execute step, we can change the SYNCHRONIZED value to True:


    DECLARE @synchronized bit = 1

    EXEC [SSISDB].[catalog].[set_execution_parameter_value]
    , @object_type=50
    , @parameter_name=N'SYNCHRONIZED'
    , @parameter_value=@synchronized

    Executing the T-SQL now changes things, and we can view the changes in the Catalog Overview report:



    When SQLAgent starts an SSIS package using the Integration Services Package job step type, it automatically overrides the SYNCHRONIZED SSIS execution parameter, setting it to True. Incidentally, SQLAgent also supplies a value for the CALLER_INFO parameter.

    The SSIS Catalog provides many lesser-known and lesser-documented features to manage SSIS package execution. The SYNCHRONIZED execution parameter is but one cool nugget!


More Posts Next page »

This Blog



My Company

Community Awards

Friend of Red Gate

Contact Me


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement