ETL Extraction syntax problem
Hi Experts. I am fairly new to SSIS and have been trying to perform a simple task. I am replacing an Access DB which has a linked table to a QAD MFGPro enterprise system. In Access, there was a linked table, and then a query run against the linked table to filter the results based upon date criterion. The Access SQL was SELECT QADDB_wo_mstr.wo_lot, QADDB_wo_mstr.wo_due_date, QADDB_wo_mstr.wo_part, QADDB_wo_mstr.wo_nbr, QADDB_wo_mstr.wo_status FROM QADDB_wo_mstr WHERE (((QADDB_wo_mstr.wo_due_date)>Now()-30 And (QADDB_wo_mstr.wo_due_date)<Now()+60) AND ((QADDB_wo_mstr.wo_status) In ("F","A","R","C")) AND ((QADDB_wo_mstr.wo_rel_date)>Now()-180) AND ((QADDB_wo_mstr.wo_site)="1000")); So trying to perform the same function in a Data Flow task I get the ODBC connection and then can perform a select of coloumns SELECT wo_lot, wo_due_date, wo_part, wo_nbr, wo_status, wo_rel_date FROM QADDB.wo_mstr WHERE (wo_site = '1000') AND (wo_status IN ('F', 'A', 'R', 'C')) but I can't figure out the syntax for the date criterion. I have tried numerous things including adding to the where statement wo_rel_date > DateAdd('dd', -180, GetDate()). When exiting the ADO.Net Source Editor the system complains about not being able to understand. I suspect that there may be an issue with the wo_rel_date coming in the souce as a 99/99/99 format which is mm/dd/yy. Perhaps that needs to be converted to something different? Any suggestions would be greatly appreciated. I have been stuck on this point now for 2 days. Thanks in advance, Chris
November 22nd, 2010 12:27pm
Try using SSIS expression just the way it explains here http://www.codewrecks.com/blog/index.php/2010/04/13/ssis-use-parameter-with-ado-net-source-in-dataflow/ Gaurav Gupta http://sqlservermsbiblog.blogspot.com/
November 22nd, 2010 1:19pm
In .net-Datasource you have to use the statement for the accessed Database Server. For Oracle use Oracle syntax, for DB2 use DB2 syntax. For your Server use the syntax for that. It ist shurely not equal to Access.
November 22nd, 2010 1:46pm