Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SQL Server Job set up to import data from a table to a table on another server, using the Import/Export data wizard.

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

The source is SQL Server 2012 and the destination is SQL Server 2008.

share|improve this question
add comment

3 Answers

Take a good look at the control flow, both in your SQL Agent job, and the SSIS package it's running. I suspect there's a step in one of them that cleans out the source table after the work is finished, and this step is running regardless of whether the import succeeded or not.

Or there may be an unrelated cleanup job that cleans out that import table.

share|improve this answer
    
I don't know of any cleanup job. And if that would be the case, the table would be cleared each time it's ran, which it isn't. –  Rivka May 20 '13 at 14:21
add comment

In one instance, the job failed (with, I believe a SQL connection issue), and with that, the data from both tables were deleted. The tables on both the source and destination server were removed.

Does this make sense? If anything, why would the data be removed from the source?

No this does not make sense, unless you have selected it to delete destination data when creating SSIS package using Import/Export Wizard.

You must have selected :

Delete rows in an existing destination table. - See step 3 in BOL.

enter image description here

The same might be selected for source tables.

Check the package, you will find the incorrect setting.

share|improve this answer
    
Sure I had the delete rows checked off. But that doesn't explain why the data in the source table was deleted. –  Rivka Jun 10 '13 at 13:20
    
@Rivka Look in the sql agent job as well as the package itself, there should be as there is no way that SQL Server will decide to purge your data unless IT is instructed (delete or truncate). If you are still not able to find out, then there might be a rouge process -- fire up a sql trace to see whats going on the database server. –  Kin Jun 10 '13 at 13:23
add comment
up vote 0 down vote accepted

One thing I noticed with this import job was the security option was something other than the default, "encrypt sensitive data with user key". After changing it back to the default, I didn't have this problem again (so far).

Now, how and why that would cause this behavior, I don't know.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.