| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2010-11-18 : 10:43:58
|
| I've been looking into an issue we had with a DTS package last night. The package simply imports data from a handful of tables on a remote server and has been working fine for many months.For some reason last night the data from one of the tables didn't import. The job package logs for last night indicate what steps failed but their is neither an error code nor an error message. The server and the SQL server logs have nothing strange in them. The DTS package is logged to a text file. I looked in that and there is nothing in there to indicate a failure that I can see. There is however something odd. Previous entries in there have a summary at the end of the job. The entries for last night don't have this.Running the package manually works fine. Running it as a scheduled job currently works fine.Basically I have run out of ideas of how to troubleshoot this so if anyone has any suggestions I would be grateful.thankssteve-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-18 : 11:11:00
|
quote:
Originally posted by elwoos
I've been looking into an issue we had with a DTS package last night. The package simply imports data from a handful of tables on a remote server and has been working fine for many months.For some reason last night the data from one of the tables didn't import. The job package logs for last night indicate what steps failed but their is neither an error code nor an error message. The server and the SQL server logs have nothing strange in them. The DTS package is logged to a text file. I looked in that and there is nothing in there to indicate a failure that I can see. There is however something odd. Previous entries in there have a summary at the end of the job. The entries for last night don't have this.Running the package manually works fine. Running it as a scheduled job currently works fine.Basically I have run out of ideas of how to troubleshoot this so if anyone has any suggestions I would be grateful.thankssteve-----------I used to think I wasn't a morning person but it never got better in the afternoon
did you check whether there was some network issue on remote server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-18 : 11:56:55
|
quote:
Originally posted by X002548
rewrite the process as a sproc?Brett
I suggeseted that too Brett but it wasn't a well received.Visakh we aren't aware of any issues on the remote server or network (at least none that anyone will admit to). Basically all but one of the tables copied successfully. For the one that failed, the package does a few things on the server before trying to access the remote box and they failed too.I've just taken another look at the DTS logs and realised contrary to my earlier statement there are no entries at all for yesterday (I wasn't reading the log correctly, it's been a long time since I looked at a DTS log). For each of the 5 tables copied, there are a few sql commands run then the copy is performed, these are all in parallel so we essentially have 5 distinct things happening in the DTS. I have now realised that last night 4 of these 5 had a single failure. for two of the tables it was one of the SQL commands that failed, for the other two it was in the copy from the remote box. We've now decided to wait to see if the issue recurs tonight but I am wondering if it is connected to these operations that are running in parallel, though at the moment this appears to be a one off.thankssteve-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-11-18 : 12:01:18
|
| ok..anyways would be interesting to know more on this.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-19 : 04:42:53
|
| Brett it's a pretty short process so your suggestion is quite feasible. Can the profiler be scheduled to start/end? Just as an update to this, it all worked fine last night!Many years ago now I was sent on a DTS course, I think I will have to dig out the manual over the weekend-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-19 : 11:02:37
|
| sounds very much like a network issue. or possibly, the data wasn't yet available on the remote server (timing issue).check the logs on remote server.i have no idea how making a dts into an SP would help. if anything, it would make it more difficult to troubleshoot.the dts logs to a text file? can you post the log for the run that failed? |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-22 : 05:45:22
|
| It all worked fine over the weekend. Russell, I've asked the admins on the remote box to check it but they haven't yet got back to me. As far as the dts logs (the text file) go, there is simply no entry for that evening, the dts logs jump from the 16th to the 18th. While some of it could be network issues I don't think it all is and here is why. The package essentially has operations that run in parallel and all do the same thing for a total of 5 different tables. It runs some sql locally before pulling the data from the remote box. This is the sequence - 1. Do a select count from the remote box (this is intended to see if the box is available)2. Do a test insert into the target table3. Do a test delete from the target table4. Truncate the target table5. Copy the data from the remote box to the target table.The steps have the Success precedent for each of them i.e. if step 1 succeeds do step 2 etc.The target table is on the same box as the dts package so I am unclear why some of these steps are needed, however I am not going to be able to alter this package. On the night it "failed" this is what happened according to the package logs.For table 1: step 4 succeded no entry for step 5table 2: step 4 failed, no entry for step 5 (as to be expected)table 3: step 5 failed (could be network issues)table 4: step 5 failed (could be network issues)table 5: step 1 failed (could be network issues)None of the entries that failed have an error code or description. The only indication of failure is that four (tables 2-5) of them have a run status of 0. If a step succeeds it has a run status of 4.The biggest mystery here is table 2 as that failed on step 4 which is a truncate on the local box. Apart from that I would agree it looks like it could have been a network issue. I'm also a little mystified by the lack of an entry for step 5 for table 1.steve-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 08:14:26
|
| steps 1-3 are silly and should all be removed.no log entry is strange. maybe the job was chosen asa deadlock victim. check windows app logs |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2010-11-22 : 08:58:49
|
quote:
Originally posted by russell
steps 1-3 are silly and should all be removed.
I don't disagree however I'm not allowed to, when I mentioned it the response was "If it aint broke ..."There is nothing at all in the windows logs at or around the time that this occured. The dts has run successfully since late May this year. It's a very quick process that never takes more than a minute to complete.I've just noticed something else odd about this. Looking at the Job History there is no entry for the date it failed. This is odd as most of the job appears to have worked as normal i.e. it must have been started as a job. We have had some issues in the past with Windows Scheduler but I always assumed that the scheduler in SQL Server was a little more robust.-----------I used to think I wasn't a morning person but it never got better in the afternoon |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 10:08:00
|
| one other thought...check to make sure it wasn't running long on the previous run. meaning it never finished the night b4 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-22 : 12:03:28
|
quote:
Originally posted by elwoosLooking at the Job History there is no entry for the date it failed.
It didn't launchquote:
This is odd as most of the job appears to have worked as normal i.e. it must have been started as a job. We have had some issues in the past with Windows Scheduler but I always assumed that the scheduler in SQL Server was a little more robust.
Why do you say it must have started?One of the thing Ido for ALL my sprocs, they got logged to a proc_exec_log table, outside of any transactions, so I KNOW if they were executed or notYou could probably add a log steps to the job(S)"DTS is useful if you don't know SQL"[/quote]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 12:09:58
|
| "DTS is useful if you don't know SQL"This is just silly. DTS is a fully robust ETL program. It is poorly documented, yes. But it and T-SQL each are important tools. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 13:35:22
|
| DTS does a lot more than transformations.DTS has verbose logging built in.DTS can access remote servers (of any platform) without creating linked servers.DTS exposes a full object model which can be accessed by script.DTS natively supports two scripting languages (in addition to T-SQL).DTS has global variables as well as local variables.DTS can pass results, variables, parameters from step to step.DTS has precedent constraints (workflow) built-in.DTS can create #temp objects and access them from multiple steps.DTS has a wide array of built-in objects for common non-sql tasks.To name a few.There are many applications where DTS is the better tool. If you are willing to allow sp_OACreate (OLE Automation) on your servers, then you can literally do anything in a stored procedure. That doesn't make it the right tool for the job. You can already do anything with DTS.The only downside to DTS is that it is poorly documented.I have many jobs that execute stored procedures. I have many jobs that execute SSIS packages. I used to have many jobs that executed DTS packages, but they've all been migrated.DTS is not a replacement for stored procedures. It is a different tool, one which is extremely valuable and belonged in every DBAs toolbox. Today SSIS should be used. But if you're still using SQL 2000, ignoring DTS is throwing one of your best tools away.Whoever you quoted that from either doesn't understand/know how to use DTS, or they were being silly. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 13:38:28
|
| as to the OP, a very common cause of a job not starting, is that it was still running. Check that 1st. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 19:49:01
|
| one more important thing I forgot to add...DTS is WAY FASTER than stored procedures for most data pumps.(becuase it does a bulk insert in the background, unless you choose to make it a fully logged operation) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-22 : 20:34:19
|
| ahemIF You do a Transformation with DTS...It MUST apply the Transformation when it moves the data..which is a cursor, or at the very least a page at a time cursor..a Sproc I would use would use bcp to load a staging table, THEN use set based logic to audit AND Transform the dataI tell you nowALL THese fraking GUI tools are a wasteWhat did they figure out out a way to suck in flat files more effeciently...or are you talking table to table in Native modeNope...bcp in to stage, check out the data, the migrate in a set based manner...oh...and you elude to a non logged operation...which you KNOW is not trueBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-11-22 : 20:59:15
|
| If you're no longer using SQL 2000, then I'll let it go. If you are, I suggest you study up on it because most of what you said isn't true. Are you pretending that SSIS is a useless tool too?And like I said, DTS is not just a GUI tool. It supports 2 scripting languages. This is where the real power comes in.If you're pumping single SQL Server table to SQL server table, or just bulk inserting one or two flat files, then there's no need for it.But if you're doing anything much more than that, you're throwing away your best tool.How will you populate an MS Access db, or an Excel file (multiple tabs) in a stored proc, while dynamically naming the file based on the current timestamp and server name, then copy it to a folder that is chosen dynamically based on where it needs to go, then maybe even send it to the proper clients web service of ftps server and handle the http response from the service, resending on anything other than a 200?How will you import data from an unkown number of spreadsheets or flat files, with unknown names, then rename and move the files to an archive folder with a stored proc?How will you import packed decimal fields in a stored proc?How will you import DB2/400 hierarchical data sets in a stored proc?How will you import fixed length flat files with no delimiter when you don;t need/want every character in the row?How will you import flat files with header records, detail records and a terminator?They can all be done in a stored proc actually, but there are better tools for the job. DTS is going to be the best choice for the SQL 2000 DBA or developer in most of these cases. |
|
|
|
Next Page
|
Obscure DTS Issue