“Andy, I Want More ____”

I am always learning.

I enjoy learning and this trait led me to technology in the first place. It turns out a love of learning is all but essential for technologists.

After I learn, I share what I’ve learned in blog posts, webinars, and via presentations at SQL Saturdays and events like the PASS Summit.

What Do You Want to Learn?

Drop me a comment and let me know what you’d like to learn! I may take your suggestion and run with it.

:{>

The Recording for Troubleshooting SSIS (Part 1) is Available

A fantastic crowd attended today’s (13 Mar 2019) free webinar titled Troubleshooting SSIS. If you attended you got the full show. If not, you may view the content at SSIS Academy for a nominal fee.

Enjoy this focused content to SSIS Self-Help, a service from Enterprise Data & Analytics designed to help folks quickly and affordably overcome SSIS challenges.

Preview Available

The first two segments of the presentation are available for free at the top of this post. In the full version I walk you through:

  • Design-time validation
  • Debugging most SSIS tasks
  • Debugging the Script Task
  • Debugging the SSIS Data Flow Task
    • Using Data Taps
  • Debugging the Script Component

Enjoy.

:{>

Andy’s Database Credentials Hack

Ok, this isn’t a hack. It’s more of a tip that may help you protect data. Some of you would not have clicked a link to Andy’s Database Credentials Tip. You are welcome.

LastPass

I like LastPass. I pay for Premium and it’s not a bad deal, in my humble opinion.

I also like Azure – a lot. As a Microsoft Data Platform MVP, I get a free subscription to Azure. But there was a time when I paid for my Azure subscription. It was worth it, in my humble opinion.

Provisioning an Azure SQL DB

When it’s time to provision an Azure SQL DB, I sometimes configure a username and password. (You can also use Azure Active Directory to connect to an Azure SQL DB instance).

Back in the day, I would use some generic – but easy to guess – name for the Administrator account. Something like “andy,” for example.

The LastPass Chrome extension includes a Generate Secure Password option:

I use this to generate the Server Admin Login value:

(click to enlarge)

My thinking? Why give away half the key-value pair for logins?

Conclusion

LastPass isn’t the only way to generate a random string. I leverage it because, well, it’s right there in my browser already.

I cannot provide metrics for how often this trick has thwarted would-be attacks. I also do not know if this even slows bad guys down these days. But I’ve been doing this for a while and – if nothing else – it gives me a little peace of mind.

:{>

SQL Saturday 813 – Boston #SSIS Precon 29 Mar 2019!

I’m honored to deliver Intelligent Data Integration with SSIS – a full-day pre-conference session – at SQL Saturday 813 in Boston 29 Mar 2019!

What is Intelligent Data Integration?
SSIS (SQL Server Integration Services) packages developed using tried and true design patterns, built to participate in a DevOps enterprise practicing DILM (Data Integration Lifecycle Management), produced using Biml (Business Intelligence Markup Language) and executed using an SSIS Framework.
Attend a day of training focused on intelligent data integration delivered by an experienced SSIS consultant who has also led an enterprise team of several ETL developers during multiple projects that spanned 2.5 years. And delivered.
Attendees will learn:

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

Topics include:

  1. SSIS Design Patterns
  2. Executing SSIS in the Enterprise
  3. Custom SSIS Execution Frameworks
  4. DevOps and SSIS
  5. Biml, Biml Frameworks, and Tools

Register today!

I hope to see you there!

:{>

Enterprise Data & Analytics Welcomes Eugene Meidinger!

I am honored and thrilled to welcome Eugene Meidinger (@SQLGene | blog | LinkedIn | Pluralsight) to the Enterprise Data & Analytics team! Eugene is self-described as a Business Intelligence Consultant but I think he’s being modest (in my humble opinion… see what I did there?).

Eugene has authored three Pluralsight courses on the topics Power BI, DAX, Power Pivot, and SSAS. His experience includes loading ERP solutions, Power Reports, ETL, QlikView, and SQL Server development.

Eugene is a regular community presenter at SQL Saturdays and delivered a great session titled Introduction to Power Query (The M Language) ranked one the best Data Analytics sessions of the PASS Summit 2017:

Eugene is active in the SQL Server Community, serving as a co-organizer of the Pittsburgh Power BI User Group.

SQLGene is Internet-Famous!

Here’s another video of Eugene and Bert Wagner (@bertwagner) – #SQLChefs:

I continue to be humbled and thankful and excited to watch Enterprise Data & Analytics (quietly) grow – adding cool people like Eugene and Shannon and service offerings like SSIS Self-Help. It’s very cool to watch!

Welcome Eugene! I am honored to work with you, my brother and friend.

For more information, please contact Enterprise Data & Analytics!

SSIS Project Connection Manager Naming Error

I am sometimes asked, “Andy, how do I start a technical blog?” I demonstrate one way here – a way that has served me well with search engines.
1. Describe how the error occurred.
2. Copy the error message, post it as text.  
3. Describe the solution.

I am helping a large enterprise client migrate their data integration from another data integration platform to Azure-SSIS. It’s a two-step process:

Step 1: Convert the existing data integration operations to SSIS and test the conversion by deploying to an on-premises SSIS Catalog.

Step 2: Lift and shift the SSIS Catalog configurations from on-premises to Azure-SSIS (we will be using SSIS Catalog Compare for this part).

An SSIS Error

In the midst of designing SSIS solutions we encountered an interesting SSIS error:

Failed to start project

Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)

As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:

“Whut?!”

I bingled (credit for the term, “Bingle”: Ed Danger Watson) and found nothing helpful. I was beginning to think something unfortunate had happened to the client’s SQL Server Data Tools SSIS template when a crazy idea popped into my noggin. I said to myself:

Self? Could it be the odd-looking name of the Project Connection Manager?

Andy – circa 2019

The Odd-Looking Name of the Project Connection Manager

When you create a new SSIS Connection Manager, SSIS automagically names it for you. Awesome, right? Most of the time. But sometimes the default name created for a package connection manager doesn’t play nice with Project Connection naming requirements.

Don’t believe me? Try this:

Create a new SSIS package.
Add a package connection manager aimed at a named instance of SQL Server, such as “vDemo\Dev”:

Next, (attempt to) promote the package connection manager to a project connection manager:

Note the error:

The error message in this case reads:
Failed to convert the package connection mananager ‘vDemo\Dev.WeatherData’ to a project connection manager because ‘vDemo\Dev.WeatherData’ is not a valid stream name in the project. Rename the package connection manager and do the conversion again.
Object name ‘vDemo\Dev.WeatherData.conmgr’ is not valid. Name must contain only alphanumeric characters or underscores ‘_’.

The backslash – “\” – in the connection manager name – which works just fine for the Package Connection Manager – blocks promotion of the package connection manager to a Project Connection Manager.

Bummer.

Back to the problem at hand…

My client’s Project Connection Manager name included square brackets (it’s an Oracle thing…). Renaming the project connection manager – simply removing the square brackets – cleared the error:

The package now executes in the SSIS debugger:

SSIS is a complex and flexible software development platform. I learn new things with each and every consulting project. Why did I think of the connection manager name? In testing SSIS Catalog Compare version 4, I tried out a bunch of naming combinations. I was shocked to learn some characters are permitted (did you know you can create an SSIS Catalog Folder name that contains an apostrophe??).

Happy Integrating!

:{>

Start Azure-SSIS, Execute Package, Stop Azure-SSIS

Last August, I had a blast at the Data Platform Summit 2018 in Bengaluru, India. One highlight of the trip was meeting and conversing with the Azure Data Factory SSIS Integration Runtime Product/Program Manager, Sandy Winarko.

Sandy and I swapped coder stories and talked about data integration design patterns and I learned a few new design patterns sitting in his session. This post is about one of the patterns I learned from Sandy.

Before I move on, though, you should really check out the Data Platform Summit! It is an awesome show – Asia’s largest Microsoft data conference.

Stop In The Name Of… Money

I love Azure! I love the technology and the velocity of development. The cloud is changing the world and Azure is changing the cloud – almost every day. One cool thing about Azure is cost savings. A lot services are free or nearly free. That. is. Awesome! But some services are not free.  (That’s not a bad thing!)

Azure Data Factory’s SSIS Integration Runtime (aka Azure-SSIS IR) is not free. It costs money even when it’s just sitting there switched on with no SSIS packages executing. Depending on the size of the Azure-SSIS IR, one can wrack up some serious charges for what amounts to, well, no work.

That’s why I was excited to learn about this pattern from Sandy. The pattern is made up of three parts:

  1. Start Azure-SSIS
  2. Execute an SSIS package
  3. Stop Azure-SSIS

Sandy, Nick SchonningDouglas Laudenschlager, and Sreedhar Pelluru authored a post at Microsoft Docs titled How to start and stop Azure-SSIS Integration Runtime on a schedule. When I tried to follow the instructions I encountered one issue. Now, you may read that last sentence and think, “Andy, don’t you have a problem following instructions anyway?” Yes, but that’s beside the point… mostly.

Their article walks one through setting up an instance of Azure-SSIS. It then proceeds to describe how to build the start-execute-stop pattern Sandy described to me (and everyone who attended his presentation) in Bengaluru. Now this pattern has evolved since Sandy first described it. The first version used Azure Runbooks and Webhooks. I like this version much better. You can still use Runbooks and there’s more information in the How to start and stop Azure-SSIS Integration Runtime on a schedule article.

Before You Start

I’m going to share the part that gave me trouble first – it was security. In the article, Sandy and company write in Step 5 of the Create Your Pipelines section:

Assign the managed identity for your ADF a Contributor role to itself, so Web activities in its pipelines can call REST API to start/stop Azure-SSIS IRs provisioned in it.

I read that and proceeded to assign myself to the Contributor role of Azure Data Factory. That didn’t work. Not at all. And that’s not what the instructions said. The instructions said to “Assign the managed identity for your ADF a Contributor role to itself…” 

What’s a Managed Identity?

Well, I searched for the answer and found this spiffy article titled Azure Data Factory service identity that not only explained what an ADF Managed Identity was, it also told me where to find it

See, when I browsed over to assign the ADF Contributor Role, all I saw in the available list of people / accounts I could assign was li’l ol’ me:

So that’s who I assigned to the Contributor Role: me. As I said, that did not work.

The article titled Azure Data Factory service identity took me right to the location of the ADF Service Identity Application ID, which was the value I needed: 

After I figured this out – with awesome and patient help from Sandy himself (thanks Sandy!) – I was in business.

Stop It!

I created a separate pipeline to stop Azure-SSIS. I run it every night at 11:00 PM ET because if I’m awake at that time (I usually rise around 4:00 AM ET), I am not being productive on my computer (if I am on it at all…). I do this in case I forget and leave Azure-SSIS running after a presentation, class, or client demo.

Following the instructions, I created a new pipeline and added a Web Activity. I configured the Web Activity according to the instructions. Mine looked like this:

(click to enlarge)

The tricksy part (if there is one) is configuring the URL. There’s a shortcut for that. Click on the Monitor page of the ADF portal:

Click the Integration Runtimes tab at the top of the page, and then click the “View Integration Runtime Detail” link under the Actions column to bring up the snappy new mini-dashboard for Azure-SSIS:

Regardless of the state of Azure-SSIS, click the status link to open a functional  pane that contains the Resource ID and a Copy button:

This copies a lot of what you will need to set up the call to stop Azure-SSIS (and also to start it): 

/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.DataFactory/factories/{factoryName}/integrationRuntimes/{integrationRuntimeName}

When you copy the Resource ID, you get all those missing values filled in for you. How neat is that? Prefix that with “https://management.azure.com” and add the suffix “/stop?api-version=2018-06-01”, and you are golden.

Finish following the instructions in the Create Your Pipelines section, and then take it out for a test drive.

Clone

Things begin to pick up here partly because you’ve done a lot more work than you realize already, and partly because supper’s ready and I’ve been blogging for the past couple hours.

(For you, Dear Reader! I do it all for You!) And for me. In case I forget…

Click the ellipsis beside the pipeline and then click Clone to make a fresh copy of your pipeline (it still has that new pipeline smell!)

I name my new clone Start Run Stop.

The Stop AndyDemoSSIS Web Activity is in there already and just begging to be cloned.

Look how easy it is to clone an activity in ADF. I mean, could they make this any easier? I think not.

Once its cloned, it’s not much work to edit the configuration of the cloned Web Activity to start the AndyDemo Azure-SSIS instance:

Boom. That just happened.

“Run-Time!” (Spoken in the Voice of the Geico Camel)

Next up? The Execute SSIS Package Activity:

I’m not going to write much about how to configure the Execute SSIS Package Activity. You can learn more in one of these blog posts where I’ve already written about it. Supper’s waiting.

Conclusion

Thanks to Sandy and the team at Microsoft for the excellent documentation. This is a very cool Azure Data factory Design Pattern!

Enjoy!

SSIS Catalog Compare Version 4 – Three Videos

I am excited to announce general availability of SSIS Catalog Compare version 4!

I made three short-ish (~2 minutes each) videos demonstrating some of the capabilities of the product. I really like the SCCPAC files and SCCPAC Deployment Wizard, which are only available in the Enterprise Edition.

Use SCCPAC Deployment Wizard to Lift and Shift SSIS Projects and Configurations Metadata to Azure-SSIS

Use SCCPAC Deployment Wizard to Manage SSIS DevOps

Use SSIS Catalog Compare Enterprise Edition and SCCPAC Deployment Wizard to Deploy from the QA to Production Catalog in Real-Time

Enjoy!

:{>

Grant SSIS Catalog Read-Only Permissions

The SSIS Catalog has built-in security to manage permissions. SSISDB – the database behind the SSIS Catalog – is “just a database” in many respects. When it comes to security, the SSIS Catalog surfaces an internal mechanism that provides granular permissions management. 
In this post I intend to demonstrate how to use SSIS Catalog security to provide read-only access to SSIS Catalog artifacts. But first…

Two Thoughts About SSISDB Roles

Thought 1: “Help us DBAs, you’re our only hope.” – Princess Leia (paraphrased)

If you work with SSIS you already know the Microsoft team of technical writers is comprised of artists, masters of the field of technical writing. I’m convinced a large part of the successful adoption of SSIS is due these good people. You can see a sample of their outstanding artistry in the article titled Integration Services Roles (SSIS Service).

Two important roles in the SSIS Catalog are ssis_admin and ssis_logreader. According to the article linked above:

ssis_admin. This role provides full administrative access to the SSIS Catalog database.

ssis_logreader This role provides permissions to access all the views related SSISDB operational logs.

SSIS_admin and ssis_logreader are SQL Server database roles. As such, they are typically set and maintained by Database Administrators (DBAs).

Thought 2: Although SSISDB is a SQL Server database, it’s more like an application coded in T-SQL.

One for-instance, for instance, is the SSIS Catalog requires Windows authentication for most administrative activities. It took me a while to understand why Windows authentication is necessary. I now get it, but the explanation is for another post. This has implications, such as:

  • SQL Logins – even sysadmins such as sa – cannot deploy SSIS projects to an SSIS Catalog. Or execute SSIS packages.
    • One exception: an Azure-SSIS SSISDB database hosted on an instance of Azure SQL DB can perform SSIS Catalog administration using a SQL Login.

In sum, the SSIS Catalog is a database application that requires Windows authentication for administrative tasks.

Null Use Case: No Access

When a user has no access to SSIS Catalog artifacts, the SSMS Object Explorer Integration Services Catalogs node appears as shown here:

SSIS Catalog Browser presents a unified view of all SSIS Catalog artifacts (and it’s free!).

All SSIS Catalog-related products and utilities at DILM Suite respect SSIS Catalog security. Early versions of SSIS Catalog Compare allowed users to login with SQL Server Login credentials and access SSIS Catalog artifacts that SSIS Catalog security would block. A couple years ago I refactored CatalogBase – the object beneath DILM Suite’s SSIS Catalog products and utilities that interacts with the SSIS Catalog – to respect SSIS Catalog security.

A user with no access will see an empty SSIS Catalog using SSIS Catalog Browser:

Grant Read-Only Access to Folders

In SSMS, right-click the SSIS Catalog Folder you wish to surface for a 
Windows authentication-based SQL Server login:

When the Folder Properties window displays, click the Permissions page. On the Permissions page, click the Browse button to select one or more Windows authentication logins:

The Browse All Principals dialog displays. Select one or more Windows authentication logins (Windows User type):

To assign read-only permission to the SSIS Catalog Folder, click the OK button on the Browse All Principals dialog and check the Grant checkbox for the Read permission in the Folder Properties window:

The user(s) you selected are now able to view the SSIS Catalog folder using the SSMS Object Explorer’s Integration Services Catalogs node:

Similarly, SSIS Catalog Browser will display the SSIS Catalog folder for the assigned user(s);

“Where’s the Beef?”

I can hear you thinking, “Where are the projects and environments, Andy?” That is an excellent question. I’m glad you asked! They exist, of course, but we only granted the user Read permission for the SSIS Catalog Folder.

Grant Read-Only Access to Folder Artifacts

To see SSIS Projects and SSIS Catalog Environments, a user must be granted Read Objects permission on the SSIS Catalog Folder:

Now users can view SSIS Catalog folder contents using SSMS:

SSIS Catalog Browser users enjoy a similar view:

Conclusion

The SSIS Catalog surfaces a rich and powerful security model that may be leveraged by enterprise data integration DevOps teams using SSMS or SSIS Catalog Browser.

Want to learn more? From me? Join me for the next delivery of Expert SSIS – a 2-day, live, remote SSIS training event! 

Two ADF Presentations Next Week!

I am honored to deliver Moving Data with Azure Data Factory to two User Groups next week!

Abstract

Azure Data Factory – ADF – is a cloud data engineering solution. ADF version 2 sports a snappy web GUI (graphical user interface) and supports the SSIS Integration Runtime (IR) – or “SSIS in the Cloud.”

Attend this session to learn:

How to build a “native ADF” pipeline;
How to lift and shift SSIS to the Azure Data Factory integration Runtime; and
ADF Design Patterns to execute and monitor pipelines and packages.

Dates and Locations

07 Jan 2019 – 6:00 PM ET

Columbus Georgia SQL Server Users Group
Meeting at the Columbus Public Library
3000 Macon Rd
Columbus, Georgia 31906

Register today!

08 Jan 2019 – 5:30 PM ET

Midlands PASS
Meeting at WeKnowIT
440 Knox Abbott Drive
Suite 424
Cayce, SC 29033

Register today!

If you live – or will be – nearby, I hope to see you!

:{>

PS – want more? Join me 04 Mar 2019 for a full-day of training! 
Learn more:
Getting Started with Azure Data Factory – Live, 1-Day Training