Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kmiller
Starting Member
3 Posts |
 Posted - 2010-04-01 : 21:34:12
|
Okay, I’m an SSIS newbie, 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 BIDS, 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. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-04-01 : 21:58:28
|
| checked that service account has permissions on data source?tried making job step 2 of type SQL Server SSIS Package instead of executing cmd script?Also, what protection level did you save the package with? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kmiller
Starting Member
3 Posts |
Posted - 2010-04-02 : 17:25:08
|
quote:
Originally posted by russell
checked that service account has permissions on data source?tried making job step 2 of type SQL Server SSIS Package instead of executing cmd script?Also, what protection level did you save the package with?
Thanks for the response.Permissions seem to be okay, although I have been bitten before. Maybe I wrongfully assumed that permissions were not an issue since the job is in fact bringing over the requested data without a problem. Any insight there?I have tried using the SSIS Package component. I get an error saying that the source provider was not found. I run into these issues time an time again with the source (Pervasive/Btrieve DB. |
|
|
|
kmiller
Starting Member
3 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
SSIS package won't terminate
Tara Kizer