Dates Using MERANT 3.7 32-BIT PROGRESS ODBC driver
-
Hi
I’m trying to set up a DTS package that will import data from a progress system into MSSQL 2K using the MERANT 3.7 32-BIT PROGRESS ODBC driver. I can connect OK but can’t find a way to use a global variable date to automatically select a given months data.
If anyone has any experience with this I would really appreciate some advice.
Thanks
Geoff
-
When you say "a given months data" are you pulling it over on a schedule and keeping or do you jump around the data a lot?
-
I'm trying to create a regular monthly download to a staging db which will then populate a warehouse for reporting.
If I can't sort something like this it looks like I'll have to select * on the tables I need to staging and then drop the excesss. But the 'header' table has 1.5 million rows and I guess the lines will double this so it seems a bit tedious and wasteful.
-
What type of DTS object are you using for the imports?
-
A data pump from other object to mssql server.
-
You might be able to use an ActiveX script in your data pump task to only transfer the row if the source date column meets a specified criteria. The script below should (I think
) transfer any row less than a month old (using dateadd : month - 1):Function Main()
IF DTSSource("SourceDateCol") >= DateAdd("mm" , -1 , NOW() ) Then
DTSDestination("DestCol1") = DTSSource("SourceCol1")
DTSDestination("DestCol2") = DTSSource("SourceCol2")
DTSDestination("DestCol3") = DTSSource("SourceCol3")
End If
Main = DTSTransformStat_OK
End Function
I haven't tested this, it's pure theory at the moment!
-
There are a few way you could approach, my first thought is using a prebuilt query in the syntax of the origin system that uses date filed minus 1-2 months to make sure you have some overlap similar to "WHERE colDate >= DATEADD(m,-1,GETDATE())" in T-SQL. With this use a Query Driven Data Task object to precheck for the existance of the records and skip those that exist, otherwise do an INSERT.
Another possibility is to have a global parameter and before you step into your tasks alter the query with ActiveX Script to concatinate in the global variable on each. Might could use the same base value for each query and use Replace in VBScript to do something like this (note designed to handle all source query strings for packages so be carefull if there is something you don't want altered)
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Function Main()
Dim oPkg, oTask, oProp
Set oPkg = DTSGlobalVariables.Parent
For Each oTask In oPkg.Tasks
For Each oProp In oTask.Properties
If oProp.Name = "SourceSQLStatement" Then
MsgBox oProp.Value
oProp.Value = Replace(oProp.Value,"1/1/2007",DTSGlobalVariables("myDate").Value)
End If
Next
Next
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
-
You can do it with a global variable, but the trick is that the query has to follow Progress syntax, not T-SQL syntax. From memory we used a date format of DD-MMM-YYYY as a string. Progress also has a builtin function to return the current date. I think it's SYSTEM_DATE() (check docs to confirm that), so you can do things like SYSTEM_DATE() - 30
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply