DTS date stamp
-
Don't you hate that
The ActiveX package now works whhoooohhoooo
........butI run the package manually and noticed it creates the date as part of the ActiveX package. It comes up with a dialog box with "20040714", you press O.K and then another dialog box comes up with the file output eg \\sun\kris\I52_Invoice20040714.txt (Which is exactly what I want)
But then it runs the actual T-SQL script, but when you look at the output file it is still just I52_Invoice.txt.
When I set it up to run on a schedule it just hangs. It should take between 1 to 10 seconds. I let it sit there for 1 hour and it still hadn't finished.
What am I doing wrong?
Thanks,Kris
-
Hmmm,
First thing to check is that the ActiveX Script is doing what its supposed to be doing:
1. Right-click on the ActiveX script and select "Execute Step". Click the message boxes when prompted.
2. Open your "Text File (Destination)" connection. Check that the filename has been changed appropriately. If it hasn't let me know and we'll take it from there.
The reason your package is hanging when run as a schedule is quite simple. The ActiveX script pops up 2 message boxes right? Unfortunately because the package is running under a schedule it doesn't pop them up to your screen. So effectively your package is sitting there waiting for user input that it is never going to get.
Message boxes are for debugging purposes. When you actually run your packages under a schedule you must ensure that all your msgbox lines of code are commented out using the apostrophe notation which by now you should be pretty familiar with
Keep going. You'll get there eventually.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Well that makes sense.
I've commented the messages out and it runs fine until I run the whole thing has a schedule again. This is the error message:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21) Error string: Error opening datafile: The system cannot find the file specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: -2147024894 (80070002); Provider Error: 2 (2) Error string: Error opening datafile: The system cannot find the file specified. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
It's similar to the one I sent you earlier but not quite the same.
On a good note it's not hanging, on a bad note it doesn't dump any file. Also I checked when I look at the "File Text (Destination)" it does seem to change the name of the file to the date.
What have I done now?
Thanks,Kris
-
OK, first thing. Changing the text file destination at run time does just that - changes it at runtime. It is not persistent - i.e. changes made dynamically to the package at runtime will not exhibit themselves when you re-open the package afterwards. Note however that if you right-click on the ActiveX task within the DTS Designer and click "Execute Step" then it will change the text file location within the package and it will be persistent.
As for why it fails - not so sure about that one. I understand from your post that if you run the package manually (e.g. Right-click on the package and select "Execute Package") it runs successfully and outputs the file but if you run it from a SQL Server job it fails. Is that correct? That suggests to me a permissions error.
I think that if you run a DTS package from a SQL Server job it runs with the permissions of the job owner (You may have to verify that - it may be that it runs with the permissions of the startup account of the SQL Server Agent service). I'm wondering if this account has permission to access the network resource that you are trying to output to. If it is running under the localsystem account then that won't have access to a network resource.
A good test would be to change your ActiveX script so that it sets the text file location to something local (e.g. c:\152_invoice20040714.txt) and see if it works. If it does then you can be fairly sure its a permissions problem.
I'm SO determined to get this sorted now. It feels like a pet project
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Try these steps..
1. Map the file location as a drive (like F:\folder) to the server where the sql server is running. Specifying direct server path does not work.
2. Run 'dcomcnfg' exe and add in the account that SQL Server agent is running under. If it is running unde SA account, then choose 'Everyone'.
Hope this helps!!!
-sundar
-
This is becoming quite the project isn't it
O.K here's the lastest problem. It seems to be dumping files properly.....but (you knew there has to be a but
) What is suppose to happen is every night the updated data is dumped in the file and then another app imports into it's own DB (Unix), once that has been successful it deletes it out of the folder.Now to the problem. It seems if the file is removed it is going back to calling it "I52_Invoice20040714.txt" regardless of the current date.
I've checked this a hundred times and the same thing happens. If I leave the file there from the previous day it dumps a file called "I52_Invoice20040714.txt". I think you get the idea
So now I pose two questions to you.
1. How do I get it to do the current date regardless of what files are in there?
2. How do I add time to the end of it as well? eg "I52_Invoice200407150930.txt", So if I need to run it more than once a day because the user needs info that was put in an hour ago and so it doesn't over write the previous dump.
Thanks,Kris
-
Hi Kris,
OK, easy part first. To output a time as well as date stamp change the code to the following:
Function Main()
Dim dt, dtstr
dt = Now
dtstr = CStr( DatePart( "yyyy", dt ) ) & _
Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "d", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "h", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "", dt ) ), 2 )
msgbox dtstr
DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource = "\\sun\kris\I52_Invoice" + dtstr + ".txt"
msgbox DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource
Main = DTSTaskExecResult_Success
End Function
I do not understand why it may be reverting to the wrong date - I'm stumped I'm afraid. Does it run successfully if you run the DTS package manually?
BEFORE RUNNING the package try changing the file location to something arbitrarily in the past (e.g. I52_Invoice19000101.txt) and then run it under the schedule. What file does it output then? If it outputs I52_Invoice19000101.txt then your ActiveX script isn't working correctly.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I've been having a lot of problems posting lately. I hit "Post Reply" and deletes everything I just wrote, so please bear with me.
It seems to me it is the ActiveX script because I cleared everything so the only thing the file was called was "I52_Invoice.txt". I run the ActiveX and it changes the file in the test destination to "I52_Invoice200407151807.txt" which is the time I first ran the script. So it seems the first time the activeX script is run is the output that sticks.
The other thing is if the file doesn't already exist it won't complete it via schedule.
I think that it is also the "transform data task properties" is not set up properly, because as you know I have no idea what I'm doing. Are you able to tell me what sort of things should be in those tabs.
To give you some idea when I run it within the design package this is the error message I get.
"Error Source : Microsoft data transformation services (DTS) Package
Error Description : The data pump task requires transformations to be specified"
As I put before the error message I have no idea what that should be I seem to have 4 of them "DTS Transformation_1 etc"
Again thanks heaps for your help
Thanks,Kris
-
O.K I've completely stuffed it now, it's not even changing the name of the file in the test destination. Before it but the date and time on the end now it's not doing anything regardless of how I run it
Function Main()
Dim dt, dtstr
dt = Now
dtstr = CStr( DatePart( "yyyy", dt ) ) & _
Right( "0" & CStr( DatePart( "m", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "d", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "h", dt ) ), 2 ) & _
Right( "0" & CStr( DatePart( "m", dt ) ), 2 )
'msgbox dtstr
DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource = "\\sun\kris\I52_Invoice" & dtstr & ".txt"
'msgbox DTSGlobalVariables.Parent.Connections("Text File (Destination)").DataSource
Main = DTSTaskExecResult_Success
End Function
Heeeellllllpppppp
Thanks,Kris
-
First thing, the last Right(blah blah blah) line is incorrect. It should read:
Right( "0" & CStr( DatePart( "n", dt ) ), 2 )
rather than
Right( "0" & CStr( DatePart( "m", dt ) ), 2 )
Although this won't cause a problem.
When you say its not doing anything - is it completing successfully or does it error?
I'm sending you a private message with my email address in it. If you like you can save the package as a structered storage file and send it over to me to take a look. (Having trouble sending the private message due to the overall crap-ness of the site over the past few days).
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
For anyone thats interested/curious....
The reason it wasn't working properly was because Kris had missed off a precedence constraint hence the export was occurring before the ActiveX script completed, hence it was always dropping the file to the same place with the same name.
Regards
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I can't thank you enough Jamie, I was going insane over this.
Thanks Heaps
Thanks,Kris
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply