SSIS in Visual Studio 2019

If you’re using Visual Studio 2019 with SSIS, you may encounter an error or two when you start. One error is “incompatible” tacked onto the name of your SSIS project in parentheses:

(incompatible)

Another error you may encounter is found in the Migration Report, a local htm file that contains information about the the attenpted Visual Studio project migration:

Some of the details may read: “The application which this project type is based on was not found.” Here’s how to fix it:

How To Fix It

Times they are a-changin’. That’s what Bob sang back in the day. It’s true for installing SQL Server Data Tools development templates in Visual Studio 2019. Here’s one way to install SQL Server Integration Services (SSIS) templates:

1. Open Visual Studio 2019

Once open, click Extensions–>Manage Extensions:

(click to enlarge)

2. Search / Download the SQL Server Integration Services Projects Extension

When the Manage Extensions window opens, type “Integration Services” in the search textbox in the upper right corner of the window. SQL Server Integration Services Projects should appear near the top of the list returned. Click SQL Server Integration Services Projects and then click the Download button:

3. Once Downloaded, Open

Open the downloaded file:

4. Follow the SQL Server Integration Services Projects Wizard

Click Next to continue:

Select your target Visual Studio 2019 installation and click the Install button:

You may get an error because Visual Studio is still open and you’re trying to update it (I did). Click the OK button, close Visual Studio, and click Install again:

The SSIS templates take a while to install. Once they’re installed you’ll see this window. Click the Close button:

5. Open or Create an SSIS Project

If you’ve attempted to open an SSIS project before, it may still open as “incompatible.” If so, right-click the project and click “Reload Project”:

Conclusion

You should now be able to develop SSIS using Visual Studio 2019!

SSIS in SSMS Version 18: Try SSIS in Azure Data Factory

There’s a new option when you right-click the Integration Services Catalogs node in SSMS v18: “Try SSIS in Azure Data Factory”:

Clicking the “Try SSIS in Azure Data Factory” option in SSMS v18 opens the (new) Integration Runtime Creation Wizard (ISIRCreationWizard.exe, which is found in the <installation drive>:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn directory):

Lift and Shift SSIS to Azure-SSIS

Before you can lift and shift SSIS to Azure-SSIS, you need an SSIS Catalog in the cloud at the time of this writing. The first page of the wizard – “SSIS in ADF Introduction” – includes links to:

SSIS in ADF Configuration

The next page in the wizard is labeled ” SSIS in ADF Configuration”. After logging into your Azure account, you may configure:

  • An Azure Subscription
  • Catalog Server Endpoint – an instance of Azure SQL DB or Azure SQL Server Managed Instance
  • Connection credentials
(click to enlarge)

I can hear you thinking, “Why the squirrel-y-looking user name, Andy?” See my post titled Andy’s Database Credentials Hack.

After clicking the Connect button, you should see an Information message declaring: “Connection is successful.”

If you don’t see the “Connection is successful” message, you will likely see a message similar to that shown below:

Oops

Click the Create button. The Integration Runtime Creation screen displays and reports progress as the wizard:

  • Creates the Resource Group
  • Creates the Azure Data Factory
  • Creates the integration Runtime

I confess: I am the curious type. When I saw this list, I had some questions. So I logged into portal.azure.com and poked around a bit.

SETTINGS

The wizard created an Azure Data Factory and a resource group based on the name of the server I used, plus a formatted timestamp string:

The wizard also created an instance of Azure-SSIS on the server I specified. One interesting note? Azure-SSIS is created but not started.

At the time of this writing, creating an instance of Azure-SSIS from the Azure Portal automatically starts the instance. I think I might like the option to start the Azure-SSIS instance manually from the portal – with maybe a “Start after created” checkbox.

Clicking the edit icon revealed the configuration SSMS v18 chose for me:

I’m pretty sure these are the defaults when one creates Azure-SSIS from the Azure Portal. Interesting to note: The “Already have a SQL Server licence?” option is set to Yes, which is not the default. This makes sense. I’m creating the instance of Azure-SSIS from SSMS v18.

Returning to the Wizard:

Clicking the Next button opens the adf.azure.com Azure Data Factory Authoring page and displays the Azure-SSIS Integration Runtime Setup blade:

Clicking the Start button begins starting the Azure-SSIS Integration Runtime:

The Azure-SSIS Integration Runtime starts in about five (5) minutes, which is an awesome improvement over the 20-30 minute startup times of the past!

The final step in the Integration Runtime Creation Wizard is to click the Close button:

One Last Thing…

When you right-click the SSMS v18 Integration Services Catalogs node in Object Explorer and then click Create Catalog, the Catalog Creation Wizard starts:

The Catalog Creation Wizard includes a new “Create SSIS IR” button:

When clicked, this button starts the Integration Runtime Creation Wizard.

Conclusion

Well played, Azure-SSIS Team. 
Well played.

Need Help Lifting and Shifting SSIS?

Enterprise Data & Analytics specializes in lifting and shifting SSIS from on-premises to Azure.

Please contact Enterprise Data & Analytics to learn more.

SSIS in SSMS Version 18

Version 18 of SQL Server Management Studio (SSMS) has been out for a few weeks now, but I just now had the opportunity to install it and tinker. There are a lot of new features!

Here’s a list of SSIS-related features:

I’m not able to address or comment on each item. I can cover a few. Click the links above for more information.

Please note I am using SSMS v18 with SQL Server 2017.

Need Help Lifting and Shifting SSIS ?

Enterprise Data & Analytics specializes in lifting and shifting SSIS from on-premises to Azure.

Please contact Enterprise Data & Analytics to learn more.

Free Webinar: Build and Load a Staging Database with Biml

The next free webinar from Enterprise Data & Analytics is titled Build and Load a Staging Database with Biml – happening 29 May 2019 at 12:00 PM EDT.

Abstract

Watch Andy Leonard, Microsoft Data Platform MVP and BimlHero, create a Biml solution that would build and load a staging database using Biml with minimal configuration.

Register today!

The Recording for “A Data Science Story” by Shannon Lowder is Available

Shannon Lowder (@shannonlowder | shannonlowder.com) did a fantastic job presenting the true story of a data science project. He presented the problem he was trying to solve, the places he looked for data, issues and obstacles he encountered, and solutions he developed. Enjoy!

Links

Learn More:

Enterprise Data & Analytics Public Course Schedule

03 Jun 2019 – Fundamentals of Azure Data Factory
11 Jun 2019 – Mastering the SSIS Catalog
17-18 Jun 2018 – Developing SSIS Data Flows with Labs 
27 Jun 2019 – Power BI: Implementing the Other 90% 
15-16 Jul 2019 – Expert SSIS 
23 July 2019 – Fundamentals of Biml
29 July – 01 August 2019 – From Zero To SSIS

Use the coupon code “ednawebinar” for a 40% discount for now, but hurry!

Screenshots!

Azure-SSIS Starts Faster

Azure-SSIS starting in 5 minutes!

I had the honor to deliver “Moving Data with Azure Data Factory at SQL Saturday – Atlanta this weekend. Because Azure changes daily, I fired up an Azure-SSIS instance Integration Runtime the morning of my presentation and walked through my demo.

I play a game I call, “What’s Changed in Azure!” It’s a fun game.

What’s Changed in Azure?

I noticed Azure-SSIS was running 12 minutes later, when I checked. That’s down from 20-30 minutes in the past.

This morning I built a brand new Azure-SSIS IR – it still has that new Azure-SSIS IR smell – and it was built, deployed, and running in five minutes.

Kudos!

Kudos to the Sandy Winarko and the Azure-SSIS Development Team!

