Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Wierd Date problem with ActiveX task and global variables

Wierd Date problem with ActiveX task and global variables

Wierd Date problem with ActiveX task and global variables

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.

George Oakes
Check out this awsome .Net Resource! http://www.learnvisualstudio.net

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close