importsql server
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.
You could drop the foreign keys, but if the tables are large and/or you have busy environment, be sure to test your insert times before you do anything on production. In regard to new keys, it shouldn't throw a problem, but if you're trying to insert duplicate keys then you'll get errors. Also, be sure to insert parent entities first (if you don't drop foreign constraints first!) then child entities.
Choose to save the package instead of running it right away.
Then edit the dtsx file and on your copy task check "keep nulls".
Then run the dtsx.
I didn't test it but think it will solve your case.
Testing the suggestion on the comment on express with advanced services... it worked well:) You create the destination table, set a null default on the column and after importing data with the dtsx you remove the default or set it to whatever you want. dtsx import will replace nulls with the default value of the column when you can't check the "keep nulls".
create table dbo.test (
a int null
, other columns ...
)
alter table dbo.test add constraint x default null for a
--Run Import task here
alter table dbo.test drop constraint x
Best Answer
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.