title: SSIS ETL package intermittently loads partial data
body: <p>I have an SSIS package that uses SQL statements to pull data from 10 Postgres databases with identical schemas but different data.  The package is written into a job with 10 tasks, each task has hardcoded values indicating which server to pull data from.  The source SQL doesn't change, and the db schemas across 10 source servers are identical, but the data is different.  Basically, we're running a data extract against 10 instances of an app's backend on a loop.</p>
  
  <p>We use a 32-bit ODBC driver to connect to Postgres from SQL Server 2008 R2 via system DSN connections.  We don't use fetch because it works for larger reports but fails for smaller ones.</p>
  
  <p>On the target SQL Server side, after each run the result data from each SQL statement just gets appended to the same table with a source id.  This way we can query a single table for any or all of the 10 sources' data.  Each day the package runs, everything gets truncated and reloaded.  On the source side, the data is staged for us via backup/restore around 6 am; our jobs start around 8 am.  The SSIS ETL dumps the data into the intake bucket, and the actual final insert into the user-facing tables happens via a stored procedure.</p>
  
  <p>To summarize,</p>
  
  <p>We have a package with several SQL data transformation tasks.  It hits 10 PG databases one at a time via global variables in job tasks.  It loads data into intake tables, and then a stored proc inserts into final customer tables.</p>
  
  <p>Here's the weird problem I am having -- sometimes everything loads correctly, but sometimes I only get half of the data, sometimes nothing at all.  Some tasks complete, some say they are complete but target tables contain no data.  When I run the package manually (by kicking off the job via t-sql or opening it in Visual Studio), everything works 100% of the time.  As soon as I schedule the package, I can't get any consistency.  I've tried scheduling them during different times of day to no avail ... I checked with upstream people to make sure I am not pulling when they are pushing, but all that seems to be OK.</p>
  
  <p>The only thing I can think of is some obscure timeout settings in SSIS that kill the jobs when they run long.  I can understand all results or no results, but halfway loaded tables I don't understand.  Can anyone point me in the right direction?  Thanks.</p>