SQL from SQL Server DTS to Navision: Date problem

Hello: (I’m not Navision developer… just a datawarehouse architect) Navision 2.60 SQL Server 2000 From SQL Server Data Transformation Services via ODBC I’m trying to read data from a Navision table filtering with the today date (In a SQL Server DTS). If I write: SELECT * FROM MYTABLE WHERE (MYDATE = { d ‘2005-06-30’ }) It works! Now I want to change 2005-06-30 for something like today, sysdate… to execute this everyday getting only the new day’s records. Any ideas??? THANK YOU, Regards Fran

Hello: Thanks to Simon Hayes I found that the key was to build dynamicaly the SQL string in SQL Server DTS. This is one solution for the problem (it would be easier if Navision ODBC would allow something like sysdate… anyway…): In SQL Server 2000: I build a DTS with 2 connections (Origin and Target) and a Data Transformation Task between them with all the transformation changes I want. Until here everything as I had before. My goal is to read only the data that I want filtering it with a date variable. For example you have some million registers in a table and want to extract each day just the few new records. Now I add an ActiveX Task with the following code: (Probably there’s an easiest way to do this, I’m not an expert here, but this works…) '********************************************************************** ’ Secuencia de comandos ActiveX Visual Basic ‘************************************************************************ Option Explicit Function Main() Dim pkg, stp, tsk, cus, sql ‘************************************************************************ ’ Dealing with dates in Navision C/ODBC: They have to be <{d’aaaa-mm-dd’}> ’ Being the first d from date… options: t from time and ts from timestamp ’ The Month function will give you just one number if you are before October Dim mes mes = Month(Date) If mes <10 Then mes = “0” & mes end if ‘************************************************************************ Set pkg = DTSGlobalVariables.Parent ’ Notice here that DTSStep_DTSDataPumpTask_1 is the name of the Data ’ Transformation Task Set stp = pkg.Steps(“DTSStep_DTSDataPumpTask_1”) Set tsk = pkg. Tasks(stp.TaskName) Set cus = tsk.CustomTask ’ Construct the sql statement with your date requirements… sql = "select * from MyTable where (MyDateField = {d’" & Year(Date) & “-” & mes & “-” & Day(Date) & "’})" 'Assign the SourceSQLStatement property of the custom task cus.SourceSQLStatement = sql Main = DTSTaskExecResult_Success End Function I put a green (Success) line from this Task to the Origin Connection of the Data Transformation Task, so that the ActiveX scritp runs the first, build the SQL sentence, and then the Data Transformation Task Origin Connection runs the SQL Sentence selecting only the proper data. You can see this works just looking at the Origin Connection, in the SQL box you’ll see the SQL sentence generated in the last execution of the DTS. Regards, Fran

Fran, thanks, we need more members like you. It is soooo rare on thissite for a new user to find a solution to their own problem and then come back and post the information for every one else to share. I hope others ot there will follow suit. [Wow!]