Press "Enter" to skip to content

Category: Integration Services

Biml Metadata, With And Without BimlStudio

Ben Weissman has a pair of posts regarding metadata models in Biml.  First up, he gives us the high-roller solution:

If you’re lucky enough to be a BimlStudio user, you have access to the Biml Metadata feature! This feature allows you to build a Metadata model that fits your exacts needs which can then be browsed and used through a Metadata Instance using a dynamic object model.

As you probably still want to maintain your metadata outside of BimlStudio, we’ve build this little piece of code. It will ready your meta-Schema from a given SQL Database and build a Biml Metadata-Model from it. In a second step, it will also import the contents of your model into an instance:

If your company doesn’t want to shell out the cash to buy a license for BimlStudio, Ben also has a version for people using the free BimlExpress tool:

So maybe you’ve noticed our blog post on deriving metadata from SQL Server into BimlStudio, but you are using BimlExpress and therefore don’t have access to the feature described in there? While it’s true, that BimlExpress doesn’t support the Metadata features of the Biml language, there are similar ways of achieving a flexible metadata model in Biml.

This post shows you, how you can build a model in SQL Server, import it to Biml including derived relationships etc. and use it in a very flexible way.

To get started, we need to set up a sample model in SQL Server first. You can either download the scripts from https://solisyon.de/files/SetupDerivedMetadata.sql or scroll to the very end of that page. Although your individual model can and will differ from this one, we suggest you follow along using our example and start tweaking it afterwards!

Once you really get how Biml converts metadata to packages, life gets so much easier.

Comments closed

Unboxing ISPACs

It’s an early Christmas for Richie Lee:

The first file that we’re going to look at is the [Content_Types].xml file, and this is the file that confirms that the ZipPackage class is used. There’s an article here that is ten years old but is still valid (scroll down to the System.IO.Packaging INcludes Zip Support to read up on this.) This is because we know that the content_types file is part of the output when using the ZipPackage class to zip up a bunch of files into a .zip. The content_file contains both the extension and content type of the three other files that are included in the ispac:

  • dtsx
  • params
  • manifest

Note that the content_types file does not specify the files, either in quantity or in content, other than the fact that they will contain xml.

Read on for a good amount of detail on what’s included in an Integration Services package.

Comments closed

SSIS In Azure

Richie Lee reports that SQL Server Integration Services is now available as a service in Azure:

I’ve written about it elsewhere in greater depth, but here are the headlines:

  • It makes use of SSIS Scale Out, which was released as part of SQL Server 2017.

  • Although it is based on SSIS Scale Out, you can’t actually configure SSIS Scale Out to run on the instance. If this confuses you then read my in-depth post.

  • SSISDB is installed in either SQL Azure or on a Managed Instance.

  • You don’t have to create Integration Services Catalog/SSISDB yourself; it is done for you. So that annoying key management is no longer a problem.

Richie’s got more to say on the topic, so check out the highlights and then his in-depth post.

Comments closed

Using Biml With Oracle

Shannon Lowder shows us that you can write Biml to connect to an Oracle database as well:

Next, I wanted to build a package from Biml. A package that uses a connection to my Oracle instance.  Defining connections to Oracle in Biml is similar to every other connection you’ve ever defined in Biml (lines 3-12). The difference here is the Provider. (line 10)  I took the Provider attribute for my Oracle connection from the manual package I created in my previous test.

Simply right click on your Oracle connection in the package and choose properties.  Then look at the Qualifier value. I take the whole value and copy and paste it into the Provider attribute in my BimlScript.

Once you have that defined, set up a Package with our two connections, and a data flow (lines 14 – 33).  This script builds a simple data flow to move data from a single table on Oracle over to SQL Server.  The only difference is, this time, the package is named Automatic, rather than Manual.

You could get as complex as you’d like in your example.

As far as Integration Services goes, Oracle is just another source or sink and as long as you have an appropriate driver, the flows work the same way as with any other database.

Comments closed

SSIS Out Of Memory

Kevin Hill diagnoses an out-of-memory error in SQL Server Integration Services:

