A little Dance with SSIS and Informix

Tags: , ,
Comments: No Comments
Published on: July 23, 2010

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.


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.


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:

[codesyntax lang=”tsql”]


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.

SQL 2008 DTS

Tags: , ,
Comments: 1 Comment
Published on: July 7, 2010

It’s a Bird…

No, not really.   It’s just Windows 7.   I have recently upgraded to Windows 7.  I took the roundabout trip to do that going from Server 2003 to Windows XP and then finally up to Windows 7.  Having never used Windows Vista, there were several nuances to learn.  Most of those nuances had to deal with security and how to disable this or that setting or run programs with elevated privileges.  Pretty straight forward for much of it, once you figure out that it needs to be done.

One area that caused me a lot of grief trying to get it to work correctly was in the realm of dealing with DTS packages.  I browsed several webpages trying to find the fix for this particular issue.  Most of the fixes were explicitly designed to fix the issue in a 64 bit environment.   I am running a 32 bit environment.  I must admit that the packages run just fine from the 64 bit servers that are running SQL 2008 on Windows 2008 R2.  However, running the package or editing the package from my laptop only resulted in the following error message.

SQL Server 2000 DTS Designer components are required to edit DTS packages.  Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

Nitty Gritty

Let’s quickly delve into everything that I did in an effort to fix this.  Let’s start with the most comprehensive resource I found on the issue.  You can find that resource here.  The information presented in that blog post is more informative than the information listed in MSDN on the same topic.  I will outline the steps from the first article noted.

  1. Install Sql Server 2000 DTS Designer Components
    1. Download from here.  This is the most current version as of the writing of this article.
  2. Install Sql Server 2005 backward compatibility components
    1. Download from here.  This is the most current version as of the writing of this article.
    2. This is available with the installation media for SQL Server 2008.
  3. Verify your path environment variable.  The SQL 2000 path should be placed in the string prior to the SQL 2008 variable.  As a sample, this is what mine looks like.
    1. %SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\
    2. You can access the environment variables in Windows 7 by: Right Click My Computer  -> Properties -> Click Advanced System Settings -> Click Environment Variables -> Scroll to “Path” -> Click “Path” and then click Edit…
  4. SSMS – Manually Copy Files
    1. DLL Files to copy
      1. semsfc.dll, sqlgui.dll, sqlsvc.dll
    2. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\
    3. Destination
      1. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\
      2. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\
    4. RLL Files to copy
      1. semsfc.rll, sqlgui.rll, sqlsvc.rll
    5. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
    6. Destination
      1. %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\
  5. BIDS Manually Copy Files
    1. DLL Files to copy
      1. semsfc.dll, sqlgui.dll, sqlsvc.dll
    2. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\
    3. Destination
      1. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\
    4. RLL Files to copy
      1. semsfc.rll, sqlgui.rll, sqlsvc.rll
    5. Source
      1. %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\
    6. Destination
      1. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\
        1. I had to create the Resources Subdirectory
      2. %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\%lang_id%\
  6. The last step was not applicable in my case since the file version already matched
    1. stardds.dll File version: 2000.80.2151.0

That is the basic gist of what needs to be done in most cases to resolve this issue.  In my Step 5 (BIDS manual copy), I would note that this should be a required step.  I skipped that step numerous times due to it appearing to be “optional.”  I was not attempting to edit these packages in BIDS, but directly from SSMS.  It was only after trying to edit the package in BIDS that I found the next key to the issue I was experiencing.  Up to that point, I had installed, uninstalled, rebooted and repaired the install numerous times after several different minor tweaks.  Only after the next step did it finally start working.

I must also share that I attempted starting SSMS as administrator and also tried to launch it in compatibility mode.  I had to verify that neither of those options was the root of my problem.

Auction Block

I think it is necessary to describe how to edit a DTS package from BIDS prior to explaining what the fix was that got this working for me.  You don’t just open a DTS package directly in BIDS.  In my case, the DTS packages are not structured storage files either – I have them stored in SQL Server.

In BIDS, there is a Control Flow item called “Execute DTS 2000 Package Task.”  Drag this item into the work area in BIDS.   This Control flow task is illustrated in the image to the left as the last option in the image.  This task will give you the ability to edit a SQL 2000 dts package from within SSIS.  With the task showing in your workspace, open the properties for the task so you can configure it appropriately for the dts package you wish to edit.

After setting the properties for the package that you desire to edit or even simply view, click the Edit Package… button.  It is at this point that I was able to get a further clue into what was causing the inability to open packages from within SSMS.  At this point I got the following error message.

Error: Attempted to read or write protected memory. This is often an indication that other memory is corrupt (Microsoft Visual Studio).

Could this be my big break in the case?

Hard Hat and Hammer

With this piece of information, I have a tool and I can get to work fixing the problem.  Admittedly, at this point, I did not know that this was the root of the problem for me.  Quickly I race to my search engine du jour in an effort of finding any relevant information on this new error message.  I soon discover that this issue is one that existed with Windows Vista and appears occasionally in Windows 7.  The cause of this problem has its roots in Data Execution Prevention.  It would seem to only be an issue if your CPU supports DEP.   To determine if you have DEP enabled, you will need to check the Advanced Settings of your computer properties.  You can get there as explained earlier when checking the path.  Only this time you will need to click the Settings… button rather than the Environment Variables button.  Once inside of the settings, you will notice that there is a tab called “Data Execution Prevention” – click on this tab.

Once you have reached the DEP tab, you will be able to tell if DEP is enabled or not by the display on the screen and not by the settings selected.  To the left, I have a screenshot of how mine looks at present.  All settings are greyed out and I see a message at the bottom explaining that I must enable it via a command prompt utility called bcdedit.exe.

If DEP is enabled, the options will not be greyed out.  You may also see a note at the bottom that says “Your computer’s processor supports hardware-based DEP.”  You will also have a choice at the top between two settings, both of which are “ON.”

Just as you must enable this setting through the command prompt, you must also disable it through the command prompt.  Once disabled or enabled, you must reboot the computer for the settings to take effect.  So how does one disable this setting?  Here is the command to disable that setting.

bcdedit.exe /set {current} nx AlwaysOff

Should you decide you need to re-enable the DEP setting, you can use the following command.

bcdedit.exe /set {current} nx AlwaysOn

Disclaimer: I recommend you have a good backup of your system first.  It is also advisable to have a system restore point and understand how to boot into safe mode.  Adjusting settings like this may have an effect that is undesirable (such as unable to boot into windows).  You can read more about DEP from Microsoft.

I got my Toes in the Water…

As you can already see, I have disabled DEP on my laptop.  After disabling DEP and having rebooted, I am now able to edit a DTS package from within BIDS.  Great! Can I open a package from within SSMS though?  I open SSMS and attempt to open a package and it does indeed work.  This is just one more tool in the troubleshooting arsenal.  Happily I can now open and edit packages from both tools.  This will permit me to take the time I need to upgrade those packages to SSIS and do it correctly.

page 1 of 1

February 2020
« Jan    

Welcome , today is Monday, February 24, 2020