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, and developer; a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and author of Managing Geeks - A Journey of Leading by Doing, and the Stairway to Integration Services.

  • 800

    In May 2015 I wrote 700 – a post marking my 700th blog post here at SqlBlog.com. Last month I wrote Nine Years about another milestone here. Well, here we are in August 2016 and I’ve reached 800 posts!

    It’s an honor and privilege to write here. Here’s to the next 100 posts!

    :{>

  • Data Integration Lifecycle Management (DILM) with SSIS Catalog Compare: SSIS Catalog Environments

    Click to enlarge

    Have you ever tried to move some (or all) of the contents of one SSIS Catalog to another? Deploying an SSIS Project to a different SSIS Catalog is straightforward: Execute the ISPAC file, configure the new SSIS Catalog as the target, click a few times, and Boom; done.

    What about the rest of it? I can hear you thinking:

    “What Rest Of It, Andy?”

    Some background may help. The SSIS Catalog has many awesome features. One less-publicized (in my opinion) feature is Catalog Environments. If you’ve used Package Configurations in the past, you may have encountered some unexpected behavior. Catalog Environments is an elegant – albeit complex – solution that overcomes many of the “quirks” of Package Configurations. I explain some features of Catalog Environments in SSIS Catalog Environments– Step 20 of the Stairway to Integration Services  as part of the SSIS Catalog Environments– Step 20 of the Stairway to Integration Services series at SQL Server Central.

    The “rest of it” to which I refer is the project and package configuration metadata stored in the SSIS Catalog.

    I began building data integration frameworks (years ago) to support the common enterprise need to manage connections. My first Framework was designed in Data Transformation Services (DTS). It used INI files and the Set Properties functionality in DTS to facilitate DTS packages “finding” their connections at run time. I built a similar solution for SSIS 2005. Automating connections management is tedious but worth it: done correctly, it strongly mitigates the possibility of executing Development and Test executions and having them impact Production. (Running DTS in Development and loading data into Production is what inspired me to build the first solution with DTS.)

    Reference Mappings 101

    In the image above we see one basic example of SSIS configurations metadata in the SSIS Catalog (click the image for a larger view). The circled items make up the constituents of a Reference Mapping that configures the ConnectionString property of a package (Stage_Member.dtsx) Flat File Connection Manager named “FFCM_Member_Source” to one of two potential values stored in a Catalog Environment Variable named “MemberFilePath”. The two different values are stored in separate Catalog Environments named “Env1” and “MedicalData”. A “link” is configured between each Catalog Environment and the Stage_Member.dtsx package; this “link” is called a Reference.

    A Reference Mapping establishes a “link” from a Catalog Environment Variable, through a Catalog Environment, via a Reference to a project or package, to a project or package parameter. Only one reference can be selected at run time, so the value of the project or package parameter is sourced from the value of the Catalog Environment Variable in the Catalog Environment that is “linked” to the Reference selected at run time.

    CCDoc_29

    As I mentioned in passing above, complex. But also elegant.

    A Catalog Environment can hold dozens of Variables and Values. All that is required to completely reconfigure the execution of an SSIS Package is to select a different reference.

    “How Do You Move an SSIS Catalog Environment and Its Variables, Andy?”

    Using out of the box functionality, there’s no good answer to this question. If I open a Catalog Environment in the Integration Services Catalogs node of SQL Server Management Studio’s Object Explorer, there’s a button labeled “Script”:

    ScriptEnvironment

    Once Catalog Environment configuration is complete, clicking this button opens an empty SSMS query window.

    To complete the Reference Mapping, one needs to create the Reference between the Project or Package and the Catalog Environment, and then map the parameter to the desired Catalog Environment Variable. Using the Integration Services Catalogs node of SQL Server Management Studio’s Object Explorer, one can right-click the Project or Package and click “Configure” to open a Configure dialog. This dialog also contains a “Script” button. Clicking this button after configuration changes have been stored does nothing:

    ScriptConfiguration

    I am not the first data integration developer to notice these issues. Several data integration developers have produced scripts that query the SSISDB database (the SSIS Catalog database) to surface this information.

    SSIS Catalog Compare: Catalog Environments, Variables, References, and Reference Mappings

    SSIS Catalog Compare provides two solutions: scripting and deployment. As shown below, a Catalog Environment (or Reference) can be scripted:

    CC_ScriptEnvironment

    If a data integration developer of DevOps configuration manager scripts the Reference…

    CC_GenerateReferenceScript

    … scripts are automatically generated for dependent objects (the dependencies for a Reference are the Folder and the Catalog Environment):

    CC_EnvironmentScripts

    Deployment is even easier. Connect to two SSIS Catalog Instances:

    CC_TwoCatalogs

    Right-click the artifact you wish to deploy and click “Deploy <artifact>”:

    CC_DeployReference

    Deploying References and Reference Mappings requires selection of a target Project and a target Catalog Environment:

    CC_DeployReferenceConfirmationDialogs

    That’s it. You’re done.

    The Beta Continues… But Not for Long!

    At the time of this writing (5 Aug 2016) SSIS Catalog Compare is in beta. If you purchase SSIS Catalog Compare now, you pay only $95 USD for a non-expiring license that includes lifetime maintenance upgrades.

    This offer will expire soon!

    SSIS Catalog Compare v1.0 will be released soon and this offer will expire. The license and yearly maintenance prices will both increase when SSIS Catalog Compare v1.0 is released.

    Act now! Contact me for information on bulk pricing.

    :{>

  • Presenting at Spartanburg and Charlotte SQL Saturdays!

    I am honored to be presenting at two upcoming SQL Saturdays, Spartanburg and Charlotte!

    In Spartanburg 20 Aug, I’m presenting:

    Together, these two presentations an introduction to Business Intelligence Markup Language (Biml) and advanced-level application.

    In Charlotte 17 Sep, I’m presenting a 90-minute session called Biml and SSIS Frameworks. This session focuses on aspects of DevOps and Data Integration Lifecycle Management (DILM) for enterprise SSIS package execution and reporting. Why 90 minutes? I demonstrate Biml plus free DILM Suite tools:

    I hope to see you there!

    :{>

    Learn more:

    Related Training:
    Biml Academy 2 - 15-19 Aug 2016 – Register today!
    Loading Medical Data to the Cloud, a (FREE!) webinar - 11 Aug 2016 – Register today
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles:
    Stairway to Integration Services
    SSIS 2016 Administration: Create the SSIS Catalog
    SQL Server 2016 Developer Edition is Free
    Converting an SSIS 2014 Solution to SSIS 2016
    Installing SQL Server 2016 Developer Edition, One Example
    Deploying a Single Package First in SSIS 2016 Announcing SSIS Catalog Reports v0.1
    SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • Announcing Biml Academy 2!

    BimlAcademy-Course-Option1

    Biml Academy 2 – Lessons 5-9 – will be 15-19 Aug 2016. Each 1-hour session will begin at 1:00 PM EDT and will be recorded.

    Join Cathrine Wilhelmsen (blog), BimlHero and Microsoft Data Platform MVP, Scott Currie (creator of Biml and Mist), and me as we explore staging and loading data using SSIS packages generated by Biml.

    • Monday 15 Aug: Cathrine demonstrate several methods of importing source tables, schemas, databases, and connections into Biml.
    • Tuesday 16 Aug: Andy demonstrates one approach to staging data with Biml.
    • Wednesday 17 Aug: Andy demonstrates a metadata-driven staging pattern with Biml.
    • Thursday 18 Aug: Scott demonstrates BimlFlex, a Biml solution for delivering data warehouses using the Data Vault methodology.
    • Friday 19 Aug: Biml Q & A with several BimlHeroes!

    Register today!

    :{>

  • RVA (Richmond) Code Camp is Back!

    I’m excited to announce Richmond Code Camp is back! We’ve rebranded to RVA Code Camp and our next event is scheduled for 22 Oct 2016 at ECPI in Glen Allen, VA.

    At the time of this writing:
    Registration and the Call for Presentations are both open.

    We hope to see you there!

    :{>

    PS – Are you interested in sponsoring the event? Contact us!

  • Free Webinar: Loading Medical Data to the Cloud

    Join Andy Leonard and Kent Bradshaw of Enterprise Data & Analytics 11 Aug 2016 at 1:00 PM EDT as we discuss and demonstrate strategies for loading medical data to the Microsoft Azure SQL DB with SQL Server Integration Services (SSIS). Register today!

    :{>

  • Stop an SSIS Package

    There are several ways to stop and executing SSIS package. If the package is executing in the SSIS Catalog, you can use the SSISDB.[catalog].stop_operation stored procedure. Stop_operation has one parameter – a bigint named @operation_id. You can execute the stored procedure thus:

    exec SSISDB.[catalog].stop_operation @operation_id = <operation_id>;

    You can obtain the operation_id of an executing SSIS package in several ways.

    One way is from the All Executions report built into SSMS:

    StopSSIS_0

    Another way is to use my (free) utility – Catalog Reports – which is a SQL Server Reporting Solution (SSRS) solution that allows you to view much of the information contained in the Catalog reports built into SQL Server Management Studio (SSMS). It’s shown here running on SSRS 2016 inside the Microsoft Edge browser:

    StopSSIS_1

    Once you have the operation_id value, simply plug it into the stop_operation stored procedure and execute:

    exec SSISDB.[catalog].stop_operation @operation_id = 24

    The stop_operation stored procedure runs for a few seconds (typically less than 15 seconds) and stops the execution of the SSIS package. SSIS packages that have been stopped are listed with “Canceled” status. You can see operation_id 19 was stopped in the screenshots shown above.

    :{>

    Learn more:

    Related Training:
    Loading Medical Data to the Cloud, a (FREE!) webinar - 11 Aug 2016 – Register today
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles:
    Stairway to Integration Services
    SSIS 2016 Administration: Create the SSIS Catalog
    SQL Server 2016 Developer Edition is Free
    Converting an SSIS 2014 Solution to SSIS 2016
    Installing SQL Server 2016 Developer Edition, One Example
    Deploying a Single Package First in SSIS 2016 Announcing SSIS Catalog Reports v0.1
    SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • Nine Years

    Exactly nine years ago I published my very first post here at SQLBlog.com titled SSIS Design Pattern - Incremental Loads.

    BlogDashboard_20160702

    At the time I wrote this post, the last page of my blog dashboard appeared as shown in the screenshot above. It shows the Incremental Loads Design Pattern post has received over 230,000 views in the past nine years. That’s about 2,000 views per month or about 71 views per day. For nine years. Wow.

    I asked my friend Adam Machanic (Blog | @AdamMachanic) about the total number of views for all the blog posts on my blog. Adam tells me we’ve recorded 7,742,031 direct views over the past nine years; or over 70,000 views per month. That’s not all of the views, either – that’s just the stuff he can measure directly. Wow!

    In marketing, this is called a long tail. Once the post is written and posted, people can access it and read it for years. I’m a farmer and writing a blog post is like planting a tomato plant (I love fresh tomatoes – they are so full of taste right off the vine!) and having it continue to produce fruit for years without any tending.

    You should start a blog. I wrote a couple posts about getting started years ago. I hope they encourage you to begin a blog today.

    I hope you enjoy what you’ve read here for the past nine years. I look forward to nine more years of blogging, Lord willing!

    :{>

  • Use Catalog Compare to Migrate to the SSIS 2016 Catalog

    I recently tried to use the SSISDB Upgrade Wizard to upgrade a restored SSISDB (backed up in an earlier version) to SQL Server 2016. It didn’t go well.

    I decided to use SSIS Catalog Compare to generate the scripts and ISPAC files from the previous instance, and deploy them to the SSIS 2016 Catalog.

    “You Can Do That?”

    Yes. Yes you can. Here’s how…

    First, connect to an SSIS 2014 Catalog using Catalog Compare. Right-click the SSISDB node and click “Generate All Catalog Scripts”:

    SSISDBUpgrade_10

    The Browse for Folder dialog displays. Select or create a target folder to hold the Catalog scripts and ISPAC files:

    SSISDBUpgrade_11

    Click the OK button. Once the export is complete, Catalog Compare’s status will display something similar to this message:

    SSISDBUpgrade_12

    A directory is created for each SSIS Catalog folder, and the scripts and ISPAC files are stored in that directory. They are numbered in order of dependencies:

    SSISDBUpgrade_13

    Each script contains documentation that specifies where it was generated, when, by whom, and which version of Catalog Compare was used:

    SSISDBUpgrade_14

    Each ISPAC file uses the Integration Services Deployment Wizard:

    SSISDBUpgrade_15

    I conducted a quick test after the migration and found the newly deployed SSIS packages function in the SSIS 2016 Catalog:

    SSISDBUpgrade_16

    At the time of this writing, SSIS Catalog Compare is in beta, and we’re offering a pretty sweet deal on it. Learn more here.

    :{>

    Learn more:

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles:
    Stairway to Integration Services
    SSIS 2016 Administration: Create the SSIS Catalog
    Announcing SSIS Catalog Reports v0.1
    SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • (Attempting to) Upgrade the SSIS Catalog to SSIS 2016

    A couple people have asked me about upgrading the SSIS Catalog from previous versions to SQL Server 2016 so I decided to blog about it.

    The SSIS Catalog is an SSIS Execution Framework that ships with SQL Server versions starting with 2012. To execute SSIS packages using the Catalog, the SSIS project must be built using the Project Deployment Model and deployed the SSIS Catalog. You can learn more about the process at the MSDN topic: Deployment of Projects. All SSIS Catalog functionality is encapsulated within a database named SSISDB.

    As this MSDN article explains, SQL Server 2016 ships with a utility named the SSISDB Upgrade Wizard:

    SSISDBUpgrade_6

    The Adventure Begins

    I decided to try it by restoring a backup of an SSISDB database to SQL Server 2016:

    SSISDBUpgrade_0

    The restore worked as expected:

    SSISDBUpgrade_1

    And the SSISDB database appeared in my list of databases in SSMS Object Explorer:

    SSISDBUpgrade_2

    So far, so good. I clicked the Start Windows icon in Windows Server 2016 Technical Preview 5 and browsed All Applications until I found the SQL Server 2016 Upgrade Wizard link:

    SSISDBUpgrade_3

    Once the upgrade wizard opened, I entered the name of the SQL Server 2016 instance that hosts the restored SSISDB database and then checked the “I have taken a backup of the SSISDB database. Continue with the SSISDB upgrade” checkbox. I clicked the Upgrade button to begin the upgrade process:

    SSISDBUpgrade_4

    I received this error:

    SSISDBUpgrade_5

    Sad Trombone…

    The text of the error message reads:

    CREATE ASSEMBLY for assembly 'Microsoft.SqlServer.IntegrationServices.Server' failed because assembly 'Microsoft.SqlServer.IntegrationServices.Server' is not authorized for PERMISSION_SET = UNSAFE.  The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
    Changed database context to 'SSISDB'.
    Warning: The SQL Server client assembly 'microsoft.sqlserver.integrationservices.server, version=13.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

    I keyed on “PEERMISSION_SET”. My first thought was security and I know from experience the SSISDB database employs Transparent Data Encryption (TDE). So I began searching for information on how to restore a database that uses TDE. I found a helpful post at http://dba.stackexchange.com/questions/3388/restore-encrypted-database-to-another-server and executed a query I found in an answer:

    SSISDBUpgrade_8

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
     ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
     GO
    The query succeeded, so I retried the SSISDB Upgrade Wizard and got an new error:
    SSISDBUpgrade_9
    The error message text is:

    Cannot drop the assembly 'ISSERVER', because it does not exist or you do not have permission.
    Changed database context to 'SSISDB'. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

    I tried again, thinking I should re-restore the SSISDB database, then run the query to fix the TDE. No dice, same error as shown above. As of the time of this writing, I haven’t figured it out. I will update this post when I do.

    In the meantime, I decided to take

    A Different Approach…

    … and use Catalog Compare to Migrate to the SSIS 2016 Catalog.

    :{>

    Learn more:

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles:
    Stairway to Integration Services
    SSIS 2016 Administration: Create the SSIS Catalog
    Announcing SSIS Catalog Reports v0.1
    SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • Use Biml to Remove Spaces from Flat File Column Names

    I hate a space in a database column name.

    Kent Bradshaw and I are updating our demo for loading National Provider Index (NPI) file data (We plan to present on this soon… more later…). The NPI files currently have 329 fields and almost every one of them contains spaces:

    ImportPackage_0

    I had 5 minutes, so I decided to fix it with Biml. I first created an SSIS package and added a Flat File Connection Manager configured to consume the NPI data file. I opened Mist and clicked the Import tab. Then I clicked the Import Packages button on the ribbon:

    ImportPackage

    When the Import Packages window displayed, I selected “DTSX From File System” option from the SSIS Asset Location dropdown:

    ImportPackage_1

    The Import Packages window is reconfigured to support the import of an SSIS Package:

    ImportPackage_2

    I click the “Select DTSX File Paths” link to open the “Browse for DTSX Files” dialog. I select the SSIS package I wish to import and click the Open button:

    ImportPackage_3

    Once the SSIS package is selected, click the Import button:

    ImportPackage_4

    Once the SSIS package has been imported, click the Add To Project button to add the imported SSIS package to the Mist project:

    ImportPackage_5

    The Biml

    Once hidden nugget of Biml in Mist is the Object Model. You can traverse the SSIS package via the RootNode. Actually, you can traverse and reference much more than just a single SSIS package, but that is for another post.

    RootNode is your friend. What is RootNode? Reeves Smith provides an explanation in Stairway to Biml Level 5 - Biml Language Elements, part of the Stairway to Biml series at SQL Server Central.

    There are a couple ways to approach removing spaces from the Flat File Connection Manager’s column names. I chose to build the Biml for the columns and paste it into the Flat File Connection Manager’s Biml.

    The Biml file for the Flat File Connection Manager may be found on the Project View tab in Mist. Expand the project (MedicalDataBiml, in this case)—>addedBiml—>FileFormats to locate the Flat File Connection Manager’s Biml file:

    ImportPackage_6

    Opening this file reveals the Biml representation of the Flat File Connection Manager’s format.

    ImportPackage_8

    Now, the Code

    I used the following Biml to remove spaces from the column names:

    ImportPackage_9

    <#
    AstFlatFileFormatNode ffformat = (AstFlatFileFormatNode)RootNode.FileFormats["FFCM Provider Data"];
    foreach(var col in ffformat.Columns)
    {#>
        <Column Name="<#=col.Name.Replace(" ", "") #>" DataType="<#=col.DataType #>" Length="<#=col.Length #>" MaximumWidth="<#=col.MaximumWidth #>" Delimiter="<#=col.Delimiter #>" />
    <#}
    #>

    This Biml declares an AstFlatFileFormatNode named ffformat and sets it to the FileFormat named “FFCM Provider Data” found the RootNode’s FileFormats collection. I next loop through each column in the ffformat AstFlatFileFormatNode object. I use Biml to generate the <Column> object, replacing the spaces with an empty string for the Name attribute. The results in Mist appear as shown below:

    ImportPackage_10

    I copied these 329 results from the Preview Expanded BimlScript box and pasted it back into the FFCM Provider Data.biml file:

    ImportPackage_11

    I saved the FFCM Provider Data.biml and built the Package:

    ImportPackage_12

    Now, the Flat File Connection Manager’s column names contain no spaces:

    ImportPackage_15

    The only caveat was the Unicode checkbox was checked when I reopened the Flat File Connection Manager Editor:

    ImportPackage_13

    Unchecking the checkbox was a small price to pay. Ok, make that 5 minutes and 10 seconds.

    :{>

    Learn more!

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services
    Biml Academy

    Related Articles:
    Stairway to Integration Services
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • PASS Updates to Speaker Contracts

    Adam Jorgensen, PASS President, has posted an update about the PASS speaker contract: Contributions to the Speaker Contract.

    Included in the post are links to the updated versions of the Summit Speaker Contract and the Summit Pre-Conference Contract.

    PASS leadership has put more time into this issue than they planned to and I applaud their efforts.

    :{>

  • PASS Summit 2016 Speakers

    I commend PASS, the PASS Board, and PASS leadership for all their hard work putting together this year’s PASS Summit. From the looks of the lineup of presenters and presentations the PASS Summit 2016 is going to be another awesome event!

    Allen White (@SQLRunr) serves as PASS Programs Director. Although Allen has been actively engaged in PASS for years, this was his first year leading the PASS Summit speaker selection effort. Allen wrote a great blog post about the PASS Summit speaker selection process: Insights into the PASS Summit Speaker Selection Process. I recommend anyone who is interested in presenting at the PASS Summit read the post.

    Some highlights:

    • The PASS Summit 2016 has:
      • 112 community sessions
      • 10 full-day pre-conferences
      • 4 lightning talks
    • There were:
      • 840 abstracts submitted
      • from 255 speakers potential speakers

    And, my favorite statistic:

    • 20% of those chosen to present at the PASS Summit 2016 are first-time speakers at the event!

    That’s a fantastic number! I’ve long been a champion of increasing the number of first-time presenters at the PASS Summit so I am excited to see this number. I am not sure, but I believe 20% may be a new record for first-time speakers at the Summit (excluding, of course, the first Summit where everyone was a first-time speaker).

    Stuff

    Every year there’s “stuff” surrounding the PASS Summit speaker selection process. This year there was a lively conversation about changes to the Speaker Contract. A lot of people weighed in on the controversy. I wrote A Couple-Three Thoughts and Questions About Swag at Community Events and there were lots of comments.

    We often refer to the SQL Server Community as #SQLFamily. I think that’s an accurate representation of our community. There is often love and warm fuzzies, but sometimes there are misunderstandings and hurt feelings. I think it’s all pretty normal.

    The SQL Server Community is the envy of other technical communities. We regularly share knowledge and help each other. No one seems concerned with hoarding knowledge to make themselves look smarter than everyone else. That. is. awesome!

    Why? Because I didn’t learn what I know in a vacuum. The SQL Server Community has helped me along the way. I shared this a few days ago and it is Just True:

    My name is Andy. I am Community taught.

    I’ve blogged several times about why I love the PASS Summit and how attending has changed my life and career. Read those. Especially the first one. The key line in that first link (for me) is:

    I know it sounds cheesy, but I entered that room as a guy learning databases and left it a database professional.

    After that experience at the PASS Summit 2004, I was on-track to become a SQL Server MVP and author. Before that, I was the biggest imposter ever.

    This Year…

    For the first time in a long time, I will not be attending the PASS Summit this year. There are a couple-three reasons. Enterprise Data & Analytics is just getting going (we are still in our first year, can you believe that?). DILMSuite and Biml Academy are also in their infancy. It takes time and effort to shepherd new endeavors, and shepherding three at once is a lot of work.

    I wasn’t selected to present. Now, some will read that and think harshly of me. I forgive you. There is definitely value for me in attending whether I present or not. It’s simply a matter of priorities, and – this year – attending the PASS Summit falls just beneath the dashed line between what I will do and what I will not do. I am really and truly sorry that this is so. I will miss seeing friends and #SQLFamily from all over the world.

    At the same time, though, presenting at the PASS Summit is a zero-sum game and the fact that I am not presenting means that someone else has that opportunity – maybe for the first time.

    Am I happy to not be presenting? Goodness no. I was disappointed when I read the email. Not being selected stung a little. I reacted poorly, actually, for about 10 minutes (I can produce a witness). But go back and read Allen’s blog post. The process is fair. My abstracts and speaker ratings data simply didn’t make the cut. As that initial 10 minutes drew to a close I remembered how it felt to be selected to present at the PASS Summit for the first time. I know many of the first-time presenters and I became so thrilled for them that this new thought completely overwhelmed the sting.

    Conclusion

    The PASS Summit 2016 is going to be an great event. I hope you will attend and engage with the awesomeness that is the SQL Server Community! Register now!

    :{>

  • Migrating SSIS Projects and Parameters, Part 1 is Live at SQL Server Central!

    Have you ever moved an SSIS project from one SSIS Catalog to another? With Catalog Environments and References? If you haven’t, you may be in for a surprise when you try.

    First, why should you try?

    SSIS is software development.” – Andy, circa 2008

    Best practices for software development apply to SSIS. One of those practices is: Never allow developers to deploy their own code to Production. Does this sound like a silly rule? If you’re the only developer in your enterprise, this is a silly rule. But even for you, O’ Lone Developer, software best practices can help prevent you from stepping on your own foot. (You are my hero, O’ Lone Developer…)

    Why is this even a best practice? Let’s get hypothetical. I don’t like to be negative, so I will not ask, “What if you get hit by a truck?” I’ll be positive and ask, “What if you win the lottery?” We don’t really need to go to an extreme of death or lottery-winning: What happens when you (try to) take a vacation? (Andy’s Rule for Calling People Who Are on Vacation: If you get called while on vacation, you get paid for that day.) You want a repeatable process in place for software deployment – for code promotion. Even if no one but you ever promotes code, the process needs to exist and be documented. A video file on a file share will suffice. Heck, screenshots pasted into a Word document will do the trick. Something is better than nothing. You need your vacation time. All of it.

    Migrating SSIS Projects and Parameters, Part 1 is live at SQL Server Central this morning. In this article, I discuss some of the “gotchas” surrounding SSIS code promotion in the enterprise and some solutions.

    Kevin Hazzard (Blog | @KevinHazzard) and I built SSIS Catalog Compare to help. We’ve built other tools, too, but we’ll talk about those in other posts. Do you have 3 minutes? Go to the SSIS Catalog Compare page and check out the 3-Minute Drill video… especially if you’ve promoted an SSIS project from one Catalog to another. I would love your feedback.

    :{>

    Learn more:

    Related Training:
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Articles:
    Stairway to Integration Services
    SSIS 2016 Administration: Create the SSIS Catalog
    Announcing SSIS Catalog Reports v0.1
    SSIS Framework Community Edition (a free, open-source, SSIS Catalog-integrated execution framework)
    Stairway to Biml

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

  • SSIS 2016: Building and Deploying a Simple Test Project

    All software is tested. Some, intentionally. – Andy, circa 2010

    I test software a lot. Why? Because all software is tested. Some software is tested intentionally. Some is tested by your soon-to-be-former-largest-customer.

    In this post I will show you how to build and deploy a very simple SSIS 2016 project and deploy it. Cool? Let’s get started!

    Open SQL Server Data Tools or Visual Studio 2015. I have a copy of Visual Studio 2015 Enterprise installed on my Windows Server 2016 Preview 5 virtual machine (named vmWin16_5). To open it, click the Window (Start) button in the lower left corner and type Visual Studio:

    Cat2016_1_0

    Click Visual Studio 2015 to open the Integration Development Environment (IDE):

    Cat2016_1_1

    If the Start Page doesn’t appear as shown above, you can click View—>Start Page to open it:

    Cat2016_1_2

    The Start Page is a good place to start (pun intended). You can open recent projects, open non-recent projects, and create new projects. We’re going to create a new project. To do that, click the New Project… link on the Start Page:

    Cat2016_1_3

    When the New Project window displays, it will appear similar to that shown below. Your version may look different from my version pictured below, depending on which templates you have installed. Visual Studio is an IDE (Integrated Development Environment), as I mentioned earlier. Each product installs templates for developers to use, and the template we want to use – Integration Services Project – is found under Installed—>Templates—>Business Intelligence—>Integration Services (click to view larger image):

    Cat2016_1_4

    Type a name for the project in the Name textbox. Optionally, type or select (using the Browse button) a location for the Integration Services project:

    Cat2016_1_5

    When the project is created it will appear similar to that shown below:

    Cat2016_1_6

    Note Solution Explorer contains a single SSIS package in the SSIS Packages node, and it is named “Package.dtsx”:

    Cat2016_1_7

    You can test it in the debugger by clicking the Start button:

    Cat2016_1_8

    You can click Debug—>Start Debugging or press the F5 key:

    Cat2016_1_9

    Believe it or not, this empty SSIS package will execute:

    Cat2016_1_10

    Let’s add a Script Task. First, click the SSIS Toolbox icon in the upper right corner of the package surface:

    Cat2016_1_11

    Once the SSIS Toolbox opens, click a drag a Script Task onto the Control Flow surface:

    Cat2016_1_12

    Double-click the Script Task to open the editor:

    When the Script Task Editor opens, it will appear similar to that shown here:

    Cat2016_1_13

    Let’s add a ReadOnlyVariable. Click the ellipsis in the ReadOnlyVariable value textbox:

    Cat2016_1_14

    When the Select Variables window displays, scroll until you find the System::PackageName variable. Check the checkbox for this variable:

    Cat2016_1_15

    When the System::PackageName variable has been selected, click the OK button to close the Select Variables window. The Script Task Editor now shows the variable has been added to the ReadOnlyVariables property:

    Cat2016_1_16

    Click the Edit Script button to open the Visual Studio Tools for Applications (VSTA) Script Editor:

    Cat2016_1_17

    The VSTA editor takes several seconds to open but when it does, it appears similar to that shown below (click to view an enlarged image):

    Cat2016_1_18

    Find the public void Main() method and replace the commented text “// TODO: Add your code here” with the code shown below:

    Cat2016_1_19

    string packageName = Dts.Variables["System::PackageName"].Value.ToString();
    string msg = "I am " + packageName;
    bool fireAgain = true;

    Dts.Events.FireInformation(1001, packageName, msg, "", 0, ref fireAgain);

    Close the VSTA editor and then click the OK button on the Script Task Editor to close it as well. Your Script Task should appear similar to that shown here:

    Cat2016_20

    Press F5 or click the Start button to execute the package in the SSIS debugger. It should succeed…

    Cat2016_1_21

    … and you should see the message “I am Package” on the Progress tab:

    Cat2016_1_22

    Deploy the Project

    To deploy the SSIS Project, right-click the project name in Solution Explorer and click Deploy:

    Cat2016_1_23

    The Integration Services Deployment Wizard displays. If this is the first time you’ve deployed an SSIS package, the Introduction page will display first:

    Cat2016_1_24

    Click the Next button to select a Destination SSIS Catalog. Enter a SQL Server Instance that hosts an SSIS Catalog (SSIS 2016 Administration: Create the SSIS Catalog).

    Cat2016_1_25

    Click the Browse button beside the Path textbox to open the Browse for Folder or Project dialog:

    Cat2016_1_26

    When the Browse for Folder or Project dialog displays, select a Catalog folder to deploy the project:

    Cat2016_1_27

    You can learn more about the SSIS Catalog by reading Deployment and Execution – Level 18 of the Stairway to Integration Services.

    When the Create New Folder window displays, enter a Name for the Catalog Folder and (optionally) a Description. I named my Catalog folder “Test” but you can name yours whatever you’d like:

    Cat2016_1_28

    Click the OK button to create the Catalog Folder and return to the Browse for Folder or Project dialog:  Cat2016_1_29

    Click the OK button to select the newly-created Catalog folder and return to the Integration Services Deployment Wizard:

    Cat2016_1_30

    Click the Next button to proceed to the Review page:

    Cat2016_1_31

    Click the Deploy button to begin the SSIS project deployment:

    Cat2016_1_32

    Once the deployment is complete, click the Close button. View the deployment by opening SQL Server management Studio (SSMS):

    Cat2016_1_33

    Connect to your SQL Server instance that hosts the SSIS Catalog you used to deploy your SSIS project:

    Cat2016_1_34

    SSMS Object Explorer, once connected, will appear similar to that shown below:

    Cat2016_1_35

    Expand Integration Services Catalogs—>SSISDB—><Folder Name>—>Projects—>SSIS Test Project—>Packages. There’s your SSIS Package!

    Cat2016_1_36

    Conclusion

    I hope this basic walk-through helps you get started using SSIS 2016, a free component of SQL Server 2016. Remember, you can get SQL Server 2016 Developer Edition for free!

    :{>

    Learn more:

    Related Training
    IESSIS2: Immersion Event on Advanced SQL Server Integration Services

    Related Posts
    SSIS 2016 Administration: Create the SSIS Catalog
    SQL Server 2016 Developer Edition is Free
    Converting an SSIS 2014 Solution to SSIS 2016
    Installing SQL Server 2016 Developer Edition, One Example
    Deploying a Single Package First in SSIS 2016
    Stairway to Integration Services

    Related Books
    SSIS Design Patterns

    Help!
    Enterprise Data & Analytics – We are here to help.

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

Privacy Statement