August 21, 2009 at 5:51 am
Hi!
I wonder if anyone can offer me some advise or assistance on a problem I am experiencing using SSIS. I'll give you some background.
I work at an insurance company who use an insurance sales/administration package that runs on SCO UNIX/INFORMIX.
In order to produce MI on this system, I need to copy core data out of the informix database over to my SQL2005 VMWare machine, then run a series of T-SQL stored procedures over the copied database.
In total I copy parts of approximately 40 tables on a daily basis using the Esker TUN ODBC driver tunodbc200.ifx - each of these table copies is in it's own .dtsx file stored on the SQL server. The dtsx pack simply truncates the destination table and downloads a fresh copy of the data via ODBC.
Column definitions in destination tables match those in the source database, there are no validation warnings or messages at all in teh packages.
I wrote a stored procedure which logs the time at the start of the copy, kicks off the dtsx file using xp_cmdshell, logs the time it completes and sets a success flag before rebuilding the indexes on the table it just copied.
a SQL server agent job kicks of this stored procedure 40ish times sequentially, each time specifying the next table that needs to be downloaded. Total download time is about 5 hours - this is done nightly starting at 10PM and continues on to 3AM.
one of the tables fails to copy - successfully downloading this table is very important as it provides a link between all the other tables and is required for about 35 of our 50 reports.
The table contains just shy of 3 million records. every single record makes it's way successfully into the destination table, however whilst commiting to the database the ssis package reports a communication link failure, saying that the source database had experienced a problem during the post-execute phase. The ODBC driver reports 'Network Error'.
We have run a trace using wireshark and there is nothing untoward or unexpected on the network.
This is causing me real difficulty as the reports won't run at the scheduled time of 4AM if there was any report of a copy failing - even though all the data is there. Now I wouldn't mind setting the success flag to True on that table every morning and kicking off the reports manually except that the VM is a single CPU affair that runs about 4 business critical applications and running the reports during the day causes the other systems to run very very slowly.
Why is this Communication Link Failure occuring and what can I do to stop it? I have already tried re-creating the job several times, all to no avail. it was working fine a few days ago just suddenly stopped. All the other tables copy without incident. Another thing worth noting is that the source database runs on 2 separate servers, both of which are physically identical, one is used for live, the other receives a data transfer overnight to bring it's database up to date. This error occurs when copying from either of the 2 source servers.
Many Thanks for you assistance!
Ben
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply