I'm moving Avaya CMS data from Informix to SQL Server. I have several data flow tasks - one per table, and some of them work fine in any circumstance, while other work fine when executed manually from Integration Services, but will produce different results when run via SQL Server Agent. I created a new package just to test the root.hvdn table (phone data summarized by the half hour). Avaya says the table is supposed to be keyed on row_date, starttime, vdn (the internal number - similar to extension) and vector; in truth, there are some duplicates, but with valid (i.e, different, call data - probably for a call that crosses an interval). The row_date and vdn fields won't map directly, so they go through a converstion where the row_date converts to DT_DBDATE and the VDN to DT_STR. The next step is to see which of the Informix records have been previously imported, so it goes through a lookup transformation matching the coverted row_date and vdn, along with the vector and start time against a SELECT DISTINCT of the same fields in the destination table (which eliminates the dupe keys). So, it there's no match, the Informix data goes through a record count to variable (for the sucess email). The final step is an OLE DB Destination. When executed through BIDS or the deployed package on Integration Services, the package runs fine. When I schedule it or run it from SQL Server Agent it runs, but inserts a zero length value into the vdn field - every other field is fine. In the version of the package with multiple data flows, some tables update correctly every time, and others have this same type of error. It's as though the conversion works correctly in BIDS, but when running from SQL Server Agent, the vdn is converted to a zero length value, which is a no match and results in an append of the entire record set, but without the vdn. I've reduced the number of candidate records from Informix by limiting it to row_dates greater than yesterday. That just gives me fewer appends with zero length vdns. I don't want to truncate the destination table because I need to retain records that Informix has previously deleted - it maintains a two-month history, and I need to go back at least two year (eventually). I'm at a loss. The only thing I can think of is that the destination vdn field is Char(7), but I'm not sure. Suggestions? Insights? Tim Mills-Groninger

I am having same informix server with avaya setup. I have setup a store procedure to insert data into my table at sql server through t-sql script & schedule the store proceude execution through SQL agent job. Through SSIS, it seems that you are applying logic in SSIS package & inserting data into SQL from informix after applying logic. I suggest :- 1) To insert row data same as in informix server to sql server then apply logics in sql server. It helps you in keeping row data at sql server & you can also change the lgin as & when required. Change in logic will not hampper re-populatation of data. 2) Try to run the package manually at the same time on SQL agent run package & gives 0 output. Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

One thing for you to be aware of - executing through BIDS is exactly the same as executing the deployed package. Those two things are different than executing through Agent. Why? Because it doesn't matter where the package file is deployed - it matters what computer is running the code. Much like it doesn't matter where the Excel spreadsheet is saved, it only matters where Excel is running. Given that, it appears to indicate that there are configurations on your machine that are different than on the server. This could be a permissions issue, a component installation issue, a driver issue, ... I would start diagnosis by copying your package, and removing everything after your initial read from Informix. I'd route that read directly to a CSV file. Run that on your system, then through Agent and compare the files. If they are identical, then start moving "down" your data flow before you dump intermediate results to the CSV. Talk to me now on