ado source query reverting when ssis package is saved

• PGarberick

  SSCrazy

  Points: 2098

  More actions

  September 30, 2014 at 7:11 am

  #150243

  I am trying to streamline an ssis package that currently is hogging the resources on a sql server. The ADO source query against a mysql database is pulling every record from the dawn of time (DOT) and then going through several loops and turns. In 2006 this made sense...8 years later, DOT is pulling thousands of records instead of hundreds.

  Here is the query (fieldnames have been disguised to protect the innocent):

  SELECT r.ca_ref_id, r.field1, r.field2, r.field3... r.load_dt, r.field10, r.load_msg, CURDATE()

  FROM ca_ref r

  WHERE r.ca_ref_id > 0;

  Now the WHERE reduces the number of records but I would like to add:

  and r.load_dt > (some date this year since we're supposed to be collecting new events happening)

  I ran this statement on the the mysql server and there were no errors. Copy/pasting the new query into the ado source step or manually adding the additional criteria in the ado source query statement is just fine when I click the ok box and close the configuration box. However, when I save the ssis package the "AND r.load_dt > somedate" line drops off the ado source query.

  I'm not sure what triggers the ca_ref_id to be > 0 because this is a third party db but clearly, running through 2006 - 2014 data every half hour needs some improvement.

  Any suggestions?