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.

  • 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.

  • SSIS 2016 Administration: Create the SSIS Catalog

    Creating an instance of the SSIS 2016 Catalog is identical to creating an SSIS Catalog in SQL Server 2012 or SQL Server 2014. Follow these steps to create an instance of the SSIS Catalog on SQL Server 2016.

    Open SQL Server Management Studio (SSMS) and connect to an instance of SQL Server. Once connected, open Object Explorer (if it is not already open) and right-click the Integration Services Catalogs node. Click Create Catalog…:

    Cat2016_0_0

    When the Create Catalog window displays (for the first time) it will appear as shown here:

    Cat2016_0_1

    Check the “Enable CLR Integration” checkbox to enable the other controls on the form.

    I recommend you also check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox. This feature causes a stored procedure to execute whenever SQL Server starts. The stored procedure will identify any SSIS packages in a running (or other “active”) status and mark them as “Ended Unexpectedly.” You want this. Trust me. (As my friend Kevin Boles (LinkedIn | @thesqlguru) says, “Push the trust me button and let’s move on,” (paraphrased).

    You cannot alter the name of the SSIS Catalog database. It is SSISDB. And, as in Highlander, there can be only one SSIS Catalog per instance of SQL Server.

    You must supply a password for encryption used in the SSISDB database. The SSIS Catalog encrypts SSIS packages using a rough equivalent of what we used to call “Server Storage” package protection.

    <throwback> Using Server Storage encryption was perhaps the most (perhaps the only) valid use case for deploying SSIS packages to the MSDB database, back in the day.</throwback>

    The SSIS Catalog encrypts passwords and values marked Sensitive (such as SSIS Package and SSIS Project parameters).

    SQL Server 2016 requires you supply a strong password for the SSISDB database. This is a default in SQL Server 2016. Even if I knew how to defeat it, I wouldn’t tell you. It’s 2016 people. Use strong passwords or risk being pwn’d by the bad guys!

    Once configured, the Create Catalog window will appear as shown below. Click the OK button to create an SSIS Catalog:

    Cat2016_0_2

    What happens when you press the OK button? The SSISDB database is restored. Since I deployed SQL Server 2016 to the E: drive on my Windows Server 2016 Preview 5 virtual machine, the backup file is located at E:\Program Files\Microsoft SQL Server\130\DTS\Binn\SSISDBBackup.bak for me.

    Cat2016_0_3

    The SSISDB Catalog node will appear under the Integration Services Catalogs node in Object Explorer.

    We’re not there yet, but you can find helpful utility applications for Enterprise Data Integration at DILMSuite.com (Data Integration Lifecycle Management Suite).

    You will need to refresh the Databases nod in Object Explorer to see the SSISDB database:

    Cat2016_0_4

    What’s in the SSISDB database? Well, there are tables…

    Cat2016_0_5

    … views…

    Cat2016_0_6

    … and stored procedures…

    Cat2016_0_7

    … and functions and other database-y stuff. It’s just a database. That’s important to remember. It needs the same care and feeding as the other databases in your enterprise. It’s a neat piece of work, but it’s still just a database. It may not scale well for you in your enterprise. You may need to tune it. You definitely need to back it up. You need to store the encryption keys somewhere, preferably somewhere safe and not on the same physical server. Restoring SSISDB needs to be part of your disaster recovery (DR) planning and DR tests.

    You are doing DR tests, aren’t you? Regularly?

    I hope this post helps you get an SSIS Catalog up and running in SQL Server 2016. If you have any questions, please leave them in the (moderated) comments below or email me using the link in the upper right section of this page.

    Enterprise Data & Analytics can help you put SSIS, data warehousing, business intelligence, and analytics to work for your enterprise. We offer:

    • training;
    • evaluation of your enterprise data integration, architecture, and best practices;
    • and consulting.

    We are here to help. Contact us today!

    :{>

    Learn more:

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

    Related Articles:
    Converting an SSIS 2014 Solution to SSIS 2016
    Installing SQL Server 2016 Developer Edition, One Example
    SQL Server 2016 Developer Edition is Free
    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.

  • PASS Provides an Updated Speaker Contract

    The PASS Board has provided an updated Speaker Contract prior to announcing Summit speakers at the end of this month. In his post on the PASS Blog titled Improving the Speaker Contract, PASS president Adam Jorgensen provides a link to a PDF of the new contract and invites comments.

    Adam notes clauses 14, 15, and 16; shown here:

    New PASSSpeakerContract14-16

    My thoughts:

    • First: Kudos to PASS Leadership for responding in such a timely manner!
    • Item 14 – especially the last sentence – allows presenters to build their brands while increasing the value of their contributions to both the Community and the PASS Summit, provided the scripts and tools are free and do not require registration or email.
    • Item 15 bans sharing branded stuff “outside of your designated session room(s)” and permits “one-on-one” exchanges that occur as a result of normal networking.
    • Item 16 is polite housekeeping. If you put stuff (that’s allowed by item 14) out, please clean it up.

    In my opinion, this is a set of compromises that seeks to balance concerns expressed by the Community with concerns expressed by Summit exhibitors. Some things to note:

    • Free tools or scripts need to be available somewhere where there is no registration or email required. I’m not sure how this applies to open source projects – like those hosted at github. I’m pretty sure people need to register to join an open source project hosted at github. I wonder about that, especially since item 14 specifically mentions “Open Source” in its language. My suggestion: Remove the registration/email restriction language from item 14. Some platforms require (or will require in the future) registration of some kind. Presenters have no control over the policies of these platforms. I think this is going to be difficult for PASS (or anyone, although I am certain some will try) to police and impossible to enforce with equity. In my opinion, this is a recipe for more complaints.
    • I think I understand the balance PASS is trying to achieve. I think they’re saying, “If you want to collect email addresses, sponsor.” I have mixed emotions about this limitation on personal brand-building (see Adam Machanic’s first comment on this post), but I believe this is an effort to preserve the value of sponsoring for both PASS and the sponsors.
    • In my opinion, item 16 definitely leans towards presenter brand-building, and therefore towards the Community. It also benefits sponsors by making the Summit more valuable, increasing the value of their sponsorship dollar.

    All in all, I am impressed with PASS’ leadership on this issue. Kudos!

    :{>

  • Converting an SSIS 2014 Solution to SSIS 2016

    Microsoft SQL Server Data Tools (SSDT) supports “deployment targets.” This means you can use the same Integrated Development Environment (IDE) to build SSIS packages targeted for deployment to the SSIS Catalog on a SQL Server 2012, 2014, or 2016 instance of SQL Server. When I open an SSIS solution built in SSDT-BI for SQL Server 2014…

    Upgr2016_0

    …the project is opened as a SQL Server 2014 project:

    Upgr2016_2

    Note the “(SQL Server 2014)” beside the project name in Solution Explorer.

    If I want to deploy this project to an SSIS Catalog on a SQL Server 2016 instance, I should update the project to SSIS 2016. How do I do this? In Solution Explorer, right-click the project name and click Properties:

    Upgr2016_3

    Navigate to the Configuration Properties\General page. Click the TargetServerVersion dropdown and select SQL Server 2016:

    Upgr2016_4

    When you click the Apply button, an informative dialog displays and asks if you “want to make the change now?” Click the Yes button to change the target SSIS Catalog version to SQL Server 2016:

    Upgr2016_5

    Another dialog prompts you to save the changes. Click the Yes button to save the changes you just made:

    Upgr2016_6

    Once the changes are complete, click the OK button to return to Solution Explorer in SSDT:

    Upgr2016_7

    No more parenthetical version metadata, your project has been targeted for SQL Server 2016:

    Upgr2016_8

    :{>

    Learn more!

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

    Related Articles:
    Installing SQL Server 2016 Developer Edition, One Example
    SQL Server 2016 Developer Edition is Free
    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.

  • Installing SQL Server 2016 Developer Edition, One Example

    There are lots of options when installing SQL Server 2016 Developer Edition. I am not going to exhaust them. I will, however, share how I set up SQL Server 2016 to begin learning about the relational engine, SSIS, and SSRS.

    First, you need SQL Server 2016 Developer Edition. I blogged about one way to obtain SQL Server 2016 Developer Edition in the post SQL Server 2016 Developer Edition is Free.

    Once you have SQL Server 2016 Developer Edition, start the installation by executing setup.exe. SQL Server Installation Center starts:

    Ins201600

    Click the Installation page:

    Ins201601

    Click the link labeled “New SQL Server stand-alone installation or add features to an existing installation”:

    Ins201603

    Accept the defaults on the Product Key page – Developer Edition and no product key, then click the Next button:

    Ins201604

    On the License Terms page check the “I accept the license terms” checkbox and click the Next button:

    Ins201605

    If the Install Rules page tests pass, click the Next button to proceed:

    Ins201606

    On the Feature Selection page, select the features you want to install and a location for the instance root files. I installed the Database Engine Services, Reporting Services – Native, Integration Services, and Client Connectivity. Click the Next button to proceed:

    Ins201607

    On the Instance Configuration page I opted to install a default instance and clicked the Next button:

    Ins201608

    On the Server Configuration page, I accepted the defaults for services and startup types:

    Ins201609

    On the Database Engine Configuration page, I opted to use Mixed Mode, supplied a strong password, and added the current user to the SQL Server Administrators group.

    Ins201610

    On the Reporting Services Configuration page, accept the default (Install and configure), then click the Next button:

    Ins201611

    On the Feature Configuration Rules page, you may encounter an error like I did above. I am re-purposing a virtual machine on which I’d installed SQL Server 2016 RC3. The SSRS database files remained even after I uninstalled RC3. I deleted the ReportServer and ReportServerTemp database and log files and clicked the Re-run button:

    Ins201602

    The Ready to Install page displayed; I clicked the install button to begin the installation.

    Ins201613

    Once the installation completed, I clicked the “Install SQL Server Management Tools” link on the SQL Server Installation Center’s Installation page:

    Ins201614

    The link takes me to a page titled Download SQL Server Management Studio. It contains a link to install SSMS for the GA release:

    Ins201615

    Once the download is complete, click the Run button to install SSMS 2016:

    Ins201617

    The SQL Server Management Studio (SSMS) installation starts:

    Ins201618

    Once SSMS is installed, click the “Install SQL Server Data Tools” link:

    Ins201619

    Clicking the link opens a page named “Download SQL Server Data Tools (SSDT)”:

    Ins201620

    Click the link named “Download SQL Server Data Tools for Visual Studio 2015”:

    Ins201621

    I read through this page and tried to walk through the steps described in step 2 above. I opened Visual Studio 2015 Preview…

    Ins201622

    …I opened Tools—>Extensions and Updates. But I did not see a listing for SSDT 2015.

    Ins201623

    So I returned to the web page and downloaded the SSDT ISO image file:

    Ins201624

    That worked. I started the SSDT 2015 installation…

    Ins201625

    … and installed SSDT 2015:

    Ins201626

    That was it! Now I’m ready to learn more about SQL Server 2016!

    :{>

    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.

  • SQL Server 2016 Developer Edition is Free

    You can now download SQL Server 2016 Developer Edition – which is Enterprise Edition functionality with a license that forbids using the platform for Production purposes – for free. How? Join Visual Studio Dev Essentials.

    JoinVisualStudioDevEssentials

    Once you join (for free!) you get access to lots of benefits – not just free SQL Server 2016 Developer:

    MyVisualStudioDevEssentials

    Click the Downloads link at the top of the page and search for SQL Server 2016:

    FreeSQLServer2016Dev

    Click the SQL Server 2016 Developer link to open the download options:

    DownloadSQLServer2016Dev

    Now you can begin learning what’s new in SQL Server 2016. For free!

    :{>

    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.

  • So You Want to Learn More About Biml?

    Biml is growing in popularity and it’s so cool to be a part of this awesome and growing community! This is not an exhaustive list, but here are some places you can go to learn and read more about Biml:

    Resources

    Bloggers

    Notes: I compiled this list from memory and by searching for blogs that have posted more than once about Biml. Plus, stuff I just found interesting and Biml-related. Bloggers are listed in alphabetical order.

    If you blog about Biml or know of a Biml resource I left off this list, please email me using the link to the right. My intention is to edit this post to keep it relevant as our Biml Community grows!

    :{>

  • Proper Use of Social Media

    unfollowAndy

    This has come up a couple times (as in twice) over the past couple years, so I thought I’d put the question to my loyal readers (both of you, Hi Mom!) and see what you think.

    What’s the proper way to use social media?

    I don’t want to limit the conversation to technical people; I don’t want to limit it at all. I’m curious to learn your thoughts.

    Most people I know use social media however they please. Some use it for sharing personal thoughts. Some business-y stuff. Some do both. Some do both on separate accounts; some do personal and business posting on the same account. Many people schedule social media posts of some kind (I do – I use Hootsuite for this). Everyone I follow on social media participates randomly; there are periods when they are more active and other times they are less active. Sometimes their activity is impacted by things like vacations or family emergencies. Sometimes, it’s just that they prefer to do more or less on social media. I believe everyone does whatever they do for a reason.

    Are those (or other) reasons valid? Do you think it’s acceptable for people to post on social media as seldom or as often as they’d like? Is someone’s consistency or frequency for posting on social media valid grounds for criticism? I don’t think so.

    I’m curious to hear your feedback.

    :{>

More Posts Next page »

This Blog

Syndication

News


My Companies



Community Awards

Friend of Red Gate

Contact Me

Archives

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