The general steps are
Set up your source query.
Pass the data through a Lookup for your Dimension with the missing results routed to a “No Match” output.
Insert those “No Match” rows into your Dimension using a SQL task – checking to make sure that this particular row hasn’t already been inserted (this is important).
Do another lookup using a “Partial Cache” to catch these newly-inserted members.
Use a UNION ALL transform to bring the existing and late-arriving members together.
Click through for more information and a helpful package diagram.
Take note here that I am only querying the msdb database. There is nothing exceedingly top secret here – yet. Most DBAs should be extremely familiar with these tables and functions that I am using here.
What does this show me though? If I have a package that is being run via Agent Job in msdb, then the sensitive information needs to be decrypted somehow. So, in order to do that decryption the password needs to be passed to the package. As it turns out, the password will be stored in the msdb database following the “DECRYPT” switch for the dtutil utility. Since I happen to have a few of these packages already available, when I run this particular query, I will see something like the following in my results.
That’s a clever solution. I get the feeling that I should be a bit perturbed by how simple this is, but I don’t; the real sensitive data is still secure.
Wolfgang Strasser has started a series on the new scale-out functionality in SQL Server Integration Services 2017. First, his introduction:
In the past, SSIS package executions were only able to run on the server that hosted the Integration Services server itself. With the rising number and requirements of more and more package executions sometimes the resources on the server ran short. Addressing this resource shortage custom scale out functionality was implemented that allowed package executions to be transfered to other “worker” machines in order to distribute execution load. With SQL Server 2017, this functionality is built into an shipped with SSIS 2017.
Before I am diving deeper into SSIS Scale Out I would like to discuss some basic vocabulary in the field of scalability.
The master is managing the available workers and all the work that is requested for execution in the scale out topoloy.
The master manages a list of (active) workers
The master gets the instructions from clients
The master knows the current state of work (queued jobs, running jobs, finished jobs, ..)
If you’re familiar with other distributed computing systems, this follows a similar path.
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.
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:
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.
I’ve written about it elsewhere in greater depth, but here are the headlines:
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.
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.
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.
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.
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.