Press "Enter" to skip to content

Category: Integration Services

Biml And Metadata

Published 2016-08-31 by Kevin Feasel

Ben Weissman provides an example of using metadata to drive conditional data loading:

Now that we’ve defined connections, databases and schemas we still need to add our table metadata.

We’re going to do that by looping across all our databases marked as a source in Biml, retrieving the list of required tables from SQL (located in View vMyBimlMeta_Tables) and creating a table tag for each table which will also reference back to the corresponding target system. That also allows us to use the same table names multiple times. Again, we’ll store some additional data in annotations.

This is an interesting concept.  Check it out.

Comments closed

Deploying SSIS Packages In VS 2015

Published 2016-08-29 by Kevin Feasel

Neil Gelder notes that you can deploy different versions of SSIS packages using Visual Studio 2015:

For years I’ve dream’t of having one set of tools for developing SSIS packages! not a lot to ask really and  great step towards this from Microsoft was decoupling the development IDE from the main SQL Server install to produce the standalone SSDT (SQL Server data tools)

But like most people I work in an environment which has legacy versions for SQL Server in production, but equally like most tech folk (giddy kids wanting new toys) I always try and use the most current and exciting  version of VS.  This however proves a problem when developing for SSIS, for example if you developed a SSIS package in VS 2013 you’d not be able to deploy this correctly to a SQL Server 2012 version of Integration services catalog.  In the past this resulted in having two IDE’s installed, SSDT 2012 (VS shell) for any 2012 catalog development and VS 2013 installed for other work.

I had one person mention during a talk I gave that this isn’t foolproof, but my experience (limited to SQL Server 2012 and 2014) was that deployment worked fine.  As always, test before making changes.

Comments closed

Issues With SSISDB In An Availability Group

Published 2016-08-09 by Kevin Feasel

Andrea Allred has some lessons learned from a troublesome service pack upgrade:

Here are a few of the fun errors that we saw.

“Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 942, state 4, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.”

There are some good lessons here.

Comments closed

Reverse Engineering SSIS Packages

Published 2016-08-05 by Kevin Feasel

Ben Weissman shows how to use BimlOnline to reverse engineer an Integration Services package into its component Biml:

A few things to be aware of:

– Your file will be uploaded to and stored at BimlOnline so you may want to remove passwords etc.
– If you’re trying to figure out how to build a specific task in Biml but your file does way more that just that, consider creating (and uploading) a file that will only contain the task you’re looking for – this will keep the resulting Biml clean and easy to read.

This is extremely helpful for figuring out how to use third-party components with Biml.  If you want a local IDE, there’s always BimlStudio (which costs money).

Comments closed

RetainSameConnection

Published 2016-08-02 by Kevin Feasel

I explain what the RetainSameConnection property on an Integration Services connection does:

My co-worker had set up a dynamic connection (see Rafael Salas or Hari Bagra for details on how to do this), but something weird was happening:  the package was trying to push everything to the same server.  I confirmed that if all relevant customers loaded were for the same server, that the process would work correctly, and that I could run each server load one at a time, so there weren’t any problems connecting to particular servers or parameters overriding this choice.  It’s like the connection was “sticky,” connecting successfully to the first server and then ignoring the later changes.

RetainSameConnection allows for certain benefits, but has specific limitations.  Click through to see those details.

Comments closed

AutoAdjustBufferSize

Published 2016-07-28 by Kevin Feasel

Koen Verbeeck looks at an update to AutoAdjustBufferSize in SSIS 2016 CU1:

Anyway, one of the particular updates that caught my attention was Adds the adjusted buffer size to the BufferSizeTuning event when AutoAdjustBufferSize is enabled in SSIS 2016. In simple terms, it allows you to log  the size of the data flow buffer set automatically by the AutoAdjustBufferSizeproperty. This property basically automatically calculates the buffer size needed to reach the amount of rows in the buffer specified by DefaultBufferMaxRows. Therefore, it ignores  the DefaultBufferSize property. Unfortunately, this new property is set to false by default.

Logging is at the Verbose level, so you won’t use it very often, but for test purposes, it’s interesting to see those changes.

Comments closed

Stopping Integration Services Packages

Published 2016-07-26 by Kevin Feasel

Andy Leonard explains various methods of stopping SSIS packages in progress:

Once you have the operation_id value, simply plug it into the stop_operation stored procedure and execute:

exec SSISDB.[catalog].stop_operation @operation_id = 24

The stop_operation stored procedure runs for a few seconds (typically less than 15 seconds) and stops the execution of the SSIS package. SSIS packages that have been stopped are listed with “Canceled” status. You can see operation_id 19 was stopped in the screenshots shown above.

Read on for more.

Comments closed

Using Lookup Components

Published 2016-07-25 by Kevin Feasel

Thomas LeBlanc explains how to use Lookup components as part of warehouse loading:

The “Fail component” option will stop processing of the import if no match is found in the lookup table. This is not a good option for loading data into a fact table. Most systems would want the import to continue for the customer surrogate keys that are found.

  1. Ignore Failure – Null will replace lookup values selected. Those rows with no match are streamed to the normal flow in package with the Null value in match columns selected.

  2. Redirect rows to error output – red line output will show a failure but can pipe the data to any component. Those rows with no match are not streamed to the normal flow in package.

  3. Fail component – the package stops with a failure if no match exists, if all match there is no failure

  4. Redirect rows to no match output – output can be piped to another component and processing continues. Those rows with no match are not streamed to the normal flow in package.

Lookup components are one of the more powerful components in a data flow task.  Read the whole thing.

Comments closed

Biml Transformers

Published 2016-07-21 by Kevin Feasel

Bill Fellows uses a Biml transformer to change a variable’s value:

Our transformer is simple. We’ve specified “LocalMerge” as we only want to fix the one thing. That one thing, is an SSIS Variable named “ServerName”.

What is going to happen is that we will redeclare the variable, this time we will specify a value of “SQLQA” for our Value. Additionally, I’m going to add a Description to my Variable and preserve the original value. The TargetNode object has a lot of power to it as we’ll see over this series of posts on Biml Transformers.

I’ve not used transformers before; this was interesting.

Comments closed

SSIS Firewall Rules

Published 2016-07-20 by Kevin Feasel

Slava Murygin shows how to create a firewall rule to allow SSIS connections:

Recently tried to connect to Remote SQL Server Integration Service directly from SSMS and got following error:

TITLE: Connect to Server
——————————
Cannot connect to 10.1.32.66.
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
——————————
BUTTONS:
OK
——————————

Slava then shows how to work around this.

Comments closed