tim laqua dot com Thoughts and Code from Tim Laqua

18Nov/110

SSIS, BIDS and 64bit System DSNs: Who’s on first?

After an hour or so of changing settings, package configurations, protection levels, passwords, connection strings, etc - you might just still have yourself one of those dreaded red boxes (and a headache). And you thought inserting data using ODBC was easy...

You might even have yourself some logging that indicates an error like this:
[AS400 [19]] Error: ADO NET Destination has failed to acquire the connection {43E6AE37-24E8-46F6-8AB0-689DB6531167}. The connection may have been corrupted.

Assuming you are using a System DSN for your connection (this article is based on an ODBC DSN using the iSeries Access ODBC Driver to write to DB2, but it applies to any ODBC driver with 32bit and 64bit versions where a System DSN is used for the connection).

Before going on, you should understand and have checked the PackageProtectionLevel property in your package. To get any connection that requires a password to run in BIDS, you can't use DontSaveSensitive (unless you're using package configurations to enter the password or an explicit connection string). This is by far the most common reason connections fail to validate - but there's many resources out there already on that situation, so I won't go in to it.

Now that we know it SHOULD work - why doesn't it? In short, because you don't have a 64-bit version of the System DSN you selected. What sense does that make? Let me show you.

I'm sure you've all seen this before - which one do we select? I'm apparently pretty random with it as when I started this investigation, I found various different System DSNs in both places (and they didn't match). Maybe you just use the Control Panel to get to your ODBC Data Sources:

Interesting - we know there's two places to manage ODBC connections... Which one is this? It's the 64bit one. Which means if this is the only way you enter System DSNs, all you ever enter are 64bit System DSNs.

Now, on to how this happened in the first place. First, I'll create both a 64bit DSN and a 32bit DSN:

Then in SSIS, we create an ODBC connection, the dropdown lists our DSNs for us like so:

Notice that these are a mismash of both your User DSNs and 32bit System DSNs (and probably File DSNs... but who uses those anyway). No 64bit DSNs are listed. As a side note, this also presents itself when you create a "System DSN" (not knowing you're creating a 64bit specific DSN), then you go in to BIDS and can't find your shiny new DSN in the list. To get your DSN to show, you probably go back in and create a User DSN, and it will then show up. Until you try to run it on the server under a different username - which can be even more confusing.

To test if you are encountering this 32/64bit DSN issue, you can change your SSIS Solution properties to use the 32bit runtime:

Now run your package again and it should look a little more like this:

You can also take your compiled package and run it with the 32bit version of dtexec and the 64bit version. Here's what that should look like if you're having this issue:

Use the dtexec located under Program Files\Microsoft SQL Server\100\DTS\Binn

C:\>"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtexec" /FILE "C:\Users\
tlaqua\Documents\Visual Studio 2008\Projects\AS400 ODBC Test\AS400 ODBC Test\bin
\ExportToAS400.dtsx" /CHECKPOINTING OFF  /REPORTING EWCDI
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
 
Started:  12:29:04 PM
Info: 2011-11-18 12:29:04.24
   Code: 0x4004300A
   Source: Export to AS400 SSIS.Pipeline
   Description: Validation phase is beginning.
End Info
Error: 2011-11-18 12:29:05.21
   Code: 0xC0208452
   Source: Export to AS400 AS400 [19]
   Description: ADO NET Destination has failed to acquire the connection {43E6AE
37-24E8-46F6-8AB0-689DB6531167}. The connection may have been corrupted.
End Error
Error: 2011-11-18 12:29:05.21
   Code: 0xC0047017
   Source: Export to AS400 SSIS.Pipeline
   Description: component "AS400" (19) failed validation and returned error code
 0xC0208452.
End Error
Error: 2011-11-18 12:29:05.21
   Code: 0xC004700C
   Source: Export to AS400 SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2011-11-18 12:29:05.21
   Code: 0xC0024107
   Source: Export to AS400
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:29:04 PM
Finished: 12:29:05 PM
Elapsed:  1.029 seconds

Use the dtexec located under Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

C:\>"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /FILE "C:\
Users\tlaqua\Documents\Visual Studio 2008\Projects\AS400 ODBC Test\AS400 ODBC Te
st\bin\ExportToAS400.dtsx" /CHECKPOINTING OFF  /REPORTING EWCDI
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
 
Started:  12:29:49 PM
Info: 2011-11-18 12:29:49.96
   Code: 0x4004300A
   Source: Export to AS400 SSIS.Pipeline
   Description: Validation phase is beginning.
End Info
Info: 2011-11-18 12:29:51.14
   Code: 0x4004300A
   Source: Export to AS400 SSIS.Pipeline
   Description: Validation phase is beginning.
End Info
Info: 2011-11-18 12:29:51.41
   Code: 0x40043006
   Source: Export to AS400 SSIS.Pipeline
   Description: Prepare for Execute phase is beginning.
End Info
Info: 2011-11-18 12:29:51.58
   Code: 0x40043007
   Source: Export to AS400 SSIS.Pipeline
   Description: Pre-Execute phase is beginning.
End Info
Info: 2011-11-18 12:29:51.94
   Code: 0x4004300C
   Source: Export to AS400 SSIS.Pipeline
   Description: Execute phase is beginning.
End Info
DataFlow: 2011-11-18 12:29:51.94
   Source: Export to AS400
   Component "AS400" (19) will receive 2 rows on input "ADO NET Destination Inpu
t" (22)
End DataFlow
DataFlow: 2011-11-18 12:29:52.11
   Source: Export to AS400
   Component "AS400" (19) was given end of rowset on input "ADO NET Destination
Input" (22)
End DataFlow
DataFlow: 2011-11-18 12:29:52.11
   Source: Export to AS400
   Component "AS400" (19) will receive 0 rows on input "ADO NET Destination Inpu
t" (22)
End DataFlow
Info: 2011-11-18 12:29:52.11
   Code: 0x40043008
   Source: Export to AS400 SSIS.Pipeline
   Description: Post Execute phase is beginning.
End Info
Info: 2011-11-18 12:29:52.14
   Code: 0x4004300B
   Source: Export to AS400 SSIS.Pipeline
   Description: "component "AS400" (19)" wrote 2 rows.
End Info
Info: 2011-11-18 12:29:52.14
   Code: 0x40043009
   Source: Export to AS400 SSIS.Pipeline
   Description: Cleanup phase is beginning.
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  12:29:49 PM
Finished: 12:29:52 PM
Elapsed:  2.574 seconds

So how do we avoid/fix this?

  1. Ensure you setup the EXACT same System DSNs in both 64bit and 32bit on your development machine.
  2. On your server(s), you most likely only need to setup the 64bit DSNs as all they do is execute via dtexec and it probably uses the 64bit dtexec by default.
  3. If you plan on opening BIDS on your server - refer to point 1 for the server as well because if you run BIDS on it, it's a development box.
  4. Avoid User DSNs. While they are kind of magic (they don't differentiate between the 64bit and 32bit administration tool - there's only ONE list of User DSNs), they are a formula for headaches on shared development machines, packages ran by the sql server agent, or packages run via scheduled task (all depending on what user context you have setup on each engine).
  5. If you can't setup 64bit System DSNs and have to use 32-bit System DSNs, ensure you have your BIDS solution set to use the 32bit runtime, ensure when you run the packages using dtexec, you use the 32bit version, and when you execute the package via SQL Server Agent, configure it for 32bit execution:

Tagged as: , Leave a comment
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.