Glowworm27
Programmer
Greetings all,
I want to write this Tip I have discovered about DTS, ActiveX Script tasks and SQL Global variables to see if anyone else has similar problems/issues.
I have a DTS Global Package variable defined as a Date called Filedate.
I also have a filename that I parse out to get the date that is embedded into it to get the data date. (each file has the date of the data embeded into the filename such as mydata.20080311.txt the data in this file is for 3/11/2008
I extract the date from the filename, and then use it to extract other data from SQL to put into a report.
Here is the problem I was having...
I put the date into the global variable filedate and then I set another variable lets call it DataDate which is another global Date variable in SQL. I take the filedate and do a DTSGlobalVariables("DataDate").Value = Dateadd("d", -1, DTSGlobalVariables("filedate").Value)
when I run the package from my machine there is no problems.
but when I schedule the package to run, and let the agent handle running the package, the report is wrong. Instead of subtracting one day from the filedate, it subtracts one month instead. What I am guessing here, as I could not find an answer from microsoft, or anyone else for that matter, was that because the Microsoft SQL Global Variable DATE value was in some SQL format, so when the ActiveX script tried to subtract a day with the Dateadd function, it got confused as to which part of the filedate is the month and which part is the day.
Again I am making an assumption here as I have no other explaination as to why this is happening.
So what I have done is changed the Global Variables to strings, and then used the ActiveX Script function FormatDateTime(<date>,2) on all date manipulation code to ensure that the date was properly formated into a Small Date that ActiveX Script could understand.
And this has seemed to worked out well for us so far.
It's amazing how two Microsoft products would interpret Date values differently especialy inside the same program.
I hope that someone can make use of this tip in the future.
Peace
G
George Oakes
Check out this awsome .Net Resource!
I want to write this Tip I have discovered about DTS, ActiveX Script tasks and SQL Global variables to see if anyone else has similar problems/issues.
I have a DTS Global Package variable defined as a Date called Filedate.
I also have a filename that I parse out to get the date that is embedded into it to get the data date. (each file has the date of the data embeded into the filename such as mydata.20080311.txt the data in this file is for 3/11/2008
I extract the date from the filename, and then use it to extract other data from SQL to put into a report.
Here is the problem I was having...
I put the date into the global variable filedate and then I set another variable lets call it DataDate which is another global Date variable in SQL. I take the filedate and do a DTSGlobalVariables("DataDate").Value = Dateadd("d", -1, DTSGlobalVariables("filedate").Value)
when I run the package from my machine there is no problems.
but when I schedule the package to run, and let the agent handle running the package, the report is wrong. Instead of subtracting one day from the filedate, it subtracts one month instead. What I am guessing here, as I could not find an answer from microsoft, or anyone else for that matter, was that because the Microsoft SQL Global Variable DATE value was in some SQL format, so when the ActiveX script tried to subtract a day with the Dateadd function, it got confused as to which part of the filedate is the month and which part is the day.
Again I am making an assumption here as I have no other explaination as to why this is happening.
So what I have done is changed the Global Variables to strings, and then used the ActiveX Script function FormatDateTime(<date>,2) on all date manipulation code to ensure that the date was properly formated into a Small Date that ActiveX Script could understand.
And this has seemed to worked out well for us so far.
It's amazing how two Microsoft products would interpret Date values differently especialy inside the same program.
I hope that someone can make use of this tip in the future.
Peace
G
George Oakes
Check out this awsome .Net Resource!