:{>

SSIS Script Task Error

(click to enlarge)

Has this ever happened to you? You’re tooling along building an SSIS package. You configure a Script Task, press the F5 key, and BAM! Error!

DTS Script Task has encountered an exception in user code:
Project name: ST_…
Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Not Helpful

Although accurate, this is not a very helpful error. What does it mean?

This error means an SSIS variable or parameter found in the ReadOnlyVariables property or the ReadWriteVariables property of the Script Task does not match the name of a variable or parameter referenced in the .Net code.

You can learn more by taking my short (and inexpensive) course titled SSIS Self-Help!

Anders Schneiderman on Changing the World

Earlier this year, Frank (@Tableteer) and I had the honor of interviewing Anders Schneiderman (LinkedIn) on Data Driven. The episode is now live and titled Anders Schneiderman on Why We Need to Prepare Communities for the Future of Work.

Anders has a plan to help people leverage emerging technology to transform their destiny. Anders’ plan is documented in a report hosted at Makers All.

I’ve known Anders for years. We’ve worked together, even. He is one of the most conscientious people I know, and this comes through in the podcast as well as his report at Makers All.

Focusing on Appalachia…

Anders aims to first help people living in Appalachia.

The region is near and dear to my heart for many reasons. One reason is my Dad – who passed away in March – spent the last decades of his life living in Rose Hill Virginia, just a few miles from Cumberland Gap. I am familiar with the struggles of the people of Appalachia and, quite frankly, I am disappointed in our society for not doing more to help them.

Some of Appalachia’s economic wounds are self-inflicted and reinforced by a culture of honor that inoculates people from accepting help, especially “help from outside.” The solution, therefore, must be long-term and sustainable to overcome both the problems and the problems behind the problems.

Part of Anders’ Plan

One part of Ander’s plan strikes me as good and well: leveraging the Extension Agent model. The Extension Agent model was used to disseminate agricultural best practices and knowledge from agricultural colleges and studies into the fields – literally. Farmers of the age were notoriously practical individuals who commonly rejected “new-fangled” ideas from “city folk.”

See the similarities? I do.

Local Extension Agents were members of the community already. They held the trust of many farmers in the area, most were farmers themselves. Their plan? Implement the suggestions from colleges and studies.

A virtuous cycle emerged: Colleges received more feedback from implementation and neighboring farmers witnessed the results firsthand. Granted, some of the results were not inspiring. That was ok. “It didn’t work” is good feedback for all involved. When the results were positive, the results drove adoption by neighboring farmers.

What Anders Wants

This is what Anders wants for Appalachia. He wants to foster a similar model that “plants” and “cultivates” emerging technology in the minds of people suffering economically.

The Biggest Challenge

Anders’ greatest challenge, in my opinion, will be overcoming the culture of outsider-rejection. This is one reason I love the Extension Agent approach. Identify members of the community that are most-likely to excel in technology. Find the innovators. They’re there, they merely need to be located.

Today’s Appalachian innovators are fantastic candidates to become Anders’ technology “extension agents.” Anders is asking them to remain in their community, demonstrating via their own success while integrating continuous education and accurate, if difficult to hear and accept, truths about the software industry into their culture.

The Quick Fix

The idea of a “quick fix” is a non-starter, as demonstrated in this article about the efforts of Mined Minds in Appalachia. While I applaud their effort, I think Mined Minds missed an opportunity during implementation. In communicating with Anders about the article, I reacted to the mention of “19 weeks” with “it took me 19 years to make the move from hobbyist coder to professional!” True story.

Expectation management is vital. Perhaps the most vital aspect of this effort.

Conclusion

I encourage you to:

Peace.

Free Webinar: A Data Science Story

From Shannon Lowder, Data Scientist, BimlHero: “You’ve all heard about data scientists, and seen job postings for them. But what is the job like, really?

“Let me share the story of my first official data science project. While sharing the story I’ll stop to point out the skills used that many DBAs, PowerShell junkies, BI developers and architects already have. There may be a few new topics you’ll want to pick up. This is a show and tell session.

“It’s my hope that you come away with a better understanding of what a data scientist is, and be able to measure how far between where you are and your own first project.”

Join Shannon 21 May at 12:00 EDT for A Data Science Story.

Register today!

:{>

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close