THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

  • Announcing Data Driven–a New Data Science Podcast!

    AnnouncingDataDriven

    I am honored to announce Data Driven – a new Data Science podcast hosted by Frank La Vigne (blog | @Tableteer) and me!

    Visit our site and:

    IntroducingDataDriven

    To start off the podcast, Frank and I recorded an episode titled Introducing Data Driven where we discuss our motivations, goals, interests, and aspirations for the show. You can listen to that podcast here.

    Check us out on iTunes, subscribe to the DataDriven  YouTube channel, follow us @DataDrivenTv (Twitter), @DataDrivenTV (Facebook), and LinkedIn!

    We hope you enjoy Introducing Data Driven and all the shows! We have awesome guests!

    :{>

    Learn More About Data Driven
    Data Driven website
    Episodes
    Blog
    Data Driver mailing list
    About the Show


    Follow Data Driven
    iTunes
    DataDriven  YouTube
    @DataDrivenTv (Twitter)
    @DataDrivenTV (Facebook)
    LinkedIn
    RSS Feed

  • Building Custom Tasks for SQL Server Integration Services is Available for Pre-Order

    ExecuteCatalogPackageTask

    My next book titled Building Custom Tasks for SQL Server Integration Services is available for pre-order! The current release date is scheduled for late July 2017.

    The Cost of Custom SSIS Tasks

    There is overhead associated with using custom tasks in SSIS. When I started writing, I believed there were three costs associated with custom SSIS task development:

    1. Developers need to use Visual Studio Professional
    2. The code must be deployed to every server running SSIS (at least every server with SSIS packages that use the custom SSIS task)
    3. The code must be maintained (updated and supported as new versions of SSIS are released)

    While items 2 and 3 in the list above remain true and are not trivial, I built a sample custom SSIS task (Execute Catalog Package Task) using Visual Studio Community Edition – which is free.

    What I Cover (and Don’t Cover)

    This book isn’t about the “innards” of building a custom SSIS task. I wrote this book to help people who have an idea for a custom SSIS task but aren’t experienced Visual Studio component developers. There are a handful of “gotchas” less-experienced developers will encounter... well, I encountered them. You don’t need to buy this book to discover the source of and correction for the errors you may encounter, you can cast about the internet using your favorite search engine and learn, like I did, the answers to most of the problems you are trying to solve.

    One reason I decided to write this book, though, is those answers are not as easy to find as one would think. The answers are, I believe, basic and trivial to developers with experience building Visual Studio components. They are so basic and trivial, I believe they fall into the category of tribal knowledge for developers experienced in building VS controls. When experienced developers see a question on Stack Overflow, some assume you’ve already tried the basic and trivial. Hence, the difficulty in locating a helpful response. This is not intended as a dig at Stack Overflow or the wonderful people who donate their time, knowledge, and expertise to helping the community. If anyone’s to blame, it’s me for not knowing enough about the problem I was trying to solve to search for the most helpful answers.

    I found all but one answer at Stack Overflow. I had to weed through more replies than usual, though.

    The one thing I couldn’t find dealt with getting the icon to show up on the custom SSIS task. I figured that one out by opening a sample from Microsoft and performing a manual compare until I found the property that they had changed and I had not.

    Conclusion

    Building Custom Tasks for SQL Server Integration Services is a short book focused on solving the stuff around coding an SSIS custom task. We don’t build a production-ready custom task in the example. I didn’t include that material because I find plenty of material online that discusses and demonstrates the “innards” of designing a production-ready custom SSIS task. If there’s enough interest I’ll write another book that builds out the example in this book (the Execute Catalog Package Task) and includes making it production-ready.

    :{>

    You might like working with Enterprise Data & Analytics because we grok building SSIS Custom Tasks.

    Learn More:

    From Zero to Biml - 19-22 Jun 2017, London 
    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Passively Mine SSIS Data Flow Rows Loaded From the SSIS Catalog

    CatalogAs I wrote in Parsing SSIS Catalog Messages for Lookup Performance, v2:

    I prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute queries similar to the query below and store the performance metrics in a table. I can (and do) build dashboards to track SSIS performance (perhaps I should blog about them…) using this passively-collected performance metrics.

    As with Lookup Transformation messages, OLE DB Destinations in Data Flow Tasks record specific messages using the same format every time. Because of this (hard-coded) consistency, you and I can passively collect the number of rows written while executing packages in the SSIS Catalog using the (default) Basic logging level. We can use the following Transact-SQL query to collect this execution metadata post-execution:

    Use SSISDB
    go

    declare @wroteString nvarchar(12) = '" wrote '
    declare @rowsString nvarchar(12) = ' rows.'
    declare @searchWroteString nvarchar(12) = '%' + @wroteString + '%'
    declare @searchRowsString nvarchar(12) = '%' + @rowsString + '%'
    declare @lenWroteString int = Len(@wroteString) + 1
    declare @lenRowsString int = Len(@rowsString) + 1

    Select
      msg.operation_id As OperationID
    , execution_path As ExecutionPath
    , Substring([message]
             
    , CharIndex('"'
                        , [message]) + 1
              , (CharIndex('"'
                         , [message]
                         , CharIndex('"'
                                   , [message]) + 1))
                           -
                          (CharIndex('"'
                                   , [message])) - 1) As OLEDBDestinationName
    , SubString(
        [message]
      , (PatIndex(@searchWroteString
                , [message])
        + @lenWroteString)
      , ((PatIndex(@searchRowsString
                 , [message]))
         -
         (PatIndex(@searchWroteString
                , [message])
         + @lenWroteString))
      ) As RowsLoaded
    From
        [catalog].[event_messages] msg
    Left Join [catalog].[extended_operation_info] info ON msg.extended_info_id = info.info_id
    Where message_source_type = 40
      And message_type = 70
      And [message] Like N'%" wrote %[0-9]% rows%'
    Order By msg.operation_id DESC

    Enjoy!

    You might like working with Enterprise Data & Analytics because we instrument ETL.

    Learn More:

    Parsing SSIS Catalog Messages for Lookup Performance, v2
    From Zero to Biml - 19-22 Jun 2017, London 
    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Presenting Use Biml to Automate SSIS Design Patterns at SQL Saturday Dublin 17 Jun!

    SQLSat620DublinLogoI am honored to present Use Biml to Automate SSIS Design Patterns at SQL Saturday 620 – Dublin 17 Jun!

    Abstract

    Perhaps you’ve heard the buzz about Business Intelligence Markup Language (Biml) and wondered, “Why is everyone so excited about Biml?” Occasionally, a new technology emerges that changes everything. For SSIS developers, Biml is one such technology. Business Intelligence Markup Language (Biml) is a powerful solution for rapidly creating SSIS packages and solutions. SSIS Design Patterns support various load profiles. Combined, Biml and SSIS Design Patterns offer a compelling solution for automating enterprise data integration.

    In this session, you will learn:
    - How to rapidly build multiple SSIS packages using Biml
    - How to apply different SSIS Design Patterns using Biml
    - How to automate SSIS Design Patterns using Biml

    You can register for SQL Saturday 620 – Dublin here. I hope to see you there!

    :{>

    You might like working with Enterprise Data & Analytics because we Biml all the live long day.

    Learn More:

    From Zero to Biml - 19-22 Jun 2017, London 
    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago
  • Presenting A Day of Intelligent Data Integration Precon at SQL Saturday Dublin!

    SQLSat620DublinLogoI am honored to present my day-long precon titled SSIS Design Patterns and Biml: A Day of Intelligent Data Integration at SQL Saturday 620 – Dublin 15 Jun 2017!

    Description

    What is Intelligent Data Integration? SSIS packages developed using tried and true design patterns, built to participate in a DevOps enterprise practicing DILM, produced using Biml and executed using an SSIS Framework.

    Attend a day of training focused on intelligent data integration delivered by an experienced SSIS consultant who has also led an enterprise team of several ETL developers during multiple projects that spanned 2.5 years. And delivered.

    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 the free utilities, BimlExpress and BimlOnline
    6. SSIS Design Patterns + Biml
      Putting the DILM (Data Integration Lifecycle Management) components 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.

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

    Register today. I hope to see you there!

    :{>

    You might like working with Enterprise Data & Analytics because enterprise SSIS Frameworks, baby.

    Learn More:

    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    From Zero to Biml - 19-22 Jun 2017, London 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • The Heart of SSIS Framework Community Edition-Parent.dtsx

    I’m writing about SSIS Framework Community Edition because lots of people are using this free, open-source utility to execute collections of SSIS packages in their enterprises. Earlier I wrote Schedule an SSIS Application with SSIS Framework Community Edition, a post that describes using SQL Server Agent to schedule the execution of an SSIS Application and Adding an SSIS Application to SSIS Framework Community Edition, a post describing how to add metadata to your SSIS Framework for SSIS Packages already deployed in your enterprise. I even included a free T-SQL script to help! (You are welcome.)

    This post is about the engine at the heart of SSIS Framework Community Edition – the Parent.dtsx SSIS package.

    The Little Engine That Could (and Still Can!)

    As I mentioned in an earlier post, there are a handful of large enterprises (still) running early versions of my Framework – versions that are not integrated with the SSIS Catalog (because the SSIS Catalog didn’t exist back then) – and with less functionality.

    Here’s a composite screenshot of the SSIS Framework Community Edition Parent.dtsx package:

    Par0

    This is it, folks. That’s all there is. There ain’t no more! Parent.dtsx consists of a Package Parameter named ApplicationName, a package-scoped ADO.Net Connection Manager, two Execute SQL Tasks, a Script Task, a Sequence Container, a Foreach Loop Container, and a handful of SSIS Variables.

    “How Does it Work, Andy?”

    I’m glad you asked! The Execute SQL Task named SQL Get Framework Packages executes the following T-SQL query against the SSISDB (SSIS Catalog) database:

    Select p.PackageName
    , p.ProjectName
    , p.FolderName
    , ap.FailApplicationOnPackageFailure
    From custom.ApplicationPackages ap
    Join custom.Packages p
      On p.PackageID = ap.PackageID
    Join custom.Applications a
      On a.ApplicationID = ap.ApplicationID
    Where a.ApplicationName = @ApplicationName
    Order By ap.ExecutionOrder

    If we execute this query in SQL Server Management Studio (SSMS) for the “Framework Test” SSIS Application, we see the following results returned:

    Par1a

    Returning to the SQL Get Framework Packages Execute SQL Task in Parent.dtsx, the @ApplicationName parameter is mapped to the $Package::ApplicationName SSIS Package Parameter:

    Par1

    The Full Result Set is sent to the SSIS Variable (Object data type) named User::ApplicationPackages:

    Par2

    The FOREACH Application Package Foreach Loop Container uses a Foreach ADO Enumerator to shred the contents of the User::ApplicationPackages SSIS Variable:

    Par3

    As the Foreach Loop Container enumerates, it points to each row – one at a time – in the first (and only) table in the Tables collection of the ADO.Net Dataset. Values from each column in the “current” row are read into SSIS Variables:

    Par4

    Comparing this image to the results of the query, the Foreach Loop Container is first read the value “Child1.dtsx” into the User::FrameworkPackage SSIS variable (Index 0). “FrameworkTest1” is read into User::FrameworkProject (Index 1), “Test” is read into User::FrameworkFolder (Index 2), and 1 is read into User::FailApplicationOnPackageFailure (Index 3).

    Execution

    An Execute SQL Task named SQL Execute Child Package is the first executable inside the Foreach Loop Container:

    Par6

    The SQL Execute Child Package Execute SQL Task executes a stored procedure in the SSISDB (SSIS Catalog) database named custom.execute_catalog_package:

    Par7

    If you use an ADO.Net connection type, you can set the IsQueryStoredProcedure property to True. This removes the need to call the stored procedure with Exec (or Execute). You also do not need to list the parameters after the name of the stored procedure, you simply add them – by name – on the Parameters page.

    An Aside: “How I Did It” by Victor Andy Frankenstein

    Par12

    The custom.execute_catalog_package stored procedure is part of the open-source, free SSIS Framework Community Edition download available at DILM Suite. Here’s a screenshot of some of the T-SQL:

    Par5

    The custom.execute_catalog_package stored procedure was initially built from T-SQL generated when one scripts the execution of an SSIS Package in the SSIS Catalog. If you expand the SSMS Object Explorer’s Integration Services Catalogs node until you locate an SSIS Package, you can right-click the package and then click Execute:

    Par9

    When the Execute Package window displays, click the Script button to generate a collection of stored procedure calls:

    Par10

    The Script button opens a new query window (by default) and displays T-SQL scripts that you can execute to start the execution of an SSIS Package in the SSIS Catalog:

    Par11

    Does this code look familiar? It should. This T-SQL is the basis for the T-SQL in the custom.execute_catalog_package stored procedure.

    Back to the Fact I'm the Mack and I Know That (Not Really… I Just Wanted a Segue and an Excuse to Link to That Video…)

    The SQL Execute Child Package Execute SQL Task starts the SSIS Package by passing the values from the User::FrameworkPackage, User::FrameworkProject, and User::FrameworkFolder SSIS variables to the custom.execute_catalog_package

    Par8

    Remember, these SSIS variables were three of the four shredded from the User::ApplicationPackages (object data type) SSIS variable by the FOREACH ApplicationPackage Foreach Loop Container.

    “It’s Your Fault! No! It’s Your Fault!”

    The fourth SSIS variable is User::FailApplicationOnPackageFailure, which is not passed to the custom.execute_catalog_package stored procedure. Instead, FailApplicationOnPackageFailure is used if the custom.execute_catalog_package returns a failure result, which will happen if the SSIS Package fails execution. When that happens the Failure Precedence Constraint between the SQL Execute Child Package Execute SQL Task and the SCR Evaluate Package Failure setting Script Task evaluates as True, and the SCR Evaluate Package Failure setting Script Task executes:

    Par13

    SCR Evaluate Package Failure setting evaluates the FailApplicationOnPackageFailure SSIS Variable:

    Par15

    If FailApplicationOnPackageFailure is set (1, or True), SCR Evaluate Package Failure setting raises an error. If FailApplicationOnPackageFailure  is not set (0, or False), SCR Evaluate Package Failure setting executes successfully:

    Par14

    In this way, Parent.dtsx manages fault tolerance. I can hear you thinking, …

    “So How Does Parent.dtsx Continue Executing When There’s a ‘Tolerable’ Error, Andy?”

    You don’t miss much, I’ll give you that. First we have to stop the Foreach Loop Container from failing when an error occurs. We do that by setting the MaximumErrorCount property to 0. To prevent the package from failing, we need to duplicate this setting for the Sequence Container and for Parent.dtsx:

    ParMaxErrorCount

    So how do we get Parent.dtsx to fail when the FailApplicationOnPackageFailure is set? We set the FailPackageOnFailure property to True for the SCR Evaluate Package Failure setting Script Task:

    Par16

    When the SCR Evaluate Package Failure setting Script Task returns a failure result, Parent.dtsx fails. If the SSIS Package executed by the SQL Execute Child Package Execute SQL Task fails (returning a failure result to the Execute SQL Task) and FailApplicationOnPackageFailure is not set (False), SCR Evaluate Package Failure setting succeeds and doesn’t fail Parent.dtsx. So Parent.dtsx continues executing. Cool? Cool.

    Conclusion

    Parent.dtsx looks simple but, as we’ve learned in this post, this simple SSIS Package is an elegant solution for executing collections of SSIS Packages. Via Parent.dtsx, SSIS Framework Community Edition supports metadata-driven SSIS Package ordered execution and includes configurable package-level fault tolerance. Enterprise Data & Analytics builds SSIS Frameworks Commercial and Enterprise Editions, as well as custom SSIS Frameworks to support enterprise data integration. Contact us. We can help.

    :{>

    You might like working with Enterprise Data & Analytics because we think enterprise SSIS Frameworks are groovy.

    Learn More:

    Adding an SSIS Application to SSIS Framework Community Edition
    Schedule an SSIS Application with SSIS Framework Community Edition
    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    From Zero to Biml - 19-22 Jun 2017, London 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Adding an SSIS Application to SSIS Framework Community Edition

    I’m writing more about SSIS Framework Community Edition because lots of people are using this free, open-source utility to execute collections of SSIS packages – called SSIS Applications – in their enterprises. Earlier I wrote Schedule an SSIS Application with SSIS Framework Community Edition, a post that describes using SQL Server Agent to schedule the execution of an SSIS Application. In this post I’m going to cover adding SSIS Application metadata to SSIS Framework Community Edition. But first, I can hear some of you thinking, …

    “What is an SSIS Application, Andy?”

    I’m glad you asked! An SSIS Application is a collection of SSIS packages configured to execute in some order. Do you need an SSIS Framework to execute SSIS packages in some order? Nope. There are lots of ways to accomplish SSIS package execution management. Some of those ways, though, are the data integration equivalent of storing your enterprise data in Excel. ‘Nuff said.

    The SSIS Framework uses three tables to organize SSIS Packages into SSIS Applications:

    1. custom.Applications
    2. custom.Packages
    3. custom.ApplicationPackages

    The design of the custom.Applications table is fairly straightforward with two columns, ApplicationID and ApplicationName:

    FWApp0

    The design of the custom.Packages table is also pretty straightforward with SSIS Catalog Package storage metadata columns:

    FwApp1

    A Cardinal Rule

    Let’s consider cardinality for a moment. If an SSIS Application is a collection of SSIS Packages, then the cardinality between applications and packages is one-to-many. But… one of the reasons for using an SSIS Framework is the SSIS Execute Package Task requires SSIS Packages exist in the SSIS Project (for SSIS Catalog execution using Project Deployment Model). Are there ways around this? Sure (please see my earlier comment about storing enterprise data in Excel…).

    Wouldn’t it be awesome if you could store SSIS packages anywhere in the SSIS Catalog and then re-use them as part of several SSIS Applications? Yes. Yes it would. This is one advantage of using an SSIS Framework; it facilitates code re-use. Imagine you have an SSIS Package named ArchiveFile.dtsx that, well, archives flat files once their data has been loaded. You could add that package to every SSIS Project. If you go that route, what happens if you want to update ArchiveFile.dtsx? Or, God forbid, what do you do if you find a bug in ArchiveFile.dtsx? I reckon you open all those SSIS Projects, make the change, update source control, and then redeploy ArchiveFile.dtsx to all those SSIS Catalog projects (… and remember, you’d have to redeploy all packages in the projects prior to SSIS 2016…). Right?

    Yea… no. Instead, you find (or build) a better mousetrap: an SSIS Framework.

    Considering cardinality in a framework that facilitates code re-use, you realize the possibility that some SSIS Packages can be part of many SSIS Applications. The cardinality between SSIS Applications and SSIS Packages is really many-to-many. A bridge, or resolver, table is needed to manage the many-to-many relationship and I call this table custom.ApplicationPackages:

    FWApp2

    Each record represents an SSIS Package that runs as part of an SSIS Application. ApplicationPackageID is an identity column that manages this distinction. ApplicationID identifies the SSIS Application, PackageID identifies the SSIS Package, and ExecutionOrder defines the relative execution order of the SSIS Package in the SSIS Application. FailApplicationOnPackageFailure is an execution attribute. Technically ExecutionOrder is also an execution attribute. Why store this metadata at the ApplicationPackage? Let’s return to ArchiveFile.dtsx for a moment.

    What if my data integration process is made up of one SSIS Package that loads data from a CSV file? The first SSIS Package in my SSIS Application will be the loader. The second SSIS Package in my SSIS Application will be ArchiveFile.dtsx.

    But what if my data integration process contains several SSIS Packages? In that case, ArchiveFile.dtsx may execute 10th. Or 20th. Or even more than once. Remember, it’s the same SSIS Package – ArchiveFile.dtsx. It’s just being called many times.

    Why attribute FailApplicationOnPackageFailure to the ApplicationPackage? I may desire to continue SSIS Application execution if ArchiveFile.dtsx fails in SSIS Application A. But I may equally desire to stop the execution of SSIS Application B if ArchiveFile.dtsx fails. I hope this is making sense. Execution attributes are important.

    Adding Framework Metadata

    Adding SSIS Application metadata is straightforward because the design of the custom.Applications table is straightforward. When I write T-SQL for metadata operations, I like for the code to tell me what it’s doing. It’s awesome to know there was an error (well, not awesome exactly) or that the command completed successfully. But I’d like more information. Please. So I write T-SQL that is re-executable (idempotent). The T-SQL not only manages state, it informs me of state.

    Adding an SSIS Application can be accomplished using the following T-SQL statement:

    /* Application: Load AdventureWorks2014 Stage */
    print 'Application: Load AdventureWorks2014 Stage'
    declare @appID int = (Select ApplicationName
                          From custom.Applications
                          Where ApplicationName = 'Load AdventureWorks2014 Stage')

    If(@appID Is NULL)
    begin
      print ' - Creating Load AdventureWorks2014 Stage SSIS Application'
      Insert Into custom.Applications
       (ApplicationName)
      Output inserted.ApplicationID As ApplicationID
      Values
       ('Load AdventureWorks2014 Stage')
      print ' - Load AdventureWorks2014 Stage SSIS Application created'
    end
    Else
    begin
      print ' - Load AdventureWorks2014 Stage SSIS Application already exists.'
      Select @appID As ApplicationID
    end

    Why return the ApplicationID value? ApplicationID is one of the fields in ApplicationPackage.

    Adding SSIS Package Framework metadata is similar:

    /* Package: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx */
    print 'Package: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
    declare @pkgID int = (Select PackageName
                          From custom.Packages
                          Where PackageName = 'AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
                            And ProjectName = 'AdventureWorks2014_Stage_Loader'
                            And FolderName = 'Stage')

    If(@pkgID Is NULL)
    begin
      print ' - Adding AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package'
      Insert Into custom.Packages
       (FolderName
       ,ProjectName
       ,PackageName)
      Output inserted.PackageID As PackageID
      Values
       ('Stage'
       , 'AdventureWorks2014_Stage_Loader'
       , 'AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx')
      print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package added'
    end
    Else
    begin
      print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx SSIS Package already exists.'
      Select @pkgID As PackageID
    end

    Again, PackageID is required for ApplicationPackage metadata, which is where we next turn our attention:

    /* ApplicationPackage: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx */
    print 'ApplicationPackage: AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx'
    declare @appPkgID int = (Select ApplicationPackageID
                          From custom.ApplicationPackages
                          Where ApplicationID = @appID
                            And PackageID = @pkgID
                            And ExecutionOrder = 10)

    If(@appPkgID Is NULL)
    begin
      print ' - Adding AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx Application Package'
      Insert Into custom.ApplicationPackages
       (ApplicationID
       ,PackageID
       ,ExecutionOrder
       ,FailApplicationOnPackageFailure)
      Output inserted.PackageID As PackageID
      Values
       (@appID
       , @pkgID
       , 10
       , 1)
      print ' - AdventureWorks2014_Stage_dbo_AWBuildVersion_TruncateAndLoad.dtsx Application Package added'
    end

    These scripts will add SSIS Framework metadata to create (or return existing values for) an SSIS Application, SSIS Package, and map the SSIS Package into the SSIS Application. I can hear you thinking, …

    “Great Andy. But What if I Already Have a Bunch of SSIS Packages Deployed to an SSIS Catalog?”

    The T-SQL above lends itself to automation so I automated it. The script provided below is not as robust as I’d like for Production purposes but it demonstrates the principles and, well, it works. I won’t go into detail describing each line of code. I’m sure this can be accomplished differently and I’m pretty sure it can be done better. Suffice it to say this T-SQL script loads SSIS Framework Community Edition metadata for an SSIS Project deployed to an SSIS Catalog. You set the name of the SSIS Application by setting the @frameworkApplicationName parameter. Supply the name of the SSIS Catalog Folder (@catalogFolderName) and Project (@catalogProjectName). Unless the order of execution doesn’t matter, you will likely have to update the ExecutionOrder values once the script completes.

    Please remember this T-SQL starts a transaction but does not commit it. Commit and Rollback statements are at the end of the script, commented out so you can determine which to execute based on the results of the test query.

    DISCLAIMER: This script will add SSIS Packages metadata to the SSIS Framework Community Edition in a non-deterministic order. Before executing the SSIS Application, please review the results returned by the final SELECT query in this script and update the ExecutionOrder values as needed to support your enterprise data integration requirements.

    Use SSISDB
    go

    declare @frameworkApplicationName varchar(255) = N'Load AdventureWorks2014 Stage'
    declare @catalogFolderName nvarchar(128) = N'Stage'
    declare @catalogProjectName nvarchar(128) = N'AdventureWorks2014_Stage_Loader'
    declare @packageName nvarchar(260)
    declare @sql nvarchar(4000)
    declare @CrLf char(2) = Char(13) + Char(10)
    declare @ApplicationID int
    declare @PackageID int
    declare @ExecutionOrder int = 10
    declare @ExecFlag bit = 1

    declare curPackages Cursor For
    Select p.[name] As PackageName
    From [catalog].packages p
    Join [catalog].projects pr
      On pr.project_id = p.project_id
    Join [catalog].folders f
      On f.folder_id = pr.folder_id
    Where pr.[name] = @catalogProjectName
      And f.[name] = @catalogFolderName
      And p.[name] Not Like '99_%'

    -- start a transaction
    begin tran

    -- build application
    Set @sql = '/* Application: ' + @frameworkApplicationName + ' */' + @CrLf
    Set @sql = @sql + 'declare @appID int = (Select ApplicationName
                          From custom.Applications
                          Where ApplicationName = ''' + @frameworkApplicationName + ''')' + @CrLf + @CrLf
    Set @sql = @sql + 'If(@appID Is NULL)
    begin
      Insert Into custom.Applications
       (ApplicationName)
      Output inserted.ApplicationID As ApplicationID
      Values
       (''' + @frameworkApplicationName + ''')
    end' + @CrLf
    Set @sql = @sql + 'Else
    begin
      Select @appID As ApplicationID
    end' + @CrLf + @CrLf

    print @sql

    If(@ExecFlag = 1)
    exec(@sql)

    Set @ApplicationID = (Select ApplicationID
                          From custom.Applications
                          Where ApplicationName = @frameworkApplicationName)

    open curPackages

    fetch next from curPackages into @packageName

    while(@@fetch_status = 0)
    begin
      Set @sql = '/* Package: ' + @packageName + ' */' + @CrLf
    Set @sql = @sql + 'declare @pkgID int = (Select PackageName
                          From custom.Packages
                          Where PackageName = ''' + @packageName + '''
                            And ProjectName = ''' + @catalogProjectName + '''
                            And FolderName = ''' + @catalogFolderName + ''')' + @CrLf + @CrLf
    Set @sql = @sql + 'If(@pkgID Is NULL)
    begin
      Insert Into custom.Packages
       (FolderName
       ,ProjectName
       ,PackageName)
      Output inserted.PackageID As PackageID
      Values
       (''' + @catalogFolderName + '''
       , ''' + @catalogProjectName + '''
       , ''' + @packageName + ''')
    end' + @CrLf
    Set @sql = @sql + 'Else
    begin
      Select @pkgID As PackageID
    end' + @CrLf + @CrLf

    print @sql

    If(@ExecFlag = 1)
    exec(@sql)

    Set @PackageID = (Select PackageID
                      From custom.Packages
                      Where PackageName = @packageName
                        And ProjectName = @catalogProjectName
                        And FolderName = @catalogFolderName)
      If(@PackageID Is Not NULL)
       begin
        Set @sql = '/* ApplicationPackage: ' + @packageName + ' */'
        Set @sql = @sql + 'declare @appPkgID int = (Select ApplicationPackageID
                          From custom.ApplicationPackages
                          Where ApplicationID = ' + Convert(varchar(12), @ApplicationID) + '
                            And PackageID = ' + Convert(varchar(12), @PackageID) + '
                            And ExecutionOrder = ' + Convert(varchar(12), @ExecutionOrder) + ')' + @CrLf + @CrLf
        Set @sql = @sql + 'If(@appPkgID Is NULL)
    begin
      Insert Into custom.ApplicationPackages
       (ApplicationID
       ,PackageID
       ,ExecutionOrder
       ,FailApplicationOnPackageFailure)
      Output inserted.PackageID As PackageID
      Values
       (' + Convert(varchar(12), @ApplicationID) + '
       , ' + Convert(varchar(12), @PackageID) + '
       , ' + Convert(varchar(12), @ExecutionOrder) + '
       , 1)
    end' + @CrLf

    print @sql

    If(@ExecFlag = 1)
    exec(@sql)

       end

      Set @ExecutionOrder = @ExecutionOrder + 10
      fetch next from curPackages into @packageName
    end

    close curPackages
    deallocate curPackages


    Select a.ApplicationName
         , p.FolderName
         , p.ProjectName
         , p.PackageName
         , ap.ExecutionOrder
         , ap.FailApplicationOnPackageFailure
    From custom.ApplicationPackages ap
    Join custom.Applications a
      On a.ApplicationID = ap.ApplicationID
    Join custom.Packages p
      On p.PackageID = ap.PackageID
    Where a.ApplicationName = @frameworkApplicationName
    Order By ap.ExecutionOrder

    -- commit
    -- rollback

    Please consider this a beta version of this T-SQL script for loading metadata for deployed SSIS Projects into SSIS Framework Community Edition. As always, I welcome your feedback.

    Enjoy!

    :{>

    You might like working with Enterprise Data & Analytics because we grok SSIS Frameworks.

    Learn More:

    Schedule an SSIS Application with SSIS Framework Community Edition
    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    From Zero to Biml - 19-22 Jun 2017, London 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Schedule an SSIS Application with SSIS Framework Community Edition

    ALyellowThe coolest thing about SSIS Framework Community Edition is it’s free. You can click that link to head over to the DILM Suite site where you can learn more and find a link to the source code, documentation, and sample SSIS projects on GitHub.

    I’m overwhelmed by the number of folks using this free utility. Believe it or not, there are a handful of large enterprises (still) running early versions of my Framework – versions that are not integrated with the SSIS Catalog (because the SSIS Catalog didn’t exist back then) – and with less functionality.

    Scheduling Execution

    This post is about scheduling execution of an SSIS Application using SQL Agent. In this post we will schedule the sample SSIS Application that ships with SSIS Framework Community Edition: the aptly-named “Framework Test” application that ships with the (open) source. You can view the SSIS Application metadata by querying the custom.Applications table:

    SchedFWa

    Log in an instance of SQL Server, expand SQL Server Agent, right-click Jobs, and click “New Job…”:

    SchedFW0

    Supply a Name when the New Job window displays:

    SchedFW1

    Click on the Steps page. Click the New button to create a new job step:

    SchedFW2

    Supply a Job Step Name. Select “SQL Server Integration Services Package as the Job Step Type. Enter a Server that hosts an SSIS Catalog and SSIS Framework Community Edition. Click the ellipsis beside the Package textbox (hidden in the image below) to display the “Select an SSIS Package” dialog. Expand the SSISDB node, Framework node, and select the Parent.dtsx SSIS package:

    SchedFW3

    Click the OK button. Your Job Step should appear similar to that shown below:

    SchedFW4

    Next click on the Configuration tab. Click the ellipsis beside the ApplicationName parameter Value:

    SchedFW5

    Set the Value property of the ApplicationName parameter to Framework Test:

    SchedFW6

    Click the OK button. The Value “Framework Test” will appear in bold as shown below to indicate the value has been configured:

    SchedFW7

    Click OK a couple times to close and save the SQL Agent Job.

    Let’s Test It!

    Right-click the job and click “Start Job at Step…”:

    SchedFW8

    If all is properly configured, you should see a successful job execution:

    SchedFW9

    Expand the SSMS Object Explorer “Integration Services Catalogs” node. Right-click SSISDB, hover over Reports, hover over Standard Reports, and click “All Executions”:

    SchedFW10

    You should see a successful execution of the Parent.dtsx package at the top of the All Executions report, similar to that shown here:

    SchedFW11

    Click the Overview link to view the Overview report. You should see the name of the SSIS Application – Framework Test – in the Parameters Used tablix, similar to that shown below (click to enlarge):

     

    SchedFW12

    Metadata

    SSIS Framework Community Edition is metadata-driven. A document titled "SSIS Framework Community Edition Documentation and User Guide" is included with the download. You can add metadata to create your own applications by following the example shown in the section named “Adding an Application to SSIS Framework Community Edition.” For a shortcut, check out the T-SQL script in the post titled Adding an SSIS Application to SSIS Framework Community Edition.

    Using metadata to drive execution of a collection of SSIS packages adds complexity to the setup process.

    The complexity is worth it – especially if you have lots of SSIS package to execute. But if you want, you can continue creating a job step for each and every SSIS package your enterprise executes. It’s completely and totally up to you.

    :{>

    You might like working with Enterprise Data & Analytics because we grok DevOps and SSIS, and we are here to help.

    Learn More:

    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    From Zero to Biml - 19-22 Jun 2017, London 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Honored to Present Use Biml as an SSIS Design Patterns Engine at SQL Saturday-NYC!

    I’m honored to present Use Biml as an SSIS Design Patterns Engine at SQL Saturday #588 in New York City 20 May 2017! I’ve been looking at the event schedule and… WOW! There are a lot of smart people presenting – plus me!

    I hope to see you there.

    :{>

  • Data Science, Unicorns, and Some Things That Never Change

    This post is a follow-up to Mind the Gap where I wrote about Δ, the difference between our perception of ourselves and other’s perception of us. In Mind the Gap I focused on personal and community ramifications of minimizing your Δ. In this post I write about applying these same concepts to data science, analytics, and IT.

    Before we proceed, please take 46 seconds and view this old IBM commercial. It’s a great spot that highlights some of the gaps between business and IT solutions. I have to credit Kent Bradshaw. We were discussing this post and he remembered this commercial which is a great example of a disconnect between business and IT. I can hear you thinking, …

    “How Does Δ Affect Data Science and Analytics, Andy?”

    Jen Underwood Unicorn ETL OLAP quote

    In her post titled Analytics Market Commoditization and Consolidation, analytics guru Jen Underwood (blog | Impact Analytix) shares the unicorn image at left – a reference to Lars Nielsen’s (excellent) book Unicorns Among Us – along with the quote:

    “The ‘unicorn’ the CEO can’t find is someone that knows old ETL and OLAP.”
    - Jen Underwood, in Analytics Market Commoditization and Consolidation [bold emphasis mine]

    Why Do ETL and OLAP Remain Important?

    Extract, Transform, and Load (ETL) and Online Analytical Processing (OLAP) skills remain important because garbage in stills leads to garbage out. We can now get aggregated and scaled garbage out – supported by stellar graphics engines executing on much faster GPUs. As I’ve shared in several presentations over the years, the data quality of a useless data warehouse often exceeds 99%. Why is the data in the data warehouse (or data lake, data store, data puddle, data closet) useless? Because losses accumulate, gains don’t. Aggregating the data for predictive analytics compounds the problem, amplifying minor variation in data quality into major Δ between expected and actual results.

    A Problem

    As Jen points out earlier in her Analytics Market Commoditization and Consolidation post (you should read it all – it’s awesome – like all of Jen’s posts!) many analytics solution providers share the “Same look, same marketing story, same saves time and allows users [to] avoid evil IT.”

    I can hear some of you thinking, “Are you telling us analytics doesn’t work, Andy?” Goodness no. I’m telling you hype and sales strategy work in the analytics market as well as anywhere. When asked why a solution may not perform to expectations, the #1 response is “your data is not clean.”

    NoOnesDataIsCleanQuote

    The longest pole in data science is still data integration: data wrangling, munging, etc. The need for high-quality data is never going away.

    A Solution

    The value of data science and analytics solutions rides on the quality of the data in the data store. There are no workarounds for garbage-in-garbage-out. So how do we improve the quality of the data?

    Automate

    As I wrote in Why Automate? automation can reduce redundant and repetitive work, thereby mitigating human error. At Enterprise Data & Analytics, we focus on data integration automation. How? Here’s our mission:

    AccomplishBetterDataIntegration

    We don’t help “users avoid evil IT,” we help IT better serve the enterprise by speeding up data integration development, managing execution, and improving performance.

    Contact us. We are here to help.

    :{>

    Learn More:

    Designing an SSIS Framework (recording)
    Biml in the Enterprise Data Integration Lifecycle (recording)
    From Zero to Biml - 19-22 Jun 2017, London 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

  • Free Webinar: Enterprise SSIS, Biml, and DILM

  • UPDATED: Use Amazon’s Alexa to Create a Copy of a Database Using Redgate SQL Clone…

    Please help: If you commented below please email me by clicking the Email link and sending me an email. Redgate wants to notify the winners!

    Thank you,
    Andy
     

     

    CMYK_SQL Clone col 1

    …and win an Amazon Echo Dot and one lucky winner will score a free SQL Clone license!

    About SQL Clone

    SQL Clone

    I can hear you thinking, “What’s SQL Clone, Andy?” I’m glad you asked!

    SQL Clone is a database provisioning tool that removes much of the time and disk space needed to create multiple database copies. With SQL Clone, database copies can be created in seconds and each copy uses around 40MB of disk space, no matter the size of the original database. Provisioning requests can be dealt with very quickly, and teams can work locally on up-to-date, isolated copies of the database to speed up development, accurately test code, and fix issues faster.

    About the Amazon Echo Dot

    I can hear you thinking, “So how do I win an Amazon Echo Dot, Andy?” I’m glad you asked!

    Amazon’s Alexa Voice Service is the engine behind the Amazon Echo, Tap, and Dot devices. The talented developers at Redgate built an Alexa Skill – a capability that enables interaction between people and devices and services – to facilitate voice-activated database cloning using SQL Clone. You can view a cool video of SQL Clone in action via Alexa here!

    “How Do I Enter to Win?”

    To enter the competition, post a comment stating what “skill” you would build with an Echo Dot (if time and money were no object). Maybe you’d like to know when a backup fails or how many deployments have been made to a database. Or maybe you would like to a reminder to record your favorite TV show! It doesn’t necessarily have to be database-related.

    The contest ends 30 April. Winners will be announced 5 May on the Redgate blog. Please check the Redgate blog on May 5 to see if you've won.  See the full contest terms and conditions are here.

    So have at it! Leave a comment to enter to win a free Amazon Echo Dot. Redgate is giving away five Dots! And one lucky winner will receive a SQL Clone license!

    :{>

    PS – Learn how Redgate created an Alexa skill for SQL Clone.

  • The Recording for Designing an SSIS Framework is Now Available

    Kent Bradshaw and I had a great crowd, lots of awesome questions, and delivered good demonstrations at the Designing an SSIS Framework webinar. You can now view the webinar for free (although registration is required) here.

    In the webinar Kent and I discussed the following Data Integration Lifecycle Management Suite products:

    While Kent was covering the functionality of the SSIS Framework Community Edition he mentioned several features of the SSIS Framework Commercial and Enterprise Editions. If you’re interested in learning more about the Commercial and Enterprise editions, please contact us! You can contact us through either the DILM Suite contact page or the Enterprise Data & Analytics contact page. Kent an I will be honored to speak with you and your team about how we implement SSIS Frameworks!

    :{>

  • Presenting Intelligent Data Integration Precon at SQL Saturday–Louisville!

    I am honored to present Intelligent Data Integration – a day-long precon at SQL Saturday #643 in Louisville Kentucky 4 Aug 2017!

    Synopsis:

    What is Intelligent Data Integration? SSIS packages developed using tried and true design patterns, built to participate in a DevOps enterprise practicing DILM, produced using Biml and executed using an SSIS Framework.

    Attend a day of training focused on intelligent data integration delivered by an experienced SSIS consultant who has also led an enterprise team of several ETL developers during multiple projects that spanned 2.5 years. And delivered.

    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
    2. Executing SSIS in the Enterprise
    3. Custom SSIS Execution Frameworks
    4. DevOps and SSIS
    5. Biml, Biml Frameworks, and Tools

    I hope to see you there.

    Register today!

    :{>

  • Mind the Gap

    A couple months ago my friend – and for many years and in many ways, mentor – Andy Warren (blog | @sqlandy) wrote a blog post that has been slow-cooking in my soul. The title is The Ebb & Flow of Community Participation. Basting this slow-cooking are the collected works of K. Brian Kelley (blog | @kbriankelley) at his blog, The Goal Keeping DBA – especially those articles tagged Professional Development.

    Andy’s thoughts strike me – especially his thoughts about the ebb of community participation. In particular, his final couple paragraphs that are aimed at leaders. Brian’s new year (2016) post titled Be Intentional in Your Efforts strikes a similar chord. I won’t cloud either Andy’s or Brian’s words with my interpretations. I’m just letting you know they inspired this post – the men and their words.

    Δ

    Δ is the symbol for the Greek letter we call delta. In science and engineering, Δ represents change or changing or the difference between two or more values. Change is constant:

    Everything changes and nothing stands still. 
    – Heraclitus, quoted by Plato in the Cratylus dialogue

    Growth is change and death is arguably an ultimate change. As Andy and Brian note: change is inevitable.

    Managing Δ

    So, how does one respond to change? Or, taking a step back, should one even try to manage change? Grossly oversimplifying, I see three options in responding to change:

    1. Fight it
    2. Ride it
    3. Create it

    Pick Your Battles

    I’m sometimes told (and have been told by both Andy and Brian) that I should “pick my battles.” I almost always respond, “I did. I chose this battle.” (If people realized how many battles I don’t choose… let’s just say it’s more than I choose…) I believe my friends only share this advice when they disapprove of a battle I’ve selected, but that’s human nature (and friendship in action). “This isn’t a hill worth dying on,” is a variation. It may be true; this hill may not be worth taking. Some hills are strategic now, others later, and some are never worth taking. Although it’s best to have a plan, a plan isn’t always necessary – and no plan survives contact with… well, anyone or anything. Even when you choose to fight change there’s some portion, some element, of riding the change or creating new change involved.

    Surfing

    Another option is to roll with the changes. Sometimes this involves recognizing the difference between the things one can change and things one cannot. Sometimes this involves sitting down, knowing your role, and shutting your mouth. Going with the flow is sometimes the path to peace, and peacemakers will be called sons of God. Other times, well…

    "There are times when silence is golden, other times it is just plain yellow."
    - Edwin Louis Cole in Maximized Manhood

    Be the Change

    A third option is to lead the change. Leading change often begins as riding or fighting change. Being the change is never easy. Some people will resist and some resistance will escalate to malice; more on this in a bit… It’s important to persevere or be stubborn; the adjective chosen will depend on whether one agrees with the change you’re leading or disagrees (so don’t take either adjective personally).

    “So How Do I Know When to Fight, When to Ride, and When to Be the Change, Andy?”

    That’s an excellent question. The answer is, “Very often, you don’t know.” You have to make the best call you can at the time. You have to pick a way to be and then be that way. Can you change your mind? Goodness yes. And if you do change your mind and realize that you were wrong, you should do everything in your power to correct the results of your words and actions. Start by apologizing. Is this easy? Nope. But it’s the right to do.

    Will some misinterpret and / or intentionally misrepresent what you say, write, and do? Also a big ol’ yes. What then? Again, it’s a decision to fight / ride / create. I’m a big believer in “time will tell.” I once heard Rush Limbaugh listen to a caller ramble on for a long time, insulting him and his recently-(at-the-time-)deceased father. After the caller ran out of things to say Rush said, “If a caller is determined to make an [fool] out of himself, get out of the way.”

    I do that too. The best mechanism is to just ignore folks while they continue doing what they do.

    Applied to Community…

    MindTheGapWe all live with some self-awareness delta. In the case of community there is always some difference between what we believe about ourselves and what others think of us. That’s normal. Like every other thing in life that is normal, it can be stretched beyond the point of usefulness and become abnormal. Managing the delta means minding the gap; for me it means minimizing the difference between what I think of myself and what others think of me. As I admitted to an admirer (recorded in Andy-Frickin-Leonard), “I’ve been trapped in here with me for several decades and I am not impressed.” For the remainder of that post I confess weaknesses and struggles with pride and politics. Looking back, I wrote that at a time while stuff was beginning that would end with me walking away from a company I co-founded. Interesting timing…

    Perhaps admitting weakness appears weak. I don’t think so and some smart people agree with me – like Dr. Brené Brown in her TED Talk titled The Power of Vulnerability. Almost 30 million people have viewed Dr. Brown’s TED talk. It’s just over 20 minutes – invest in yourself some and give it a listen.

    In Sum…

    To wrap up this ramble-y post, Andy, Brian, and Heraclitus are right. Change happens. Our response to change, though? That’s up to us. I conclude with advice – which I strive to follow – from the Apostle Paul:

    For by the grace given to me I say to everyone among you not to think of himself more highly than he ought to think, but to think with sober judgment, each according to the measure of faith that God has assigned.” 
    – Paul in Romans 12:3

    Yall be good. Especially to each other.

    :{>

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement