Re: Calling all SQL and Informix experts!
DTS is very robust. Using an activex script transformation you can pretty much order and amend the data as you see fit, including the decision of which transacts to load or just skip over because you've already loaded it.
Howevere it's generally better to limit what you pull in. I am geussing your transactions are timestamped in some way?. You can adjust the the SQL in the transform task dynamically using a parameter whihc reads its value out of a global variable.
You can set the global variable dynamically using a piece of script. This will limit the data pulled through and should prevent you needing to figure out whihc data is duplicated and which isn't.
You could run the DTS multiple times, once for each store using a different parameter to restrict your data selection to just the current store.
If a store fails to import or returns 0 records to import you could always re-run that store later either manually or programtically.
Timothy Peterson has an excellent book out about DTS programming whihc covers this stuff in a far more approcahable manner than the Books Online docs.
Originally posted by FunkyD
Our Informix server is struggling with all the reports we run and so we are thinking of making a dedicated server for reporting.
SQL is an obvious choice because we have it already for our retail system.
However, the challenge is how to download the data we need each night. DTS works a treat but it is the volume of data that is the problem.
We are a retail operation and we need to download the transactions from our Informix server into SQL. This data gets into Informix from the EPOS system in our stores.
What we don't want to do is download everynight the entire back history of transactions. We could do this by using the date of the transactions but we discovered it wont work.
The problem is that if a store doesn't post their transactions e.g. because of a system failure then these will get missed.
What we need to do is record which transactions are downloaded into SQL and then compare this against what is on the Informix server and then download the difference each night.
We thought of adding a flag onto the Informix server but we are not able to make any modifications to it.
I think we could log the downloaded transactions in a SQL table and then use this as a record of what has been downloaded. We could then run a query that compares this to what is on the Informix server.
With the right indexes I think this could work really well. Any thoughts? Incidently the two servers are separated by a 512Kbps wan link......
I haven't lost my mind, there's a backup on one of these floppies, somewhere.