XopherS
Technical User
- Dec 8, 2006
- 35
- 0
- 0
I have 8 packages that copy data using a query from MS SQL 2000 on machine 1 to MS SQL 2005 on machine 2. My package creation steps:
- Select the MS SQL 2000 server name, use server authentication, and select the db.
- Select local, use Windows authentication, and select the db.
- I write a query to specify the data. The SQL statement parses.
- I select the destination table and click [Edit] to map.
- I delete the rows in the dest. table, make sure things are mapped right, and hit OK.
- I don't execute immediately. I save as a file on my desktop.
- Package protection level is set to the default, "Encrypt sensitive data with user key".
- I name the file, and finish out. The package is saved.
I do this 7 more times, then create a job. In the job, the only thing I do that's more than "add the step, locate the file" is change the on failure action to "keep going".
I also do these steps while saving them to SQL Server, only I change the package protection level to "Rely on server storage and roles for access control".
Either way, one or two may complete successfully when I run the job, but the rest fail, and sometimes all of them fail.
When I test this by executing them immediately and not saving them or running them in an agent, they work fine.
What gives??
- Select the MS SQL 2000 server name, use server authentication, and select the db.
- Select local, use Windows authentication, and select the db.
- I write a query to specify the data. The SQL statement parses.
- I select the destination table and click [Edit] to map.
- I delete the rows in the dest. table, make sure things are mapped right, and hit OK.
- I don't execute immediately. I save as a file on my desktop.
- Package protection level is set to the default, "Encrypt sensitive data with user key".
- I name the file, and finish out. The package is saved.
I do this 7 more times, then create a job. In the job, the only thing I do that's more than "add the step, locate the file" is change the on failure action to "keep going".
I also do these steps while saving them to SQL Server, only I change the package protection level to "Rely on server storage and roles for access control".
Either way, one or two may complete successfully when I run the job, but the rest fail, and sometimes all of them fail.
When I test this by executing them immediately and not saving them or running them in an agent, they work fine.
What gives??
