January 7, 2013 at 9:53 pm
I have created a SSIS to copy a DB to another server. but it keep on failing with this error:
Error: 0xC0024104 at Copy DB: The Execute method on the task returned error code 0x80131500 (An error occurred while transferring data. See the inner exception for details.). The Execute method must succeed, and indicate the result using an "out" parameter.
Task failed:
I have no idea what is the error because all the value are all correct. both SQL are build 2325.
:-):cool:
January 8, 2013 at 1:12 am
Can you run the package in SSDT and post the entire copy of the output screen?
January 8, 2013 at 4:08 pm
anthony.green (1/8/2013)
Can you run the package in SSDT and post the entire copy of the output screen?
SSIS package "c:\users\<user>\documents\visual studio 2010\Projects\Solution SSIS\Project SSIS\Copy DB.dtsx" starting.
Error: 0xC0024104 at Copy DB: The Execute method on the task returned error code 0x80131500 (An error occurred while transferring data. See the inner exception for details.). The Execute method must succeed, and indicate the result using an "out" parameter.
Task failed: Copy DB
SSIS package "c:\users\<user>\documents\visual studio 2010\Projects\Solution SSIS\Project SSIS\Copy DB.dtsx" finished: Success.
I am able to run the package successfully when copying a DB by offline mode. that error is when using the online mode. I am using a local admin account and the service account is both admin in source and destination box, both are sysadmin in SQL instance. the connections are using sa as credential.
:-):cool:
January 9, 2013 at 1:23 am
Do you have any columns in tables called Error or ErrorCode or something similar?
January 9, 2013 at 7:15 am
anthony.green (1/9/2013)
Do you have any columns in tables called Error or ErrorCode or something similar?
hmmm... maybe there are... i'll verify once I've got back to office tomorrow.. it is a vendor database... everything is provided by the vendor... so it could be a SMO issue.. the generating of the scripts?
:-):cool:
January 9, 2013 at 7:17 am
IIRC, I had an issue with online transfer with columns called ErrorCode, as it gets confused with the column vs the SSDT package ErrorCode output variable to say if the package fails or not.
Doesn't like that, so had to script around tables with ErrorCode and do them manually.
January 9, 2013 at 4:50 pm
Is there a way to verify if that is the issue? like where can I get the log/or specific error that causing it? I need to show to higher people that that solution might not feasible in the DB. 🙂 I checked the DB columns using sys.columns and sys.objects and i found "code" column but no like '%error%' one aside from 'crerrors' which i think is from a system table.
I tried to other DB like the ASPState one and it goes well, so it is for that specific DB only.
:-):cool:
January 10, 2013 at 1:22 am
If there isnt a collumn that contains error, then it wont be that problem.
Does the database need to done online? Can you not setup logshipping for example and then when ready to migrate your in sync? Or even replication?
This is a long winded approch, but I would script out the database, re-create it on the destination then use the import export data wizard to create a bulk SSIS package to transfer the data.
Out of curiosity can you post the full output of DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS when run in the database context.
A quick google on the error code brings nothing back but people have had issues with it auto completing the file paths, which look right at a quick glance, but are not correct after a closer look.
January 10, 2013 at 4:06 pm
The vendor has its own replication to set in place but as of the moment it is not yet ready to implement. As an interim solution to create a DB for reporting on the other server, we are exploring some approach and one of it is the transfer db task in SSIS, though I already have working solution, a backup-copy-restore SSIS package, this transfer db tasks is a simple way to do this. the source DB needs to be online all the time.
Result of the dbcc checkdb : Command(s) completed successfully.
I also saw those posts about incorrect dir path but I already copy paste them on the properties of the task. it should not be the case. Thanks Anthony for your replies. much appreciated.
:-):cool:
January 11, 2013 at 1:16 am
What edition of SQL are you running Enterprise, Standard?
How often do you need to refresh the destination database? Does it need to be close to real time population? Can you live with users being disconnected for X minutes every so often?
Do the destination and source database live on the same server?
You could look at a number of different options for the task in hand depending on the above, Change Data Capture, LogShipping, Custom Replication, SQL Replication, Staging areas and a full end to end ETL solution.
January 13, 2013 at 5:19 pm
anthony.green (1/11/2013)
What edition of SQL are you running Enterprise, Standard?
Enterprise 64-bit
How often do you need to refresh the destination database? Does it need to be close to real time population? Can you live with users being disconnected for X minutes every so often?
the refresh is once daily. Taking the DB offline or kick off users is not an option.
Do the destination and source database live on the same server?
different server
You could look at a number of different options for the task in hand depending on the above, Change Data Capture, LogShipping, Custom Replication, SQL Replication, Staging areas and a full end to end ETL solution.
the vendor has its own replications solutions for their DB. they just not yet ready to implement it. this replication will be the permanent solution but for the mean time we are looking for some way to copy the DB to another server for reporting purposes.
:-):cool:
January 14, 2013 at 1:17 am
For the time being you could setup replication yourself (for real time reporting) or enable CDC (change data capture) and then you can transfer out the changed data nightly to the reporting server via SSIS.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply