Press "Enter" to skip to content

Category: Integration Services

Adding SSIS Catalog to a Docker Container

Published 2019-04-02 by Kevin Feasel

Andy Leonard takes two shots at adding the SSIS Catalog to a Docker container. First up is the version which doesn’t work:

I have been working on getting an SSIS Catalog running in a container for a couple years.
I share this post not to discourage you. 
I share it to let you know one way I failed. 
I thought I had succeeded when the PowerShell in this post worked. The PowerShell works, by the way – just not in a container configured thus.
This is but one failure. 
I failed more than once, I promise.

Andy perseveres and succeeds in part 4 of the series:

I can hear some of you thinking, “How do we accomplish this, Andy?”
I’m glad you asked. 
The answer is “We modify our container.”
Disclaimer: I’m about 100% certain there’s another way to do this and about 99% sure there’s a better way. I’m going to show you what I did. Cool?
Cool.

Read on to see how Andy did it.

Comments closed

SSIS on Windows Containers

Published 2019-03-18 by Kevin Feasel

Andy Leonard is a man who doesn’t like to take “no” for an answer:

Seriously, since I hopped the fence from developer to data I’ve dreamed of the day when I could practice lifecycle management with data-stuff like I used to practice lifecycle management with software development.
I recognize the obstacles. The greatest obstacle (in my humble opinion) is software is mostly stateless these days (these days started with Object-Oriented Programming and include its descendants).

Stateless development solves lots of engineering problems in lifecycle management, and by “solves a lot of engineering problems” I mean some engineering problems simply don’t exist so lifecycle management for stateless stuff can simply ignore a “lot of engineering problems.”

A database, on the other hand, is all about that state. When it comes to managing lifecycle for a stateful platform – like a database – ACID gets dumped on many lifecycle management tools and solutions (see what I did there?).

Is it possible to manage a data-related lifecycle using stateless tools? Yes. But here there be obstacles. Let’s look at on use case:

Click through for more thoughts and setup for a new series.

Comments closed

SSIS Catalog Dashboard

Published 2019-03-08 by Kevin Feasel

Tim Mitchell has a new GitHub repo:

The SSIS Catalog Dashboard is a simple collection of reports that provide insight into the activity within the SSIS catalog. The first of these is the Dashboard report. This report shows a summary of the number of packages that are running or have run in the recent past.

The dashboard repo, a Reporting Services project, is available on GitHub and is licensed under GPL version 3.

Comments closed

Clearing sysssislog

Published 2019-02-19 by Kevin Feasel

Eduardo Pivaral shows that you should clear out some of the bigger SSIS tables occasionally:

If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.

If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.

This kind of batched delete works for more than just the Integration Services tables; it’s a good plan wherever you have a large table and need to delete a fairly significant number of records from it.

Comments closed

Power Query SSIS Source Preview

Published 2019-02-18 by Kevin Feasel

Joost van Rossum is excited about a new SSIS data source in preview:

First you need to download and install the latest SSDT (Version 15.9.0) and since it is still in preview, you can only use it within SSDT or on a Azure Integration Runtime in Azure Data Factory.

If you drag the new Power Query Source to your Data Flow canvas and edit it, you can paste your Power Query script from Power BI (or Excel) in the query textbox. So there is not yet(?) an editor, but this should simplify a lot of tasks that could were previously could only be solved with .Net scripting.

Read on for more details. Because it’s a preview, there’s still a lot of work yet to do it, it seems. But on the bright side, it’s new functionality in Integration Services.

Comments closed

SSIS Error “Deserializing The Package”

Published 2019-02-14 by Kevin Feasel

Andy Leonard troubleshoots an odd error in SSIS:

Exception deserializing the package “Operation is not valid due to the current state of the object.”. (Microsoft.DataTransformationServices.VsIntegration)

As a professional consultant who has been blogging about SSIS for 12 years and authored and co-authored a dozen books related to Microsoft data technologies, my first response was:
“Whut?!”

That is a reasonable first response. Fortunately, Andy also had a second response which was more helpful in finding the root cause.

Comments closed

Emailing SSIS Errors

Published 2019-01-08 by Kevin Feasel

Peter Schott improves upon Kevin Hill’s script:


Recently, Kevin Hill (b | t ) posted on getting package errors from the SSIS catalog in a single query as opposed to clicking through the SSIS Reports and digging through pages.  I took that and ran with it a little bit. The first pass needed an additional index on the catalog to increase performance.  Kevin’s included that at the bottom of his query on the post above.  (You probably don’t need the included “message” column, though.)
I wanted to take this and run with it a little bit to report on all errors for a given folder within the last day, then e-mail that in an HTML formatted e-mail. To that end, I wrote up a quick stored procedure that should take the Folder or Package or Project name and a “to” e-mail address to send an e-mail through DBMail.

Click through for the script.

Comments closed

Querying SSISDB For Errors

Published 2019-01-02 by Kevin Feasel

Kevin Hill shows us two ways to get at error messages in Integration Services packages running in the SSIS Catalog:

My client makes extensive use of SSIS and deploys the packages to the Integration Services Catalog (ISC), and runs them via hundreds of jobs.
When one of the jobs fail, I have to go get the details.
Job History doesn’t have it.

I’d recommend the query route if you have to do this more than once or twice.

Comments closed

Optimizing SSIS Catalog Cleanup

Published 2018-12-31 by Kevin Feasel

Tim Mitchell has a script which replaces [internal].[cleanup_server_retention_window] in the SSISDB database:

Earlier this week, I blogged about the automatic cleanup process that purges old data from the SSIS catalog logging tables. This nightly process removes data for operations that are older than 365 days. While this is useful, many SSIS admins have complained that this process is very slow and contentious on large or busy SSISDB databases.
In this post, I’ll show to you one of the main reasons this purge process is slow, and will share a more efficient way of performing this delete operation.

Click through for the script and explanation.

Comments closed