This week, I started work on a data mart for a client whose source data resides on an AS400. The data access for the source system is handled via the iSeries ODBC driver from IBM. Getting the DSNs set up correctly to work with the client's 64-bit SQL Server installation was a little irritating, so I thought I'd share some lessons learned.

As you may already know, even though the database engine for 64-bit SQL Server is a 64-bit application (with a 64-bit dtexec executable for SSIS packages), the BIDS designer is a 32-bit application. This presents a challenge in package development and deployment when data connections either do not have a 64-bit driver (as in the case of Jet for Excel or Access) or when the 64-bit and 32-bit drivers exist, but require different executable versions (as with ODBC).

In this case, I needed to connect to the AS400 with the iSeries ODBC driver from within SSIS. The client had created the DSN for me and believed he had created a 32-bit DSN (since the driver description within the ODBC Administrator was labeled as 32-bit). However, the DSN did not appear in the DSN selection drop-down in the ODBC connection manager in SSIS.

The problem was that, regardless of what the driver was labeled, the DSN had to be created with the 32-bit ODBC Administrator in order for it to exist as a 32-bit DSN and become accessible to SSIS. Furthermore, in order for the package to run both in debug mode from within the designer (32-bit) and as a scheduled job in SQL Server Agent using the Integration Services step type (64-bit), you have to create DSNs with the identical name in both the 32-bit ODBC Administrator and the 64-bit Administrator.

So, in the Program menu for the iSeries access, you'll see a 64-bit and 32-bit ODBC Administrator:

You need to create identical system DSNs using both Administration modules.  The DSN defined in the 32-bit Administrator will be exposed and utilized from with BIDS; the DSN defined in the 64-bit Administrator will be utilized by SQL Server Agent and 64-bit dtexec.

To add a little more excitement to your day, SSIS has no ODBC source adapter for the Data Flow task, and in SQL Server 2008, the Data Reader source adapter has been retired and replaced with the ADO.NET source adapter.  In order to use the iSeries DSN, you need to create your connection manager as an ADO.NET provider (.Net Providers\Odbc Data Provider) rather than as an ODBC connection manager type.

Back in fall of 2006, Perkins Consulting embarked on a data warehousing project with a client whose source data resided in Oracle and whose SQL Server 2005 environment was established on a 64-bit server. This was our first experience with this particular combination of variables, and we ran into some entertaining issues.

I was attempting to set up a Connection Manager for the Oracle source database, using the Native OLE DB\Oracle Provider for OLE DB. When testing the connection, I received the following error:

Entertaining?  Oh, yeah.  A laugh riot.

After extensive Google searches, I finally found a post regarding this BIDS error in reference to Analysis Services.  Apparently the issue was on the Oracle side.  The Oracle provider had an issue with special characters in path names.  The default installation folder for SQL Server 2005 32-bit executables (including BIDS) on a 64-bit server includes parentheses (i.e., c:\Program Files (x86)\…).  Oracle was not pleased with this situation in the slightest.

The post I found recommended installing the 32-bit executables in a folder without special characters, but that wasn’t an option at our client site.  The post provided a work-around using a batch file to launch BIDS and to explicitly set the executable path without using the special characters, placing the Oracle client first:

Set path= c:\oracle\product\10.2.0\client_1\bin;%path% "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"

The error only arises when calling the Oracle provider using BIDS (a 32-bit application).  When executing the completed package, SQL Agent invokes the 64-bit dtexec executable, which is located in a path that TNS does not find objectionable. 

It’s entirely possible that this issue has been resolved in later versions of the Oracle client or the native Oracle OLE DB provider, but because of the configuration at this particular client site, I still launch BIDS there using this batch file.

"You can't get there from here."

Part 2

In Part 1 of my post on this topic, I described how to configure a package containing UNC paths on flat file destinations to execute successfully in BIDS debug mode.

At the risk of being repetitive, here's the system background:

• The SQL Server data mart resides on a remote server running 64-bit SQL Server 2005 Enterprise Edition.
• Access to the file system on the server is not allowed.
• SSIS development occurs on a virtual server with only the SQL Server client tools installed.
• The destination for the output files is on yet a third machine, only accessible to the server and the development machine via UNC paths.

Now that I had successfully executed the package in debug mode by forcing BIDS to use the 32-bit runtime to recognize the UNC paths, I was ready to try to schedule the package via a SQL Agent job.

I'm used to using the CmdExec step type in SQL Agent to force execution of a package in 32-bit mode (I have to do this any time the package includes Excel file sources, for instance). However, when I used the usual method for this particular package, I got yet another error message:

Error: 2009-08-31 16:01:55.99 Code: 0xC001401E Source: stage_reports Connection manager "Extract File Cards" Description: The file name "\\target1\Dest-Data\Cards.csv" specified in the connection was not valid.

Well, rats.

Since I wasn't able to access the file system or view the context of the server in the network, I had to find a way to work around this - i.e., schedule the package in 32-bit mode without using SQL Agent.

To do this, I wrote a batch file to execute the package using 32-bit dtexec from the context of the development machine (I used dtexecui to get the command line for the package):

"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "\\vm-dev1\ReportStaging\SSIS\report_staging\stage_reports.dtsx"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

By scheduling the batch file as a Windows task and bypassing SQL Agent entirely, I was finally able to get the UNC paths to work on the calling machine, the server and the destination machine.

Whew!

"You can't get there from here."

Part 1

As part of a recent client project, I had to use SSIS to create six pipe-delimited flat files for consumption by a third-party report writer. I created a template container with all the tasks necessary for the conversion. The template included variables to hold the path and file name for the destination files, and I used an expression in the connection string for each destination Connection Manager to reference the variables.

System background:

• The SQL Server data mart resides on a remote server.
• Access to the file system on the server is not allowed.
• I do SSIS development on a virtual server with only the client tools installed.

During testing, I used a local drive path on the development machine (E:\data) and the package executed in debug mode with no problem. The final destination for the files was on a third machine, so I had to use UNC paths for access. After I changed the variables to UNC paths, I got the following error when executing in debug mode:

Error: 2009-08-31 15:20:49.96 Code: 0xC020200E Source: <task name> <destination adapter> [346] Description: Cannot open the datafile

I could open the Connection Manager for the files defined by UNC pathnames inside of the BIDS designer, but the execution of the package failed because it couldn't open the file.

Hmmmm. Very suspicious.

I checked the SQL Server version on the remote server. Sure enough, it was the 64-bit version. I set the Run64BitRuntime property for the project to False and the package executed successfully from the BIDS designer.

Looks like UNC paths on flat files are another point of conflict between 64-bit and 32-bit SQL Server. Since the BIDS designer is a 32-bit application, I could open the files from inside the designer. Executing the package, however, invokes the 64-bit executable, unless BIDS is forced to use the 32-bit executable with the above setting.

Of course, then I had to try to schedule the thing….(to be continued)