Press "Enter" to skip to content

Category: Integration Services

SSIS And Always Encrypted

Published 2015-12-22 by Kevin Feasel

Jakub Szymaszek links to two articles on using SSIS with an Always Encrypted database.

Using Always Encrypted:

The SQL Server 2016 Always-Encrypted feature is only supported by the ADO.NET  provider currently. It is not supported by the OleDB provider and therefore any OleDB-provider-related transformation tasks such as Fuzzy Lookup will not support Always Encrypted feature.

In the “Execute SQL Task”, parameter binding for some encrypted SQL types is not supported, because of data type conversion limitations in Always Encrypted. The
unsupported types are money, smallmoney, smalldatetime, UniqueIndentifier, DatatimeOffset, time and date.

Lookup Transformations

Add an ADO NET source connect to the table “Customers” (please ref to here get more detail about how to use ADO NET Source to connect encrypted table).

Then create a cache connection manager “Customer Cache” and set the column information as below:

Based on article #2, it looks like you can’t simply use a Lookup transformation on an Always Encrypted column; you need to pull the results into cache first and then query the cache.  That’s not exactly difficult, but if you have an encrypted column, make sure you’re not writing those columns out in plaintext because of the cache option you selected.

Comments closed

SSIS Catalog Reports

Published 2015-12-14 by Kevin Feasel

Andy Leonard announces SSIS Catalog Reports:

I’ve released a (very early) version of SSIS Catalog Reports – v0.1 – on GitHub. You can access the project here.

My main goal is to provide some visibility into the SSIS Catalog without the need for SQL Server Management Studio.

These are Reporting Services reports, so they should work anywhere SSRS is hosted.  This looks like a helpful project for companies with ops people who don’t want or need SQL Server Management Studio but do need information on the SSIS catalog.

Comments closed

SSIS Variables

Published 2015-12-01 by Kevin Feasel

Mark Broadbent has a few nuggets of information regarding using variables in SSIS script components:

Notice that in the example above the assumption is that the SSIS variable datatype is compatible with the script variable type.

Once you have finished writing your code block you may save your code and close the Script Editor. All that is left is to click the OK button to close the Script Task Editor and run your package!

Getting variables to work in script components isn’t terribly difficult, but Mark shows that there are quite a few steps to the process.

Comments closed