Applied Business Intelligence

Applied Business Intelligence Has Moved

andy — Fri, 20 Apr 2007 21:12:00 GMT

Applied Business Intelligence has moved!

Actually it's migrated to a new version of Community Server - check it out!

:{> Andy

SSIS Cookbook series!

andy — Tue, 30 Jan 2007 17:40:00 GMT

I've been posting answers in the SQL Server Integration Services forum for a few weeks now. I have to admit, I like helping folks!

Between the questions I see there and the questions I receive while conducting SSIS training for Solid Quality Learning, I have quite a few SSIS demos to post!

I've decided to begin posting SQL Server Integration Services patterns here, under the title: SSIS Cookbook Series.

:{> Andy

Technorati Tags: SSIS patterns cookbook SSIS cookbook business intelligence SQL Server

Richmond Code Camp 3 Registration is now Open!

andy — Thu, 25 Jan 2007 23:10:00 GMT

Hope to see you all there!

:{> Andy

Technorati Tags: Developer Community Richmond Code Camp 3 Register

SSIS: Using The DontSaveSensitive Package ProtectionLevel Property

andy — Thu, 18 Jan 2007 09:44:00 GMT

I've read several MSDN SSIS Forum posts recently on the topic on configuration and deployment management. I get these questions in most of my SSIS classes as well.

One suggestion I've been investigating is using the DontSaveSensitive ProtectionLevel property and passing the connection string or password at execution time. I'm a test-it-and-see kind of guy, so I started tinkering with making this work this evening. Here's what I did:

First, I created a new database and table and added some data to the table. I created a SQL Login for connectivity purposes. (Microsoft recommends using Windows Authentication and I agree. But I needed something with a password to test this...) Next, I created a simple package with a Data Flow containing an OLE DB Source Adapter connected to an OLE DB Connection Manager. The Connection Manager name is important:

I next set the Package ProtectionLevel property to DontSaveSensitive:

The options for ProtectionLevel are:

DontSaveSensitive
EncryptSensitiveWithUserKey (default)
EncryptSensitiveWithPassword
EncryptAllWithPassword
EncryptAllWithUserKey
ServerStorage

If I execute the package inside the Visual Studio debugger at this point, it fails with the following error:
[Connection manager "ANDYT64X2.ConnectionTests.SQLLogin1"] Error: An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'SQLLogin1'.".
(The bold blue font is the name of the Connection Manager above.)

To get the package to successfully execute now, I have to provide the password at execution time. There are two ways to do this:

Store the value in a Package Configuration
Pass the value into the Package at execution time

I'm not going to look at using the Package Configuration option in this post.

One method of passing the value into the package at execution time is to use the /SET switch in dtexec.exe. The dtexexui.exe utility provides a nice interface for dtexec switches, including a Set Values page for configuring package values at run-time:

Note the \Package.Connections[ANDYT64X2.ConnectionTests.SQLLogin1].Properties[Password] Property Path contains the name of my Connection Manager - again shown in bold blue font. You will need to replace this value with the name of your Connection Manager. The Value contains the password. For demo purposes, the login name and password are the same (SQLLogin1). Please don't make them the same in the field. :)

:{> Andy

Technorati Tags:

PassMN Meeting

andy — Wed, 17 Jan 2007 09:13:00 GMT

I attended the PassMN meeting this evening here in Minneapolis. Alex Down, A Senior Software Engineer with Idera did a great job presenting on SQL Server Security and Auditing.

The group meets in a really nice location - the local Microsoft office. The pizza was good and the soda tasted like Miller Genuine Draft.

:{> Andy

Technorati Tags: PassMN Idera Alex Down SQL Server Security Auditing

Technorati Profile

andy — Tue, 16 Jan 2007 17:56:00 GMT

Technorati Profile

Welcome to Applied Business Intelligence!

andy — Tue, 16 Jan 2007 17:41:00 GMT

Welcome to Applied Business Intelligence!

My goal with this blog is to cover topics specific to the Business Intelligence market including ETL; data transformation, modeling, and mining; and reporting.

I plan to continue to blog at Applied Team System. I started this blog when posts there exceeded the one-per-day threshold.

I'm considering cross-posting my older ETL-related material here as well... we'll see.

Welcome!

:{> Andy

Technorati Tags: New blog Applied Business Intelligence BI SSIS ETL Reporting Services