Category: Integration Services

SSIS In Azure

Published 2017-09-28 by Kevin Feasel

Richie Lee reports that SQL Server Integration Services is now available as a service in Azure:

I’ve written about it elsewhere in greater depth, but here are the headlines:

It makes use of SSIS Scale Out, which was released as part of SQL Server 2017.
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.

Comments closed

Using Biml With Oracle

Published 2017-09-15 by Kevin Feasel

Shannon Lowder shows us that you can write Biml to connect to an Oracle database as well:

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.

Comments closed

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:

Get the list of services using PowerShell in C#.
How to send SSIS Parameters to PowerShell using the script task.
How to use the PowerShell.addscript function.
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