Press "Enter" to skip to content

Category: Integration Services

Updated DIML Catalog Browser

Published 2018-05-14 by Kevin Feasel

Andy Leonard announces a new version of his Catalog Browser utility:

Catalog Browser first displays the reference mapping in the context of the environment named DEV_Person. DEV_Person is a Catalog Environment that contains a collection of Catalog Environment Variables.

Catalog Browser next displays the reference mapping in the context of the SSS Connection Manager named AdventureWorks2014.OLEDB that consumes the Reference between the DEV_Person environment and the Load_Person.dtsx SSIS package. Note that this Reference Mapping is displayed as <Property Name> –> <Environment Variable Name>, or “ConnectionString –> SourceConnectionString”. Why? Catalog Browser is displaying the Reference Mapping from the perspective of the Connection Manager property.

The third instance of Values Everywhere is shown in the Package Connection References node. Remember, a reference “connects” a package or project to an SSIS Environment Variable (learn more at SSIS Catalog Environments– Step 20 of the Stairway to Integration Services).  From the perspective of the reference, the reference mapping is displayed as  <Environment Variable Name> –> <Property Name>, or “SourceConnectionString –> ConnectionString”. Why? Catalog Browser is displaying the Reference Mapping from the perspective of the Reference.

Check it out.

Comments closed

Error Processing SSIS Task When TargetServerVersion Is SQL Server 2016

Published 2018-05-14 by Kevin Feasel

Shabnam Watson diagnoses an error condition when trying to run an Analysis Services processing task inside SQL Server Integration Services:

I ran into this problem a while ago at a client. They upgraded from Visual Studio 2013 to 2015 and the SSAS processing tasks started to error out immediately. The solution turned out to be setting the TargetSeverVersion to anything but SQL Server 2016. In this case, it was set to 2014 and that fixed the error.

Recently I ran into this post https://twitter.com/SQLKohai/status/994335086425399297 by Matt Cushing (@SQLKohai) and decided to dig in more.  Initially when I tested it, all was working fine. After I installed SSDT 2015 to test, I started getting the same error in SSDT 2017.  I played around with a DLL and got SSDT 2017 to work with all TargetVersionServers again. At the end I managed to break it again after I went through an uninstall and reinstall of all versions of SSDT. The reason I did the reinstall of SSDT was that I thought I might have had a broken registry entry that I was hoping the installation would fix. This did not work!

Read on for the solution and a detailed dive into the problem.

Comments closed

Verifying SSIS Database Connections With ssisUnit

Published 2018-04-16 by Kevin Feasel

Bartosz Ratajczyk shows how to test project-level connections in SQL Server Integration Services with ssisUnit:

Previously we successfully prepared tests for variables and parameters using VariableCommandand and ParameterCommand. Now it’s time to communicate with the database, and for that, I will use connection manager defined on the project level. I know from the ssisUnit tutorials it works perfect with package connection managers, so it’s time to verify it against the projects. I will test the package 10_ProjectCM.dtsx – it is just getting a single value from the table in a database and storing it in a variable. All the packages and unit tests are on my GitHub.

The package contains three SQL Tasks: the first just checks if we can communicate with the database using SELECT 1 statement, the second gets the information from the table, and the third repeats the second on the container level.

Click through for the tests.

Comments closed

Optimizing SSIS Throughput With Buffer Properties

Published 2018-04-16 by Kevin Feasel

Andy Leonard explains how he uses data flow properties to tune SQL Server Integration Services package performance:

I started answering a question on SQL Community Slack’s #ssis channel and I realized this would be better served as a blog post. The question was about three SSIS Data Flow properties: DefaultBufferSize, Engine Thread and DefaultBufferMaxRows.

I rarely change the EngineThreads property.

DefaultBufferSize and DefaultBufferMaxRows are two ways of managing the size limits of a Data Flow buffer. The two Data Flow Task properties can – and should – be treated as a single property. DefaultBufferSize is the number of bytes per buffer. DefaultBufferMaxRows is the number of rows per buffer. The defaults are 10,485,760 (10M) and 10,000, respectively.

Click through to learn more about these properties.

Comments closed

Getting Started With ssisUnit

Published 2018-03-29 by Kevin Feasel

Bartosz Ratajczyk builds a few SQL Server Integration Services unit tests with ssisUnit:

The result shows 1 test run, 1 test passed, 2 asserts run, 2 asserts passed.

Wait, what? We have prepared only one assert, why does it show two?

