August 31, 2009 at 11:51 pm
Hi,
I run into an issue with SSIS.
I'm trying to copy data from a db on sql 2005 SP2 to sql2000 using Transfer SQL server object.
I set
CopyData = TRUE
ExistingData = Replace
and specify the table to copy.
When I run the task as SA it empties the taget table and then inserts the data.
When I run the task as db_owner plus different combinations (ddl_admin among others), it transfers the data but it appends it to the target table instead of truncating first.
Doesn't db_owner have truncate rights? When I manually truncate the table it works.
What have I missed?
How should the task be set up?
Thanks for any help or input
/ricfors
September 3, 2009 at 1:33 am
I've always used a dataflow with OLEDB source and OLEDB destination for these transfers with an execute sql task preceeding it to truncate the table. I don't know if it's the best method but it works every time.
Something (I can't remember what or where I saw it) led me to believe there were issues with the newer .NET providers and SQL2000 which may be your problem.
September 3, 2009 at 5:45 am
Hi,
Thanks for your input.
I'll check it out.
For now I set it up with sp_foreachtable as an sql task which truncates the tables and then the transfer sql objects. It works fine so far. We'll see how it works when I put it into production environment.
Thanks again
ricfors
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply