April 1, 2010 at 5:46 pm
Okay, I’m an SSIS rookie, but I can take the heat. Let me have it if I’ve left out important details or I’m doing something stupid here. 🙂 I’ve searched high and low for a solution on this.
The goal is to have a scheduled Agent job copy a group of tables from an old Pervasive(8.5) DB 2003 R2 (x86) server to a local SQL Server 2005(x64) database. Here’s what I have done:
Starting with just 1 table, I created a DTS package that simply queries (*) one of the Pervasive tables, and returns about 50,000 records to the destination table. The source connection is using an ODBC provider. The destination connection is localhost. When I run it through the debugger, it runs perfectly, returning all the expected records to the local DB in less than 1 minute.
After deploying the package, I set up an Agent job that:
1.clears the destination table
(delete from table…)
On success- Go to next step.
2.Runs the dtsx using the x86 dtexec. (I couldn’t find x64 pervasive drivers that would connect)
Here’s my cmd line-"D:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\dtexec.exe" /FILE "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\MaxPartMaster.dtsx" /CHECKPOINTING OFF /REPORTING E
On success- quit and report success.
After testing step 1 (runs <1 second), I added step 2. When the job executes, it runs step 1, then “hangs?” on step 2. I’ve waited for up to an hour, the whole time the job status showing “Executing: 1(step name)” Here’s the part that has me baffled; the destination table is populated with every source record during the execution, just as it does when debugging from Visual Studio, and dtexec reports success, but the job never ends.
When I run the package from the cmd line, I get similar results. Prompt shows the start time, finish time, elapsed time and DTSER_SUCCESS (0). The table is populated, but no matter what I try, I have to manually stop cmd.exe. Are there any suggestions on logging (besides Job History/SQL Agent) that may offer some clues? Has anybody else run into this?
Please help me avoid yanking this @#$% Pervasive server out of the rack and throwing it into the beautiful pond that flanks our campus. I don’t want to hurt a duck.
April 1, 2010 at 6:46 pm
Definitely add logging to the package.
http://msdn.microsoft.com/en-us/library/ms167456.aspx
This package ends and terminates properly when run from bids?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 7:19 pm
Sorry, I didn't clarify that real well. If I run it out of bids, without debug, it doesn't terminate properly. It does populate the destination table properly, but the cmd.exe pid doesn't terminate. Cmd.exe remains open, with all the log info including PackageEnd, dtexec:DRSER_SUCCESS (0) etc. It's not locked up or anything, I can simply "x" out of the cmd prompt without force. Does that answer you question?
Thanks for the response!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply