Search results matching tags 'ssis' and 'SSIS Snack'

SSIS Snack: Name Those Connections!

andyleonard — Thu, 09 Feb 2012 12:00:00 GMT

When creating Connection Managers in SSIS, take a moment to click the “All” page and set a value in the Application Name property. In SSIS 2008+, a default value is applied to this property. It’s useful but it contains a GUID. Yuck. I prefer to enter a more concise identifier like the one shown here:

“Why should I enter this information, Andy?” I’m glad you asked! When troubleshooting performance or locking / blocking issues, DBAs start with a query to ascertain what is currently executing calls to the SQL Server instance. As shown below, sp_who2 is one way to obtain a peek into the inner workings of SQL Server. Note the column labeled “Program Name” displays the value we configured into the Application Name property of the SSIS Connection Manager. This gives the DBA a fighting chance at isolating SSIS packages from other applications and jobs executing.

I use the following format:

[SSIS].<Package Name>.<Connection Manage Name>

The format you choose isn’t all that important. Being consistent is very important.

Happy Integrating!

:{>

SSIS Snack: Data Flow Source Adapters

andyleonard — Fri, 19 Mar 2010 11:00:00 GMT

Introduction

Configuring a Source Adapter in a Data Flow Task couples (binds) the Data Flow to an external schema. This has implications for dynamic data loads.

"Why Can't I...?"

I'm often asked a question similar to the following: "I have 17 flat files with different schemas that I want to load to the same destination database - how many Data Flow Tasks do I need?" I reply "17 different schemas? That's easy, you need 17 Data Flow Tasks."

In his book Microsoft SQL Server 2005 Integration Services, Kirk Haselden (Blog) includes a blurb about "long discussions" regarding binding the data flow pipeline to the source metadata in this manner (Note, p. 539). The decision to tightly couple the source schema and the Data Flow was largely driven by the "Move Large Amounts of Data" use case.

"What Can I Do?"

You can load data from 17 files, as long as they have the same schema. There are advanced techniquues for loading data dynamically, but they are beyond the scope of this post.

:{> Andy

SSIS Snack: OLE DB Destination 1

andyleonard — Mon, 01 Mar 2010 12:00:00 GMT

Introduction

This SSIS Snack is a continuation of a couple previous SSIS Snacks:

SSIS Snack: Configuring an SSIS 2005 Lookup Transformation for a Left Outer Join

(please see the clarification SSIS Snack: SSIS 2005 Lookup Transformations 101)

SSIS Snack: Configuring a Conditional Split
SSIS Snack: Conditional Split Outputs

Final (?) Destination

Drag an OLE DB Destination adapter from the toolbox onto the data flow canvas (if you haven't already). After connecting a data flow path from the output of the Conditional Split to the OLE DB Destination, double-click the data flow path and select the Metadata page.

Note this contains a definition of the "columns" in the data flow pipeline.

Rename the OLE DB Destination adapter StageRows. Open the editor, select the destination connection manager and set the Data Access Mode property to "Table or view". Next (this is the cool part) click the New button beside the "Name of the table or view" dropdown.

The New button pops open a Create Table window populated with a CREATE TABLE statement. The table name comes from the name of the OLE DB Destination adapter, the column DDL is derived from the Data Flow Path's metadata (we saw this earlier). Isn't that cool?

As soon as you click the OK button, the DDL is executed and the table created.

:{> Andy

SSIS Snack: Conditional Split Outputs

andyleonard — Fri, 19 Feb 2010 12:00:00 GMT

Introduction

This SSIS Snack is a continuation of a couple previous SSIS Snacks: SSIS Snack: Configuring an SSIS 2005 Lookup Transformation for a Left Outer Join (please see the clarification SSIS Snack: SSIS 2005 Lookup Transformations 101) and SSIS Snack: Configuring a Conditional Split.

Landing Zone

When you use a Conditional Split to create an additional buffer, downstream connections must know which output you are trying to connect. In this case, I have an OLE DB Destination adapter to which I am trying to connect the Conditional Split:

Note that because I defined two new buffers in the Conditional Split - and renamed the Default Conditional Split Output to "Uchanged Rows" - I am prompted to select the output, and I select New Rows.

:{> Andy

SSIS Snack: SSIS, Excel, and a 64-bit OS

andyleonard — Tue, 19 Jan 2010 12:00:00 GMT

Let's say you want to load data from an Excel 2003 (.xls) file into a database using SSIS:

If you're running on a 64-bit machine, you'll get this:

Plus this error:

[PASS Regional Mentor Spreadsheet [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Not good. There is no 64-bit JET driver, and the JET driver is used by the Excel ConnectionManager. Phooey. Sad face. Bummer.

So how do you get around this? One quick and easy way is to execute this package using the 32-bit debugger. To change the default setting, right-click the Project in Solution Explorer and click Properties:

When the Properties dialog displays, click the Debugging page from the list on the left and then change the Run64BitRuntime property to False:

When you execute the package in the debugger (or in the runtime), it will now succeed:

:{> Andy

SSIS Snack: Package Version

andyleonard — Wed, 06 Jan 2010 12:00:00 GMT

Here's a couple cool SSIS Expressions for creating Package Version strings:

Format: 1.0.0

(DT_STR,4,1252) @[System::VersionMajor] + "." + (DT_STR,4,1252) @[System::VersionMinor] + "." + (DT_STR,4,1252) @[System::VersionBuild]

Format: 1.0.0.20100106

(DT_STR,4,1252) @[System::VersionMajor] + "." + (DT_STR,4,1252) @[System::VersionMinor] + "." + (DT_STR,4,1252) @[System::VersionBuild] + "." + (DT_STR,4,1252) Year(GetDate()) + (Month(GetDate()) < 10 ? "0" + (DT_STR,2,1252) Month(GetDate()) : (DT_STR,2,1252) Month(GetDate())) + (Day(GetDate()) < 10 ? "0" + (DT_STR,2,1252) Day(GetDate()) : (DT_STR,2,1252) Day(GetDate()))

Enjoy!

:{> Andy