The second assert is: “Task Completed: Actual result (Success) was equal to the expected result (Success).“. Great. Where does it come from? Let’s find out.

This is a nice introduction to the topic; if you fuss about with SSIS packages, you should check this out.

Comments closed

Calling Azure Cognitive Services From SSIS

Published 2018-03-13 by Kevin Feasel

Rolf Tesmer shows off how easy it is to call Azure Cognitive Services from SQL Server Integration Services:

My SQL SSIS package leverages the Translator Text API service.  For those who want to learn the secret sauce then I suggest to check here – https://azure.microsoft.com/en-us/services/cognitive-services/translator-text-api/

essentially this API is pretty simple;

  1. It accepts source text, source language and target language.  (The API can translate to/from over 60 different languages.)

  2. You call the API with your request parameters + API Key

  3. The API will respond with the language translation of the source text you sent in

  4. So Simple, so fast, so effective!

Click through for the full post.  It really is simple.

Comments closed

Error Running Analysis Services Processing Task

Published 2018-03-05 by Kevin Feasel

Angela Henry ran into a problem with the SSIS Analysis Services processing task:

In both of these scenarios you will not be able to save the package.  So what the heck are you supposed to do?!  Here’s where my tunnel vision (and panic) sets in.  How was I supposed to get my SSAS objects processed?

I could always script out my processing tasks using SSMS and drop them in a SQL Agent job step.  But I have multiple environments and multiple cubes so each one would have to be hard coded.  Not a great idea, so scratch that.

Click through to learn the best way to fix this.

Comments closed

Parallel Execution With SSIS Framework Community Edition

Published 2018-02-15 by Kevin Feasel

Andy Leonard has a great post on parallel execution with SSIS:

Sit a spell and let Grandpa Andy tell yall a story about some data integratin’.

Suppose for a minute that you’ve read and taken my advice about writing small, unit-of-work SSIS packages. I wouldn’t blame you for taking this advice. It’s not only online, it’s written in a couple books (I know, I wrote that part of those books). One reason for building small, function-y SSIS packages is that it promotes code re-use. For example, SSIS packages that perform daily incremental loads can be re-used to perform monthly incremental loads to a database that serves as a data mart by simply changing a few parameters.

Change the parameter values and the monthly incremental load can load both quarterly and yearly data marts.

You want better performance out of the daily process, so you read and implement the parallel execution advice* you’ve found online. For our purposes let’s assume you’ve designed a star schema instead of one of those pesky data vaults (with their inherent many-to-many relationships and the ability to withstand isolated and independent loads and refreshes…).

You have dependencies. The dimensions must be loaded before the facts. You decide to manage parallelism by examining historical execution times. Since you load data in chronological order and use a brute-force change detection pattern, the daily dimension loads always complete before the fact loads reach the latest data. You decide to fire all packages at the same time and your daily execution time drops by half, monthly executions time drops to 40% of its former execution time, and everyone is ecstatic…

…until the quarterly loads.

This is a great post.

Comments closed

K-SSIS: Examining SSIS Packages

Published 2018-02-06 by Kevin Feasel

Ed Elliott has a new tool available:

The main part of the UI is a graphical map of the objects in a package. I have spent quite a lot of time trying to think of a better way of showing the elements that make up a package, and it is hard to argue with the workflow approach, all the ETL tools I found do the same thing and visualizing packages as a series of interconnected tasks is useful but maybe not everything and the only way.

Because of all these issues I have really tried to stay away from SSIS development and looked for other solutions such as using biml to write packages or writing code in other languages but I keep coming back to the fact that the SSIS engine is great at what it does and if you have a lot of data or a lot of files/tables then writing a similar framework would be a lot of wasted effort. So, how can we use the SSIS engine but make better, more efficient use of our development time?

Click through to see a couple of screenshots as well as a link to download K-SSIS.

Comments closed

Logging Variable Values In SSIS Packages

Published 2018-01-08 by Kevin Feasel

Andy Leonard shows two methods for logging variable values in Integration Services packages:

During the December 2017 delivery of Expert SSIS, I was asked if there is an SSIS Catalog Logging Mode that will display the value of variables. I responded that I wasn’t aware of a logging level that accomplishes this, but then – as sometimes happens – I could not let it go. It is an excellent question. So I dug in…

I second his notion that you should learn how to use script tasks and script components.  They’re not very difficult to pick up and even a basic knowledge will benefit you greatly.

Comments closed