Not too many moons ago I embarked on porting some servers over from SQL 2000 to SQL 2008. On some of these SQL 2000 servers, we had numerous DTS packages performing various ETL functions. One of these jobs interacted with an Informix database. On the old server, an ODBC driver had been installed permitting a connection be created between the two servers.
Thunderstruck
Something that we did not flesh out prior to the migration was this particular setup. One would think that would not be that big of a deal considering the ODBC connection information was still present on the old server. Sure we could find most of the pertinent information to recreate this connection. One vital piece of information was missing – the password for the user account in the Informix system. FANTASTIC! This raises a dilemma. We could easily change the password so we could recreate this connection. If we change the password, then we run the chance of breaking something else. We could also create a new account for this one process and ensure we document the userid, process, and password. However, that does not resolve the problem of not knowing the password for this other account used by some processes.
Decisions, decisions, decisions. We chose to potentially break some unknown process. We reset the password. Woohoo!! We were finally able to successfully recreate the ODBC connection on the new server. We were also able to confirm that it worked on both the new and old server. Prior to changing the password, we could not confirm that the connection was properly created on the old server since the password must be re-entered in the System DSN in order to test. By changing the password (and putting it safely into the vault) we were able to improve our documentation as well as confirm that the process could work.
Verify
Along the lines of my entry for TSQL Tuesday this month where I talked about the necessity to Observe and Report, I had to go back and learn how the old DTS package worked to ensure I could make it work in the new environment. I quickly ran into a new series of problems in my efforts to study this package.
64 v. 32
The server was 64 bit, the ODBC client was 64 bit, and dts packages were 32 bit. I knew this from previous experience but had become engrossed with trying to do too much that I overlooked it. Not too big of a problem, I will convert it to SSIS. I plan on converting all of the packages to SSIS over time, the schedule just got moved up for this package. That was really a no-brainer.
Opening BIDS
Creating an ODBC connection to Informix that can be consumed by the SSIS package is a little different. Again, this was not too difficult of a problem to overcome. You can create an ODBC connection in BIDS by creating a New Connection from the Connection Managers region. From the Add SSIS Connection Manager window, there is an option for “ODBC.” Select this option.
Another option to create this connection is to Create a new Ado.Net connection. From the new Ado.Net connection Manager, you need to select Odbc Data Provider from the “Provider” drop down menu at the top.
Once you have decided which method to use to create an ODBC connection you would need to specify the appropriate settings such as the user name, password, and dsn. This worked well for a bit. This method quickly ran me into an Informix problem.
Doesn’t play well with others
I was able to test my connection successfully initially. On the first attempt to pull data from the connection though, I started getting an error message in SSIS.
I started wondering if this was an SSIS problem. I wanted to confirm what the overall impact of this error was so I tried to test it from the DTS package as well. Guess what – same thing. What about testing from the ODBC DSN? I got the same thing there too. From there I proceeded to the Internet to see what else I could learn about this problem. Well, as it turns out – this seems to be a common occurrence where Informix is involved. The resolution for this is to modify the SQLHOSTS file on the Informix database server. One needs to change the nettype from Shared Memory to a Network connection (TCP/IP). This was a solution that would have required involving our vendor. Thus while waiting for a time when they could assist, I decided to try other things.
Missing Link
Since I didn’t like the need to pass a password to a connection manager in SSIS or for multiple people to need to know this account and password being used to connect to the Informix DB, and since I also needed to find a way around this shared memory problem, I decided to try an alternative method. I would create a linked server that would use the ODBC settings I had created for the DSN just created.
For this linked server, I provided the setting for the user and password of the remote server. The user has read only on the remote Informix server. By employing this, I can ensure that the password will not need to be known by anybody outside of the DBA group. The password will also not need to be stored in any files for connecting to this Informix server – I see it as being more secure and protected than the previous setup.
With connecting to Informix via a linked server, I am using the OPENQUERY method. I saw this as a method used / recommended by several people who have come across a similar need. To use the OPENQUERY, one needs to use a query similar to the following:
SELECT * FROM OPENQUERY
(LinkedServerName,
'SELECT * FROM sometable')I recommend changing the ‘Select *’ on the interior select statement to be specific columns. As a sidebar, I ran into problems with this query initially due to using a top in the interior select statement. OPENQUERY does not like the top, and thus I removed it and it works flawlessly.
One more for the road…
Once I got the OPENQUERY statement working to satisfaction in SSMS, I tried to use the OPENQUERY directly from an execute SQL task. This attempt failed miserably. The task was unable to connect through the linked server and thus bombed. With that, I tried a different avenue once again.
I decided that the query needed to be put into a stored procedure. I would test from there since I knew that the query worked from within SSMS. I had three tables and three tasks in the DTS package that required the same process. So for all three, I combined them into a single stored procedure. The basic functionality was to simply extract the data through OPENQUERY and then dump the data into a staging table. In the new world, that means I will be replacing six steps from the DTS package with one step in the SSIS package.
With all of the code in the proc necessary for these tasks, I proceeded with testing. Testing was very encouraging and worked rather well. That was testing from within SSMS. How would it fare from BIDS? That was the next step. I created an Execute SQL task with an ado.net connection to my database and set the IsQueryStoredProcedure property to True. I then ran this step individually and it worked just as expected. Now I can finally finish translating the rest of this DTS package into SSIS and get this ETL process back up and running.
In the End
When all was said and done, I took a DTS package and converted it to SSIS 2008. The package works better now than it did in the old world. I simplified the package from 18 steps down to 6. This package in the end probably does not need to be run from SSIS. All of the steps are Execute SQL tasks and no use of the SSIS transformations are employed. That is something that can be looked into changing at a later date. For now, I will leave it as is since it also serves as an example for some of the team on how to upgrade a DTS package to SSIS. They needed something to help familiarize themselves to the product – and this serves that purpose.
