Press "Enter" to skip to content

Category: Integration Services

SSIS Out Of Memory

Published 2017-08-31 by Kevin Feasel

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

Published 2017-08-25 by Kevin Feasel

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

Published 2017-08-24 by Kevin Feasel

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

Published 2017-07-19 by Kevin Feasel

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

Published 2017-07-17 by Kevin Feasel

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

Published 2017-06-09 by Kevin Feasel

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

Mapping File Shenanigans With The Import/Export Wizard

Published 2017-06-08 by Kevin Feasel

Angela Henry ran into problems copying a boatload of data from a mainframe-hosted DB2 server and has lived to tell the tale:

This post talks about the issue I ran into with SSIS Mapping Files.

We currently run DB2 on an IBM iSeries AS400 for our ERP system.  I was tasked with copying data from the AS400 to a SQL Server database for some consultants to use.  The C-Suite didn’t want to give the consultants access to our AS400, so this was the work around that was put forth and accepted (and no, no one asked me before I was “voluntold” for the task).  Since this would essentially be a “one-time” thing, I chose to use the Import Export Wizard, but I would save the package just in case they wanted this process repeated.

Sounds like it was a painful experience, but it does have a happy ending.

Comments closed

JSON Data Sources In SSIS

Published 2017-06-07 by Kevin Feasel

Chris Koester shows how to read JSON data sources in SQL Server Integration Services:

Once the Script Component has been defined as a source, the output columns can be defined. For this post, the same USGS Earthquake data that was used in the “Download JSON data with PowerShell” post will serve as an example. Be careful to choose the correct data types here. This can be tedious because you have to choose the correct data types in the C# code as well, and ensure that they correspond with the SSIS types. It’s helpful to bookmark a SSIS data type translation table for reference.

It does involve creating a script component, but aside from the tedium that Chris mentions, it’s not too bad.

1 Comment

Collecting Rows Written From SSISDB

Published 2017-05-24 by Kevin Feasel

Andy Leonard has a script showing how to get the number of records written to an OLEDB destination, as reflected in the SSIS Catalog:

I prefer to passively gather performance metrics – even if it’s a little convoluted and more work (for me). I don’t often need these metrics immediately available, so I execute queries similar to the query below and store the performance metrics in a table. I can (and do) build dashboards to track SSIS performance (perhaps I should blog about them…) using this passively-collected performance metrics.

As with Lookup Transformation messages, OLE DB Destinations in Data Flow Tasks record specific messages using the same format every time. Because of this (hard-coded) consistency, you and I can passively collect the number of rows written while executing packages in the SSIS Catalog using the (default) Basic logging level. We can use the following Transact-SQL query to collect this execution metadata post-execution:

Click through for the script.

Comments closed

Multiple Flat Files SSIS Connector

Published 2017-05-01 by Kevin Feasel

John Morehouse shows that there are more SSIS connection managers than meets the eye:

While recently examining an existing SSIS package  before implementing some needed changes I noticed the connection manager for multiple flat files.

Normally, when processing multiple files, I use a sequence container and loop through them.  It’s usually a cumbersome process to setup and you must configure variables.  I was not aware the multiple flat file type of connection manager existed. No wonder because it’s notwithin the normal list.  This handy thing could only be found when I looked under “New Connection”.

Click through for more details.

1 Comment