fuzzyocelot
Programmer
Hi everyone!
We have a SQL 2000 database where 60 tables are refreshed every morning from an Oracle database. This is done via 3 DTS packages which we are running as encrypted DTSRun commands through a SQL job.
We are in the process of migrating to SQL 2005 (sp2). So I copied and restored this database onto our dev server where we have a 2005 instance installed. I changed the compatibility level to 90 and all seemed well. I copied the corresponding DTS packages over as DTS packages under Legacy (note: we installed the 2000 DTS Components for SQL2005). I then changed the connection information without any problems. I used the dtsrunui utility and re-generated the encrypted DTSRun commands which I inserted into a SQL job. I scheduled this job to run nightly to import the data into the database for testing.
I haven’t rewritten the DTS packages in SSIS because they’re complicated and would take me a while to do. I tried migrating them a while back but ran into errors. So I’m hoping to at least get the DTS working for now.
Anyway, the job keeps getting “stuck” on the first package. It was working but lately it hasn’t been and I don’t know what changed in that particular package or the system. The place where it gets stuck in the package keeps changing. It’s not the same place every time. This package works fine in production. The job starts at 2:00 am and when I come into work around 8:00 am, it’s still running. That’s what I mean by “stuck”. Normally the entire job takes under 20 minutes to run. I can run the encrypted DTSRun commands manually (via the command prompt) without any problems. The SQL agent is running under the LocalSystem account (I know we need to change it to a domain account with minimal privilieges). I can open the package and run it just fine. I ran a SQL trace and it doesn’t show any errors. When it gets “stuck”, the trace just stops. I also did a dbcc inputbuffer command and it shows the same thing the trace does when it stops. It keeps changing, though, which I find strange. One time it’ll stop on an insert statement after the data has been inserted. Some times it stops during a truncate table statement. I can’t find a pattern. It just varies.
Does anyone have any ideas about what could be wrong?
Thanks!
We have a SQL 2000 database where 60 tables are refreshed every morning from an Oracle database. This is done via 3 DTS packages which we are running as encrypted DTSRun commands through a SQL job.
We are in the process of migrating to SQL 2005 (sp2). So I copied and restored this database onto our dev server where we have a 2005 instance installed. I changed the compatibility level to 90 and all seemed well. I copied the corresponding DTS packages over as DTS packages under Legacy (note: we installed the 2000 DTS Components for SQL2005). I then changed the connection information without any problems. I used the dtsrunui utility and re-generated the encrypted DTSRun commands which I inserted into a SQL job. I scheduled this job to run nightly to import the data into the database for testing.
I haven’t rewritten the DTS packages in SSIS because they’re complicated and would take me a while to do. I tried migrating them a while back but ran into errors. So I’m hoping to at least get the DTS working for now.
Anyway, the job keeps getting “stuck” on the first package. It was working but lately it hasn’t been and I don’t know what changed in that particular package or the system. The place where it gets stuck in the package keeps changing. It’s not the same place every time. This package works fine in production. The job starts at 2:00 am and when I come into work around 8:00 am, it’s still running. That’s what I mean by “stuck”. Normally the entire job takes under 20 minutes to run. I can run the encrypted DTSRun commands manually (via the command prompt) without any problems. The SQL agent is running under the LocalSystem account (I know we need to change it to a domain account with minimal privilieges). I can open the package and run it just fine. I ran a SQL trace and it doesn’t show any errors. When it gets “stuck”, the trace just stops. I also did a dbcc inputbuffer command and it shows the same thing the trace does when it stops. It keeps changing, though, which I find strange. One time it’ll stop on an insert statement after the data has been inserted. Some times it stops during a truncate table statement. I can’t find a pattern. It just varies.
Does anyone have any ideas about what could be wrong?
Thanks!