Copy Data from Informix DB to SQL

[travis13]

Greetings - I need to setup a process to copy data from an Informix database to a SQL database on a daily basis. I have copied the current data using DTS, but need to append the SQL DB with the new data from the Informix DB daily.

Thanks in advance!

 

[sahernandez]

Hello , that data will be change on Informix?
and also how many rows you gonna get from informix.

you have a lot of kind of solution, but you should be know if the data on informix will be change.
You can do on informix a small view with the last 2 o 5 days a go and then on the Task read from the view and before that make sure that you deleted the las 2 o 5 days on SQl table.
But make sure that the data on informix will no change in the last 2 or 5 days.
Or you work with a Temporary table on SQL and join with the real one to check any changed.

 

[travis13]

The Informix DB is attached to our Avaya CMS system, collecting call center data. The CMS system only stores intraday (half-hourly) data for ~30 days, which is insufficient for historical reporting purposes.

I want to pull all of the data for the previous day out of Informix and append the SQL db. There are ~8 tables that contain maybe 100 fields between them. There are ~7500 records that get created everyday.

 

[Wocca]

Just schedule a DTS package to do it. It can append to existing tables just as well as create new ones.

Regards
Warwick

 

[travis13]

Thanks Warwick.

Would this be done through a sql statement, or is there an option somewhere using the DTS wizard?

 

[osjohnm]

Once you have right clicked and chosen import data, and setup the source and destination servers, choose "copy tables and views from source db"

select the tables and the destination tables. On each table that will be copied under "Select source tables and views", click on transform and choose the "Append rows to destination table". If the tables don't exist then only the create option will be available. I suggest that you first create the tables or dts them in straight. Then do the above again but then choose append since the table now exists.Then carry on with the rest of my post.

Click ok and then next and then select "Save DTS package" which will give the option to save the package as well as setup a schedule to execute the package.

That should do it.

John

 

[travis13]

John - good stuff. I think I am making some progress! One question - will this copy all of the data again, or will it only copy the records since the last import?

 

[osjohnm]

This will copy all the data again(unfortunately).
You could use the "build sql query" option and then write a statement that only brings across the data from Informix which isn't in there already. You would have to use a NOT IN statement or you could use NOT EXISTS in the statement to only get records that aren't in the tables.

Hope that helps

John