Notice the red sections above:

  • Not enough storage – was this physical memory, virtual memory or disk?

  • Buffer failed to allocate (10MB) – Why?

  • 2% memory load – yes, this box has 1 TB of RAM…plenty for what we are doing you would think.

  • Attempt to add a row – well, yeah…if SSIS uses buffers and cannot allocate one, there are either none, or they are full.  Makes logical sense to me

I won’t spoil the ending; you’ll have to read it over there.

Comments closed

Storing Sensitive Information In SSIS

Shannon Lowder shows the complex interplay between Biml and SSIS when it comes to handling credentials:

One of the questions I get when teaching others how to use Biml is how do you deal with sensitive information like usernames and passwords in your Biml Solution. No one wants to leave this information in plain text in a solution.  You need access to it while interrogating your sources and destination connections for metadata.  You also need it while Biml creates your SSIS packages since SSIS uses SELECT to read the metadata during design time to gather its metadata.  If you lock away that sensitive information too tightly, you won’t be effective while building your solutions.

In the end, you’ll have to compromise between security and efficacy.

Read on for more.

Comments closed

SSIS In Visual Studio 2017

Koen Verbeeck shows how to get SQL Server Integration Services support within Visual Studio 2017:

However, if you wanted to use Visual Studio 2017 you had to wait till today (August 23, 2017). There are several reasons why you want to use VS 2017 over VS 2015:

  • You are one of the cool kids and you use only the latest Visual Studio

  • There’s no TFS Explorer plug-in available for Visual Studio 2015. If you want to install SQL Server Data Tools only (thus without the full-blown Visual Studio), and you wanted TFS integration, you couldn’t use VS 2015. Unless you installed VS 2015 Community Edition (which has its own license issues).

  • You have a brand new laptop and you don’t want to install multiple versions of Visual Studio (the situation I’m currently in).

Read the comments for more details and clarification.

Comments closed

Compressing Files In SSIS

Mark Broadbent shows how to compress files within Integration Services using a script task:

Ok, so before I get started I will caveat this quick post by saying that there is an easier (or preferred) way to perform compression on files in SSIS using the ZipFile Class in the recent version of the .NET framework, this is sadly not available if you are using any legacy deployments.

While I have not spent ages adapting the following code to perform what should be a simple thing to do, I have spent enough time to justify sharing what I have done so hopefully you will also find some benefit from the code below too.

Click through to see Mark’s code and explanation.

Comments closed

Executing Powershell In SSIS

Daniel Calbimonte shows how to execute Powershell via C# in SQL Server Integration Services:

Get started

The article will include the following topics:

  1. Get the list of services using PowerShell in C#.

  2. How to send SSIS Parameters to PowerShell using the script task.

  3. How to use the PowerShell.addscript function.

  4. How to invoke a PowerShell script in C#

I think this is a fairly limited scenario—if you’re going to have to write C# code anyhow, you can do this same work in C#.  I suppose that it would be most useful in cases where you have to call common Powershell cmdlets rather than writing your own .NET code.

Comments closed

Synchronizing Logins And Jobs

Ryan Adams enumerates several methods for synchronizing logins and SQL Agent jobs across mirrored instances or Availabilty Group replicas:

There is an awesome set of PowerShell cmdlets out there written by MVP Chrissy LeMaire.  This method is my personal choice.  It works great and is easy to automate.  You can run it with SQLAgent or you can just use Scheduled Tasks in the OS.  The scheduled tasks method is a little cleaner, but you don’t get to see it in SQL Server.  Also if you are on a cluster and running Windows 2012 you can cluster the task scheduler as an added benefit.

Chrissy wrote this with the intent of making migrations easier, and she succeeded.  In fact, I made it a point to thank her at MVP Summit last year because it made my life insanely easier.  The advantage here is that you can automate a lot more than than just logins.  In fact you can migrate and automate pretty much anything at the server level.  Here is the link that I guarantee you are going to bookmark followed by a video demo where I show how to install and automate the syncing of logins using both the SQLAgent method and the Scheduled Tasks method.

https://dbatools.io/

DBATools would be my preference in this situation as well, but click through to see four other methods, as well as code.

Comments closed