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 a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, 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 the Stairway to Integration Services.

  • SSIS Catalog Compare v2.0 Launch Event 7 Mar 2017!

    The Data Integration Lifecycle Management Suite (DILM Suite) helps SSIS Developers and Database Administrators manage SQL Server Integration Services (SSIS) deployments in the enterprise lifecycle.

    Join Kent Bradshaw and me at 1:00 PM EST 7 Mar 2017 for the SSIS Catalog Compare v2.0 Launch Event! In this free webinar we will demonstrate the value of using the DILM Suite to practice Data Integration Lifecycle Management. Join us and learn how to :

    • “Walk” an SSIS project though several lifecycle tiers
    • Detect differences between SSIS projects deployed to Production and QA lifecycle tiers
    • Automate SSIS comparisons, scripting, and deployment between SSIS Catalogs
    • Detect broken references in the SSIS Catalog

    What is the DILM Suite?

    Visit the DILM Suite website for a full list of the (mostly free!) tools! I can hear you thinking, “What does mostly free mean, Andy?” I’m glad you asked.

    SSIS Catalog Browser, shown below, is free and surfaces a full view of SSIS Catalog objects in a single view:

    CatC_2

    The SSIS Framework Community Edition integrated into the SSIS Catalog to provided extended SSIS execution functionality. It’s not only free, it’s open source!

    CatC_4

    SSIS Framework Browser (… you guessed it, also free!) supports the SSIS Framework Community Edition by surfacing Framework Applications and Packages.

    CatC_3

    SSIS Catalog Compare is an application that helps DBAs and SSIS Developers support and maintain SSIS in the DevOps enterprise. Compare, script, and deploy SSIS Catalog objects between SSIS Catalogs with alacrity. SSIS Catalog Compare delivers value because the utility helps DBAs and SSIS Developers efficiently manage enterprise SSIS deployments and configurations.

    CatC_1

    CatCompare is the command-line interface for SSIS Catalog Compare. DBAs and SSIS Developers use CatCompare to automate SSIS Catalog comparisons, deployments, and  synchronization.

    CatC_0

    In the DILM Tools webinar, Kent Bradshaw and I will demonstrate using these tools to improve operational efficiency and automate SSIS management.

    Register today!

    :{>

    I was honored to be interviewed by Carlos Chacon (@CarlosLChacon | SQL Data Partners) and Steve Stedman (@SqlEmt | Blog) for the SQL Data Partners podcast. We had a great discussion about the SSIS Catalog. We talked about how the SSIS Catalog works, Data Integration Lifecycle Management (DILM), SSIS Catalog Browser, SSIS Catalog Compare, and the Richmond SQL Server Community.

    If you’ve never listened to the SQL Data Partners podcast, it’s awesome! My favorite part is the SQL Family questions at the end!

    Enjoy!

    :{>

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

    Learn More:
    Broken References in the SSIS Catalog
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration
    SSIS Catalog Management
    SSIS Lifecycle Management

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • SQL Data Partners Podcast Episode 79: SSIS Catalog

    I was honored to be interviewed by Carlos Chacon (@CarlosLChacon | SQL Data Partners) and Steve Stedman (@SqlEmt | Blog) for the SQL Data Partners podcast. We had a great discussion about the SSIS Catalog. We talked about how the SSIS Catalog works, Data Integration Lifecycle Management (DILM), SSIS Catalog Browser, SSIS Catalog Compare, and the Richmond SQL Server Community.

    If you’ve never listened to the SQL Data Partners podcast, it’s awesome! My favorite part is the SQL Family questions at the end!

    Enjoy!

    :{>

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

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration
    SSIS Catalog Management
    SSIS Lifecycle Management

    Referenced Posts:
    Deploying SSIS Projects to a Restored SSIS Catalog (SSISDB)
    Stairway to Integration Services
    DILM Suite
    WideWorldImporters: The new SQL Server sample database

    Related Training:
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 3

    In this post I will demonstrate how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

    There three sources of SSIS parameter values:

    1. Design-time defaults – these are the parameter values developers use when building the SSIS project and packages.
    2. SSIS Catalog Literal overrides – as part of the previous post in this series, An Example of Data Integration Lifecycle Management with SSIS, Part 2, I demonstrated overriding the value of a string parameter using an SSIS Catalog Literal override.
    3. SSIS Catalog Reference Mapping overrides – we will focus on using Parameters, Environments, References, and Reference Mappings to override parameter values in this post.

    SSIS Parameters, Catalog Environments, and References

    At the end of 2016, Kent Bradshaw and I delivered a webinar that covers this topic. It’s called SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration and you can access it for free, although registration is required. In that webinar, Kent and I talked about logical and physical models for SSIS parameters, Catalog environments, and references.

    Let’s begin by looking at parameters.

    SSIS Parameters

    SSIS parameters are available only for SSIS projects developed using the Project Deployment Model (the default project deployment model in SSIS 2012, 2014, and 2016). Parameters may be scoped at the package or project. The image below presents a logical diagram of package and project parameters in an SSIS Project deployed to the SSIS Catalog:

    image

    SSIS Catalog Environments and Catalog Environment Variables

    Unlike SSIS Parameters, which are configured at design time when developing SSIS packages and projects in SQL Server Data Tools (SSDT), SSIS Catalog Environments are creatures of the SSIS Catalog.

    A Catalog Environment is a collection of zero-to-many Catalog Environment Variables. The variables contain Name, Data Type, Description, Value, and Sensitive attributes, as we’ll see in a bit. Although it’s technically possible to create an SSIS Catalog Environment that contains no Catalog Environment Variables, I cannot think of a use case for doing so. Catalog Environments are pretty useless without at least one Catalog Environment Variable. The image below presents a logical diagram of an SSIS Catalog Environment that contains one Catalog Environment Variable:

    LogicalSSISCatalogEnvironment

    References

    A Reference is a “link” between a single Catalog Environment and a single SSIS Project or Package. The image below presents a logical diagram of Reference between a Catalog Environment and a Catalog Project:

    LogicalSSISCatalogReference

    Reference Mappings

    A Reference contains zero-to-many Reference Mappings.

    One way to describe Reference Mappings: Think of a Reference, then drill down one level at the Catalog Environment to a single Catalog Environment Variable and one level at the SSIS Project (or Package) to a single Parameter. The image below presents a logical diagram of Reference Mapping between a Catalog Environment Variable (contained in a Catalog Environment) and a Project Parameter (contained in a Catalog Project):

    image

    Summarizing So Far…

    These four objects reside solely in the SSIS Catalog:

    • Catalog Environments
    • Catalog Environment Variables
    • References
    • Reference Mappings

    These four objects are related in the following ways:

    • A Catalog Environment contains zero-to-many Catalog Environment Variables.
    • A Reference may be configured between a single Catalog Environment and a single Project (or Package).
    • A Reference Mapping may be configured between a single Catalog Environment Variable and a single Project (or Package) Parameter.

    You may be thinking, “This is awfully complex, Andy. Why would anyone use Environments and References?”

    Why This Complexity?

    My argument for embracing (or ignoring) the complexity of SSIS Catalog configuration is straightforward: Externalization – the act of storing execution-time values outside of the object to be executed – is one way to achieve portability, a software development best practice.

    As an architect I loathe complexity. Whenever I encounter (or opt to develop) complexity in a software project, I question it. I ask others to question it. I don’t always arrive at the right conclusion, but I challenge complexity at every turn.

    I’ve helped several organizations implement SSIS Catalog portability using Environments and References. We all agree that the solution is complex, but most also agree that the complexity is worth the flexibility achieved thereby.

    Implementing Catalog Environments and References

    If you don’t have the demo project, you can download it here and follow the instructions in this post to deploy it.

    To implement using our demo project, open SQL Server Management Studio (SSMS) and connect to the instance of SQL Server that hosts the Catalog containing our demo project. In SSMS Object Explorer, expand the Integration Services Catalogs node, then drill down to our SSIS Project Folder. Right-click the Environments virtual folder and click “Create Environment” as shown:

    dilm_6

    When the Create Environment window displays, supply an Environment Name (I named mine “envMedicalData”) and optional Environment Description, and then click the OK button:

    dilm_7

    The newly-created Catalog Environment will appear beneath the Environments virtual folder. Right-click the Catalog Environment and click Properties (or simply double-click the Catalog Environment) to open the Environment Properties editor:

    dilm_8

    Click the Variables page and begin configuring a new Environment Variable by typing in the Name cell. I configured my Catalog Environment Variable thus:

    • Name: “MedicalDatabaseConnectionString”
    • Type: String
    • Description: (blank)
    • Value: An OLE DB connection string aimed at the SQL Server instance for my Medical database.

    You may recall we overrode this value in the previous post using a Literal override (click the image to enlarge):

    dilm_9

    Click the OK button to save the Environment Properties. The Catalog Environment envMedicalData is now created and configured with a single Catalog Environment Variable named MedicalDatabaseConnectionString.

    Remember, a Reference is a “link” between a Catalog Environment and a Project (or Package). We will now configure a Reference between our demo project and envMedicalData.

    Right-click the Project and click Configure, as shown:

    dilm_10

    When the Configure <Project> window displays, click the References page. Click the Add button to create a Reference between our project and a Catalog Environment:

    dilm_11

    The Browse Environments dialog displays. References to a Catalog Environment can either be Relative or Absolute. A Relative reference points to a Catalog Environment that exists in the same Catalog Folder as the SSIS Project. An Absolute references points to a Catalog Environment that resides in any Catalog Folder other than the Catalog Folder that contains the SSIS Project. Since we created the envMedicalData Catalog Environment in the same Catalog Folder (Demo) as our project, we can create either a Relative or Absolute reference to envMedicalData. And it doesn’t matter which we create, they both work the same. The image below shows me configuring a Relative reference:

    dilm_12

    After selecting the Catalog Environment, click the OK button to return to the Configure window. The Environment Name and Folder are now displayed on the References page (“.” indicates “local” or a relative reference):

    dilm_13

    Click on the Parameters page and click the ellipsis beside the MedicalDatabaseConnectionString parameter:

    dilm_28

    When the Set Parameter Value window displays, select the “Use environment variable” option and select the MedicalDatabaseConnectionString Catalog Environment Variable from the dropdown:

    dilm_15

    Click the OK button to save and close the Set Parameter Value dialog. The MedicalDatabaseConnectionString Project Parameter value property is now mapped, via the reference, to the MedicalDatabaseConnectionString Catalog Environment Variable. The Configure window displays Reference Mappings by supplying the name of the Catalog Environment Variable, underlined, in the Value cell:

    dilm_16

    Click the OK button to complete the Configuration of our Project.

    Test Execution

    In SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring we walked through executing an SSIS Package in the Catalog, and monitoring that execution. That’s a good test exercise because it will reinforce what you learned last time. If you’re playing along at home, execute the SSIS Package named ProviderGetFile.dtsx.

    When we right-click the ProviderGetFile.dtsx package and click “Execute,” we see something different (click the image to enlarge it). We cannot click the OK button until we resolve the error:

    The parameter “MedicalDatabaseConnectionString” is configured to use an environment variable, but no environment has been selected.  Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.

    We can remedy this by checking the Environment checkbox and selecting a Reference from the dropdown:

     dilm_31a

    When the Environment checkbox is checked, the Reference dropdown is enabled. Since we configured one reference, we have only one option, the “.\envMedicalData” reference:

    dilm_32

    Once the reference is configured, the OK button is enabled. We can execute the SSIS Package. We may view the Overview Report – either the version built into SSMS or the Catalog Reports version from DILM Suite. I’m going to use Catalog Reports because, well, I wrote it! :)

    dilm_33

    We see the package is running at the time of this screenshot. Click the Overview link to view the Execution Parameters:

    dilm_34

    The value supplied to the execution of ProviderGetFile.dtsx’s MedicalDatabaseConnectionString package parameter came from the MedicalDatabaseConnectionString  Catalog Environment Variable of the same name (MedicalDatabaseConnectionString). How can we be sure? Update the value so that the connection remains the same but the connection string value is different. All I’ve changed here is how I reach the Data Source; I’ve updated “vmSql16” to “(local)”:

    dilm_35

    Re-executing and viewing the Overview Report confirms the MedicalDataConnectionString parameter value is now managed outside the SSIS package in the Catalog Environment Variable named MedicalDataConnectionString:

    dilm_36

    And here we have the updated value from this later execution.

    One More Thing…

    I can hear you thinking, “Andy, we sure did a lot of clicking to view those values and configurations in the SSIS Catalog. Is there an easier way to see what’s configured?” Yes. Yes, there is an easier way to view SSIS Catalog configurations metadata. Better yet, it’s free!

    It’s called SSIS Catalog Browser (beta) and, like Catalog Reports, is part of the DILM Suite . In the image below, please note you can view:

    • The Catalog Environment Variable, data type, and value (the lower circle),
    • The (Project) Reference and Reference Mapping (middle circle); and
    • The (Project) Parameter Override (upper circle).

    The Catalog configuration is surfaced in a single view. You do not have to open additional windows or dialogs to get a complete picture of Project and Package configuration in the SSIS Catalog.

    dilm_37

    As I mentioned earlier, SSIS Catalog Browser (beta) is free!

    Conclusion

    In this post I demonstrated how to use SSIS Catalog Environments, References,  and Reference Mappings to override SSIS parameter values at execution time.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping teams learn more about the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1
    An Example of Data Integration Lifecycle Management with SSIS, Part 2

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 2

    In this post I will demonstrate how to use the SSIS Catalog to execute and monitor an SSIS project.

    Manual SSIS Package Execution in the SSIS Catalog

    To execute an SSIS Package manually using the SSIS Catalog, connect to the SQL Server instance that hosts the Catalog using SQL Server Management Studio (SSMS). Once connected, open Object Explorer and drill into the Integration Services Catalogs node until you locate the SSIS package you desire to execute. I’m going to execute the ProviderGetFile.dtsx package. Right-click the package and click Execute (click the image to enlarge it):

    dilm_17

    The Execute package window displays. For this SSIS project, please pay particular attention to the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_18

    If you downloaded the demo project, it includes a SQL Server 2016 backup of a database named Medical. Restore the Medical database to a test instance of SQL Server 2016 (or higher) before proceeding. Then follow these steps to update the MedicalDatabaseConnectionString parameter value.

    1. Click the ellipsis to the right of the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_19

    In the Edit Literal Value dialog, edit the connection string in the Value property. Set the Data Source portion of the connection string to the SQL Server instance to which you restored the Medical database (click the image to enlarge it):

    dilm_20

    Click the OK button and your Execute Package window should now reflect an overridden value for the MedicalDatabaseConnectionString parameter value (click the image to enlarge it):

    dilm_21

    Values that are overridden with Literals are shown in bold font.

    Monitoring Using the SSIS Catalog Reports Solution in SSMS

    Click the OK button to begin executing the SSIS package. You will be prompted with a dialog similar to that shown below. Click the Yes button to view the Overview report:

    dilm_22

    The Overview report contains information about this execution of this SSIS package (click the image to enlarge it):

    dilm_23

    The ProviderGetFile.dtsx package takes some time to execute. On my virtual machine, it took about 5.5 minutes (click the image to enlarge it):

    dilm_24

    Monitoring Using the Catalog Reports Solution from DILMSuite

    I really like the SSIS Catalog Reports that are built into SSMS. They are informative and intuitive. I have one complaint, though, and that is that the reports are built into SSMS. Why is that an issue? Some people in the enterprise have a legitimate need to view these reports. As you can see by viewing the screenshots above, the Overview Report can provide operational and historical data regarding the execution of an SSIS package. This information is vital for enterprise data integration.

    But SSMS contains other functionality that is, frankly, not vital for many consumers of the Overview Report. The additional nodes highlighted in the screenshot below, for example, are unnecessary for people in non-database-savvy roles within an enterprise.

    One may argue – fairly, even – that enterprise security can and should manage access in such a way that users who access the Overview Report do not have access to the highlighted nodes in the following screenshot. We are in violent agreement. But… must we surface these nodes and their accompanying functionality to users who only need to view the Overview Report?

    dilm_26

    One of the first Data Integration Lifecycle Management (DILM) Suite solutions I built was Catalog Reports. Catalog Reports is a relatively simple and straightforward version of some of the SSIS Catalog Reports embedded in SSMS. The main difference is Catalog Reports is a SQL Server Reporting Services (SSRS) solution.

    It’s free.

    And it’s open source. Here’s a screenshot of the Overview Report for the same execution above (click the image to enlarge it):

    dilm_27

    As you can see by mere observation, the reports are not identical. Most of the information included in the SSMS-version of the Overview Report is, however, present in the DILM Suite version. Plus it’s open source, so if you’d like for the report to surface more data or appear differently, you have the source.

    Best of all, the DILM Suite version provides no additional access to SQL Server functionality. Must you still manage and maintain SQL Server security? That’s a silly question and the answer is, “Of course!” DILM Suite’s Catalog Reports helps by surfacing only data and functionality required for enterprise data integration reporting.

    Conclusion

    In this post I demonstrated how to use the SSIS Catalog to execute and monitor an SSIS project.

    :{>

    You might like working with Enterprise Data & Analytics because we like helping people understand the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0
    An Example of Data Integration Lifecycle Management with SSIS, Part 1

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • Introducing astreams

    astreams_0

    For months, I’ve received pings about a screenshot I posted on social media a year or so ago.

    Plus I needed to wind down. You real runners will know what I mean: You know how you feel after you’ve covered some distance (for you)? You need to walk around for a bit, you can’t just stop cold. You need to wind down. I needed that after the effort to release SSIS Catalog Compare v2.

    astreams became the coding-equivalent of a wind down.

    I’d been using another application that kind-of-sort-of did what astreams does but astreams is cleaner, in my opinion.

    So, how does one use astreams? Open it. It’s empty:

    astreams_1

    Right-click in the treeview and click Add Stream to begin adding a new stream:

    astreams_2

    Type the name of the new stream:

    streams_14

    Press Enter when done:

    astreams_4

    Right-click any stream to add a sub-stream:

    astreams_5

    As before, begin typing to enter the sub-string’s name:

    astreams_6

    Also as before, press Enter when done:

    astreams_7

    You can nest streams as deeply as you like:

    astreams_8

    You may equate streams with tasks. Check them off as you complete each stream:

    astreams_9

    When you’re done, you can save an individual stream by right-clicking and selecting “Save Stream File.” You can save all the streams loaded by right-clicking and selecting “Save Streams Folder”:

    astreams_10

    Select a folder and click OK:

    astreams_11

    Your streams are stored in JSON format in the selected folder:

    streams_12

    You can manage streams by copying, pasting, editing, and deleting the JSON files, if you’d like.

    As you can see by viewing one of my streams folders, I use the utility often:

    streams_13

    It is a simple and flexible utility. I find it very helpful for tracking what I’ve done and what I need to do.

    You may purchase astreams here.

    Enjoy!

    :{>

  • The Recording for SSIS Catalog Management is Available

    The recording for the Enterprise Data & Analytics webinar titled “SSIS Catalog Management” is now available (registration is required).

    In the SSIS Academy: Using the SSIS Catalog series, Kent and I walked through the SSIS Catalog functionality built into SQL Server Management Studio (SSMS). This webinar picks up where SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration left off.

    In this webinar, Kent Bradshaw and I discussed and demonstrated some enterprise management shortcomings of the SSIS Catalog as it ships, and some of the tools and solutions we use to overcome those shortcomings.

    Learn More:
    SSIS Academy: Using the SSIS Catalog, Day 3 – Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

    :{>

  • An Example of Data Integration Lifecycle Management with SSIS, Part 1

    In this post I will provide some details about the demo SSIS project. I will then focus on version control and deployment.

    What DILM Is and Is Not

    Data Integration Lifecycle Management (DILM) is not about data integration development.

    DILM is about everything else:

    • Configurations Management
    • Version Management
    • Deployment
    • Execution

    Although DILM is not about development, implementing DILM will impact the design of SSIS solutions.

    The remainder of this post focuses on obtaining the solution, getting some pieces of DILM in place, and deploying the SSIS project to the SSIS Catalog.

    The Setup

    I’m using SQL Server Data Tools 2016 (Visual Studio 2015) on a virtual machine named vmSql16. The VM’s operating system is Windows Server 2016.

    The Project

    My SSIS project is named MedicalDataDemo. It contains two SSIS packages of relative complexity: ProviderGetFile.dtsx and ProviderStage.dtsx. ProviderGetFile will attempt to connect to the CMS website and download the latest National Provider Index (NPI) zip file, then it unzips the file. (Yes, it does all this in SSIS using a couple/three snappy design patterns.) The file name and file status are stored in a database named Medical. The ProviderStage package reads the name of the file and loads its contents to a table in the Medical database. There’s a 2016 version backup of the Medical database included in the project, which you can download the project in this state from Dropbox here.

    Version Control

    If you download the project and open it in SSDT, you may get source control messages. I added the project to source control, then “unbound” it before zipping it and sending it to Dropbox. Hopefully that was enough and you won’t see those pesky source control prompts.

    That said, please use source control. If you do not have access to version control, create a (free) account at visualstudio.com. I use visualstudio.com version control for internal work; I’ve been using it since it was in beta. 

    “There are two types of developers, those who use source control and those who will.” – Andy, circa 2005

    Once source control is set up, SSDT Solution Explorer will add indicators about the state of the solution (click to enlarge):

    dilm__1

    Deployment

    The screenshot above shows me deploying MedicalDataDemo. Right-click the project name – MedicalDataDemo – and then click Deploy (click to enlarge):

    dilm_0

    This opens the Integration Services Deployment Wizard (click to enlarge):

    dilm_1

    The first page of the Integration Services Deployment Wizard is an introduction. Click the Next button to open the next page in the process (click to enlarge):

    dilm_2

    Note that the “Select Source” page is skipped. That’s intentional; the wizard knows you’re deploying from SSDT.

    On this page you need to do some work. First, select a SQL Server instance that hosts an SSIS Catalog. If you’re scratching your head and asking, “What’s an SSIS Catalog?” that’s ok. Please see SSIS 2016 Administration: Create the SSIS Catalog for more information about setting up an SSIS Catalog.

    Click the Next button to open the next page in the process (click to enlarge):

    dilm_3

    The Review page contains some helpful information. Did you know you can execute SSIS project deployments from the command line? You can, and the arguments portion of the command line are shown on the Review page of the Integration Services Deployment Wizard. Click the Deploy button to deploy the project and open the next page in the process (click to enlarge):

    dilm_4

    Once deployment is complete, the Integration Services Deployment Wizard should appear as shown above.

    If you open SQL Server Management Studio (SSMS) and connect to the SQL Server instance to which you deployed the project, you can expand the Integration Services Catalogs node and drill down to the SSIS project you deployed:

    dilm_6a

    The SSIS packages in our project are now ready for execution, but they will execute with the default values we configured at design time. Our next step is to set up some external configurations.

    Conclusion

    In this post we discussed the demo SSIS project, version control, and deployment. In the next installment, I will demonstrate how to use the SSIS Catalog to execute and monitor these packages.

    :{>

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

     

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy

    Previous Posts in this Series:
    An Example of Data Integration Lifecycle Management with SSIS, Part 0

    Related Training: 
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago 
    From Zero to Biml - 19-22 Jun 2017, London
  • Announcing SSIS Catalog Compare and CatCompare Version 2

    CatCompareI am pleased to announce the release of SSIS Catalog Compare and CatCompare version 2. You can purchase a bundle that includes both products (and saves you some money) or can purchase each product separately: SSIS Catalog Compare and CatCompare.

    If you’re interested in learning more about the functionality provided by the products you can download (or view) the documentation for free.

    A Couple CatCompare Examples

    Two of my favorite chunks of functionality are the CatCompare commands: Deploy Folder Differences and Deploy Catalog Differences.

    Deploy Folder Differences

    Deploy Folder Differences can be executed after you load a couple Catalogs into the CatalogBase (an object I built to model the SSIS Catalog) server objects. They must be compared to detect the differences, and then the differences in one Catalog Folder in a Catalog instance can be overwritten by the version in the other Catalog’s Folder. The following command line accomplishes that, redirects the output to a text file, and then exits:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deployfolderdiffs;\vmSql16_Dev\IS\SSISDB\Folders\Demo\|0|\vmSql16_Test\IS\SSISDB\Folders\Demo>E:\Test\CatCompare_DeployFolderDiffs_Results.txt" "-exit"

    The command line above loads the Catalogs hosted on the vmSql16\Dev and vmSql16\Test SQL Server instances. The catalogs are compared, and then the differences identified by the compare operation between the Demo folder in each Catalog are deployed from the vmSql16\Dev Demo folder to the vmSql16\Test Demo folder.

    Why do I like this command? I can run it every night to collect the changes a data integration developer deployed to her Dev Catalog, deploying the updates (only) to an integration SSIS Catalog instance. You know, just like the C# developers when they practice DevOps.

    Deploy Catalog Differences

    The Deploy Catalog Differences command will help keep two Catalogs in sync:

    "C:\Program Files\DILMSuite\SSISCatalogCompare\CatCompare.exe" "-server0;vmSql16\Dev" "-server1;vmSql16\Test" "-comp" "-deploycatalogdiffs;0>E:\Test\CatCompare_DeployCatalogDiffs_Results.txt" "-exit"

    As before, two Catalogs are loaded and compared. Things that are different in server0 (vmSql16\Dev) are deployed to server1 (vmSql16\Test). The results are redirected to a file and the utility exits.

    Why do I like this command? I can use it to keep a “warm copy” of the Test SSIS Catalog somewhere else in the enterprise. This is handy if two (or more) data integration teams are developing updates to coupled projects.

    Conclusion

    My long-term goal with DILM Suite products is to facilitate DevOps and Data Integration Lifecycle Management with SSIS.

    Kent Bradshaw and I will be demonstrating SSIS Catalog Compare, CatCompare, and more DILM Suite utilities in the upcoming (free!) webinars: SSIS Catalog Management (10 Jan) and Advanced SSIS Execution (24 Jan).

    :{>

    Learn More:
    SSIS Catalog Management – 10 Jan 2017
    Advanced SSIS Execution – 24 Jan 2017

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Save Time and Improve SSIS Quality with Biml, A Free Webinar

    Join me at 1:00 PM EST 31 Jan 2017 for Save Time and Improve SSIS Quality with Biml, another free webinar from Enterprise Data & Analytics!

    “Save Time and Improve SSIS Quality with Biml” introduces Business Intelligence Markup Language (Biml) to SSIS developers, DBAs, sysadmins, and others who want to learn more about automating SSIS development.

    Automation (or code generation) improves the quality of SSIS solutions while saving time. With Biml, you can build lots of SSIS packages faster than building them manually. Because Biml generates SSIS packages from a template human error is mitigated. Therefore, the quality of the SSIS packages improves.

    I look forward to seeing you there.

    Register today!

    :{>

    Learn More:
    Biml Academy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Presenting “Using Biml as an SSIS Design Patterns Engine” at the (605) SQL Server User Group 10 Jan

    I am honored to present – albeit remotely – Using Biml as an SSIS Design Patterns Engine to the (605) SQL Server User Group in Sioux Falls, SD Tuesday 10 Jan 2017!

    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 build and execute your first Biml file! -How to design a single SSIS package using Biml -How to rapidly build multiple SSIS packages using Biml.

    :{>

    You might like working with Enterprise Data & Analytics because we dig Biml.

    Learn More:
    Biml Academy 
    The Basics of Biml – the Execute SQL Task
    The Basic of Biml – Populating the Biml Relational Hierarchy

    Related Training:
    From Zero to Biml - 19-22 Jun 2017, London
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago

  • Free Webinar–Advanced SSIS Execution–24 Jan 2017

    Join Enterprise Data & Analytics for Advanced SSIS Execution, a free webinar starting at 1:00 PM EST 24 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share several ways to execute SSIS packages.

    Register today!

    :{>

    You might like working with Enterprise Data & Analytics because we love the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • Free Webinar–SSIS Catalog Management–10 Jan 2017

    Join Enterprise Data & Analytics for SSIS Catalog Management, a free webinar starting at 1:00 PM EST 10 Jan 2017!

    Kent and I demonstrated how to use the SSIS Catalog in the SSIS Academy series (see links to the recordings in the Learn More section). In this webinar, we share best practices, tips, and tools for using the SSIS Catalog in the real world.

    Each attendee will receive a free bit set-able to the value of their choice – available in four states. Seating is limited so register today!

    :{>

    You might like working with Enterprise Data & Analytics because we love the SSIS Catalog.

    Learn More:
    SSIS Academy: Using the SSIS Catalog Day 1 - Create the Catalog and Deploy
    SSIS Academy: Using the SSIS Catalog Day 2 - Package Execution and Monitoring
    SSIS Academy: Using the SSIS Catalog Day 3 - SSIS Configuration

    Related Training:
    SSIS Lifecycle Management (free recording, registration required) 
    IESSIS1: Immersion Event on Learning SQL Server Integration Services – April 2017, Chicago 
    IESSIS2: Immersion Event on Advanced Integration Services – May 2017, Chicago
    From Zero to Biml - 19-22 Jun 2017, London

  • An Example of Data Integration Lifecycle Management with SSIS, Part 0

    What is Data Integration Lifecycle Management (DILM)?

    Here’s one way to think about DILM:

    Data Integration Lifecycle Management (DILM) is applying software Application Lifecycle Management (ALM) best practices to Data Integration development and operations (DevOps), version control, release management, and configuration.

    I can hear you thinking, “But Andy, why would we apply software best practices to a data integration platform like SQL Server Integration Services (SSIS)?” I’m glad you asked. The answer is: “Because SSIS development is software development.”

    SQL Server Integration Services suffers from having the name of a popular relational database engine – SQL Server – baked into its name. Don’t let that throw you, SSIS development is software development. Yes, SSIS packages are tightly-coupled to data sources and destinations, and to the data the packages move.

    I Object!

    I hear from many people who wear several hats in small shops. Many of them object to my thoughts about DILM for SSIS. If you are one of those people who wear many hats and object to my thinking about DILM, I want to say two things to you:

    1. You ROCK! Wearing many hats is hard. I’ve been there, done that, and have the blood-, sweat-, and tear-stained t-shirt.
    2. Do whatever works for you.
    3. BONUS 3rd thing: You may find some benefit from DILM practices if you give them a shot.

    Who Needs to Practice DILM?

    It depends on  the problem(s) you are trying to solve. Here are a few questions to help you determine your need for implementing DILM:

    • Have you ever lost code?
    • Has a server ever crashed and part of the solution involved re-developing code?
    • Have you ever received a phone call from work while on vacation?

    If you answered, “Yes,” to any of these questions, implementing some flavor of DILM may help.

    This Series

    I don’t have all the answers, so I cannot possibly provide all the answers to you. What I can do, though, is share some things I’ve learned implementing SSIS solutions for the past decade. I’ve led a team of 40 ETL developers building multiple enterprise-class projects simultaneously. I’ve parachuted into enterprises on fire as a lone wolf consultant and helped douse the flames. I've joined teams and formed teams to solve enterprise data integration problems. This breadth of experience has taught me priorities that are different from the priorities of some of my compatriot SSIS professionals.

    You may read some of my thoughts and think, “That’s overkill.” To which I will respond, “Yeamaybe.” I understand. Really I do. As I wrote to the small-shop-people, do what works for you.

    I want to tell the story of a data integration project, to follow it through its lifecycle as it starts, matures, and grows. Although the project is interesting, we will focus on lifecycle. I hope everyone finds some value in this series for that is my goal.

    :{>

  • Three Lessons Learned in 2016

    Some folks do not like to read posts of a more personal nature or posts that contain religious references. If you are one of those people, you have been forewarned.

    I’ve read lots of posts from people who are ready for 2016 to be over. I understand their reasons. Celebrities, friends, and family have passed away this year. The world economy stumbled through another four quarters. About 51% of US voters were disappointed by results of the 2016 US Presidential Election (which are still being updated at the time of this writing, by the way).

    I learned some things in 2016 and would like to join the chorus of sharing as 2016 draws to a close.

    New Businesses

    Isaiah 61:3

    In 2015 I left Linchpin People and founded Andy Leonard Consulting. Shortly thereafter I was having lunch with my friend Nick who said, “‘Andy Leonard Consulting’ sounds like a one-person operation.” “There’s a very good reason for that,” I replied. Before we finished lunch, Nick and I agreed to work together on a new venture called Enterprise Data & Analytics, or EDNA. EDNA officially launched in January 2016.

    I kept Andy Leonard Consulting around and repurposed it as a software development company. ALC built and manages the Data Integration Lifecycle Management Suite (or DILM Suite), a collection of mostly-free utilities that facilitate my vision of enterprise data integration DevOps and Continuous Integration (CI) for SSIS.

    If you’re playing along at home, I operate two businesses: Andy Leonard Consulting (ALC) and Enterprise Data & Analytics (EDNA). ALC has “consulting” in the name but does software, not consulting. EDNA does consulting. Confused? Me too…

    Lesson: Entropy happens. Without an anchor you’ll either drift – or be blown – away. My anchor is faith in Christ. I’m reminded of Isaiah 61:3 that states God gives “beauty for ashes.” Some translations render this passage “God makes beauty from ashes.” A chunk of my life burned down in 2015. I am astounded at, overwhelmed by, and thankful for what God is making from these ashes.

    Becoming a Better Listener

    On Being Wrong

    I started listening to audio books in 2016. I have listened to audio books in the past, but I focused on listening to them in 2016. I like Audible for a number of reasons:

    • I pay ~$15/month and get 1 credit that can be used to access one book.
    • I can accumulate 6 credits over 6 months if I do not use them, so if I go through a period of not accessing audio books I don’t start losing credits until the seventh month.
    • Audible’s cloud integration means I can listen on my laptop or phone, picking up right where I left off on the other device.

    My taste in audio books is different from my taste in books I read. I’ve tried to listen to books for entertainment but I cannot enjoy them. I don’t know why this is so. I enjoy listening to audio books about theology and business. While I enjoy reading books about theology and business and science fiction, I’ve mostly shifted to reading sci-fi and listening to theology and business books.

    I prefer audio books read by the authors. Why? To me, they’re more… real. The author knows where to inflect for emphasis. Sometimes the author will throw “extras” into the audio book that are not in the written edition. The best business book I heard in 2016 is an example: Grant Cardone injected dozens of thoughts into the audio book The 10X Rule that did not appear in the print version. All of them are great and some of them are hilarious! 10X is a book about success. I confess some confirmation bias in recommending this book; I’ve long believed and stated that there’s no substitute for hard work. I assigned this book to my older son, Stevie Ray, as a homeschool reading assignment. That’s how much I liked it.

    My friend and brother Frank La Vigne (blog | @tableteer) recommended this book – thanks Frank!

    Listening to audio books like 10X has improved my listening skills. It turns out that listening to the words that people who live with you and work with you are saying and writing to you is a good idea (who knew?). Listening is an especially good idea for me because I am a hard-headed and triflin’ redneck (Can I get an amen?). I mentioned confirmation bias earlier because I’m more guilty of it than most. Kathryn Schulz’ TED Talk On Being Wrong – and the book – smacked me upside the head. The TED Talk is just under 18 minutes. I encourage you to invest 18 minutes listening to her. She’s brilliant. Will it help if I link to her TED Talk again?

    Lesson: Listen. Listen more and listen well.

    There’s More to Learn

    Pluralsight

    I’m going to confess something. I’m going to be vulnerable (another great TED Talk by Dr. Brene Brown – 20 minutes – we’re up to 38 minutes of assigned TED Talks now…). SSIS Catalog Compare is the first product I’ve written. It’s also the first full application I’ve attempted writing in C#. I’ve been writing software since 1975 so I am familiar with the practice of developing code. Although I’ve built applications in the past, I’ve never built a soup-to-nuts product and I’ve never used C# to do so; I’ve only used C# to build code snippets.

    Catalog Compare grew out of an epiphany while attempting to write a GUI to manage an SSIS Framework. An SSIS Framework can simplify your enterprise data integration. You can start the execution of a collection of SSIS packages with a single command. Awesome, right? What’s not to love? Well… there’s no free lunch. In order to accomplish this magic, an SSIS Framework relies upon a boat load of metadata. SSIS execution, whether or not one uses a Framework, also relies on even more metadata stored in the SSIS Catalog. I was building this interface to manage Framework metadata when I realized there’s no easy way to manage most of the metadata stored in the SSIS Catalog.

    So I wrote an app for that. In C#.

    Why? I found a problem that I wanted to solve. And I wanted to learn C# – really learn it. So what did I do? I committed to building SSIS Catalog Compare in C#. I knew I would need help. Fortunately for me, I’m surrounded by awesome friends who are literally masters of software development and C#. Two friends in particular, Scott Currie (owner of Varigence, inventor of Biml, all-around nice guy) and Kevin Hazzard (blog | @KevinHazzard), listened to me and suggested improvements and next steps without laughing (to my face) at my code. With their help and help from Google and Pluralsight, I learned more C# in 2016 – enough to release SSIS Catalog Compare in August.

    Thank you, Scott and Kevin.

    Lesson: Keep learning.

    Post-note: As I type this, SSIS Catalog Compare v2 is nearing release. There’s a free “view-only” utility based on Catalog Compare functionality (it’s actually a subset of the Catalog Compare codebase) called SSIS Catalog Browser. After the Catalog Compare v2 release I return to developing that Framework Manager GUI I started coding in August 2015. There’s a “view-only” version of this yet-to-be-built application available today, for free. It’s called SSIS Framework Browser and it works with the SSIS Framework Community Edition which is not only free, it’s open source! </ShamelessPlug>

    Conclusion

    2016 was a year of lessons learned for me. I am looking forward to 2017!

    Happy New Year!

    *<:{>

  • My Top Blog Posts for 2016

    KrispyKremeHotNow
    (with a nod to @KrispyKreme)

    I’d like to wish everyone reading this post a blessed, peace-filled, and happy 2017! I would also like to thank you for reading this post and any other posts you may have read.

    Some Blog Post Stats

    I published 87 blog posts this year at SqlBlog.com. I published most in December (12) and least in October (2).

    These posts attracted more reads than the others (ordered by most reads in descending order):

    1. Installing SQL Server 2016 Developer Edition, One Example
    2. SQL Server 2016 Developer Edition is Free
    3. Announcing Biml Academy!
    4. PASS Board Elections–Voting is Open!
    5. SQL Server Developer Edition is FREE!
    6. Biml Academy 2 Webinar Recordings are Available!
    7. Microsoft is Listening
    8. A Couple-Three Thoughts and Questions About Swag at Community Events
    9. A New Version of SSDT is Available
    10. BI’s Not Dead

    Is This Accurate?

    But… Some of those posts have been around a lot longer than others. The oldest post in this list is 325 days old (at the time of this writing). The newest post is 85 days old, the average “age” of these posts is 210 and the median “age” is 215 days. 

    How does a Data Philosopher account for this? I computed the age of each post and then divided the read count by that number. The results now look like this:

    1. The Recordings for SSIS Academy: Using the SSIS Catalog are Available
    2. SQL Server vNext CTP 1.1 is Available!
    3. SQL Server Management Studio (SSMS) v16.5.1 Now Available
    4. Learn More About the SSIS Catalog
    5. Three Free Webinars About Using the SSIS Catalog
    6. An Interview With Me
    7. DLM (Database Lifecycle Management)
    8. SSIS Catalog Browser Update
    9. Broken References in the SSIS Catalog
    10. New Versions of SSMS and SSDT Available

    Is This Accurate?

    But… This second list is skewed towards the newer posts due to the lower denominator. The newest post is 2 days old (and it’s #1) while oldest post in this list was published 43 days ago (at the time of this writing). The median “age” of the posts in this list is 16 days and the average “age” is 18.

    Noodling…

    I suspect both lists are valid because readership declines after the post scrolls off the front page at SqlBlog.com (which displays the latest 20 posts). I write “both lists are valid” because these results (like all results in data analytics) must be considered in context. Context is defined by answering the question, “What is the problem we are trying to solve?” There are other ways to analyze this data – lots of other questions we can ask of it. Data science is science and science (well, good science, anyway) involves experimentation.

    My theory: The first list contains the posts that experience some “longevity in appeal” (or SEO). The second list is “Hot Now.” Or not. I’d love to hear your thoughts.

    Happy New Year!

    *<:{>

More Posts Next page »

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement