Query Informix Database and Insert Data to SQL Server Database?

[octavian10]

Hello,
I have just been tasked with a project that needs to pull data from an HR Informix Database and insert it into a security SQL Databses. It needs to do this daily and only if there has been a change to the HR database. I am preaty good with SQL statments, stored procedures and .NET, but am stuck on how to take the queryed Informix data and insert it into the SQL database. Any help would be greatly appreciated, I have been away from SQL for a while and am a little rusty.
Thanks and have a good day.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[fuzzyocelot]

Well, you could do this using SQL Server. Our HR system was using an Informix db for a while but we recently switched to Oracle. We had a nightly job that imported data from Informix to SQL Server at the time (now Oracle to SQL Server). The HR system ran on Unix boxes with an Informix database. So the data was basically in files. Do you have a similar setup?

I hate to assume so I'll ask since you didn't say. Are you using SQL Server? If so, which version? 7, 2000, 2005? If you're using 2000, I would suggest creating a package using the Data Transformation Service (DTS). If you're using 2005, try Integration Services (IS). We have 2000 and are in the process of upgrading to 2005. So I'll give general info using 2000 for now.

Basically, you would need to make sure you have the Informix ODBC drivers loaded on the server or wherever the SQL database is at. Then in the package you would need to use "other" data source, I believe. I'm at home so I can't check what ours is. This is where you'll connect to your Informix database. Then you'll need to set up a destination connection (SQL database). You would connect them with a transform data task I believe. I'm doing this from memory so please bear with me. In the transform data task you'd specify one source table on the source tab, the destination table on the destination tab, and how to copy the data (transform) on the transformation tab. Mostly we do straight copies with some exceptions. Then add another transform data task using the same source and destinations. So you'd end up with multiple "lines" connecting the source and destination. Make sense? You can always add text annotations to note which "line" (i.e. transform data task) is for which tables.

When I get to work on Monday, I'll check our old packages to make sure and write more then.

 

[fuzzyocelot]

I'll also need to check if we did anything special on the Informix side. I wasn't the one who originally set it up so I'll have to check with one of our other DBAs next week.

 

[octavian10]

Thanks for the response I appreciate it, yes I am using SQL Server 2000 and will try the DTS option. Once again thanks and if find more info let me know.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)