SSIS Transfer Database task cryptic error message 0x80131500

Question

I am using SSIS with VS2010 (shell) and databases going from SQL Server 2005 (32 bit)to SQL Server 2012 (64 bit). I am developing directly on the destination server (not optimal, but it works).

When I try to use the Transfer database task, it gives me an error message as follows: "Error: 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."

Here is the problem... how do I view an "inner exception"?? it is a GUI interface with no way to step through the code! I even tried setting up logging - it just logs the same useless error message.

Microsoft has no information for this error code in their reference docs (that I could find).

After googleing the error code, I saw others have this error code along with messages having to do with users, roles, and creating them.

• I double checked that I have sysadmin rights on both servers, and
  logins on both.
• I tried the same Transfer Database task from each server to itself (with changeing database name) and that worked fine for both by themselves.
• I tried both DatabaseOnline and DatabaseOffline options. (same error both ways)
• I tried doing a "Transfer Logins" task before doing the transfer database task, that task worked, but not the Transfer databases task. Then it started throwing errors saying that the databases don't exist - which implies that I need to transfer logins AFTER I transfer databases.

Here are my settings:

What am I doing wrong? OR how can I get the "inner exception" message?

Also, follow my post to Microsoft's forums here: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cda53c80-8da6-4ed1-898a-9f3ff8464ae2

Vaticide

After you transfer the logins, can you remove those and give it a shot? Will that help in any way? – Antitragus

remove what? the logins I just transferred? – Vaticide

What all events are you logging? Just OnError and OnTaskFailed? Grab OnInformation and OnWarning as they might give an indication of what else going wrong. Another option might DTLoggedExec Never played with that task so I can't speak to the particulars of the error you're encountering – Histoid

@Vaticide Perhaps flagging this to be moved to DBA.SE would get you some answers, too. – Koreykorff

Check the size of your database: "When you copy a database, the database cannot be smaller than the size of the model database on the destination server. You can either increase the size of the database to copy, or reduce the size of model." – Signac

@RussellFox, What do you mean by "model database"? This is a Transfer Database Task, which inherintly means it is creating the destination as it copies the original - how could they be different sizes? If they could, how could I control that? – Vaticide

I just added that this is also going from 32bit(SQL2005) to 64bit(SQL2012), is that an issue? (update: I tried it from a 64bit edition of SQL2005 and that failed with the same error message.) – Vaticide

"model" as in one of the system databases: master, model, msdb, and tempdb. I have no idea why it's an issue, I just noticed it in the documentation. – Signac

Answer 1

This answer makes me sick to my stomach... I hope I save someone else this hassle. The problem was this:

1. First and foremost: the error message was not descriptive enough. The error should be handed to the interface.
2. Under "edit" on a "Transfer Database" task, the destination file paths are "auto-populated" with the file paths of the source database. They look right at first (and second, and third...) cursory glance. Upon further inspection the file paths were wrong. This makes sense if you are going from version to version - the folders are named with subtle differences according to version (MSSQL.1 vs. MSSQL11.<instanceName>).

In summary, the error was caused by the folder not existing because the path was set wrong. I imagine other low-level exceptions like this are also eaten by the interface with the same cryptic error message.

Vaticide

can you believe I've come across the exact same scenario and I'm having the exact same error? But my file paths are correct...any advice? – Kootenay

As my last line of the answer indicates, it is probably a low-level exception like bad path name. Check DB permissions, file-system permissions, double check paths... anything that might normally throw up a simple, descriptive error message box and be helpful. Also try whatever your task is through SQL Server Management Studio (e.g. "transfer databse" == "copy database" in SSMS) that may reveal your error meesage (so tragically eaten by SSIS/SSDT). – Vaticide

Answer 2

This is old but I bumped in the same cryptic message with SSMS 17.2. I tried and checked all the suggestions above to no avail. In my case the issue was related to the TargetServerVersion property of the SSIS project in Visual studio 2017. By default this was set to SQL Server 2017, while my local server was SQL Server 2014 - once changed to the same version everything went smooth.

Hibernal

Worked for me, you can find the setting in the project properties (right-click) – Illconditioned

Answer 3

We ran into this where someone told us a valid date would always exist in the column in a MySQL database and we found out later that there were dates like '0000-00-00 00:00:00' and '0001-01-01 00:00:00'.

We handled it in the query that pulls in the data using a case statement to convert the bad date into a date SSIS can use :

CASE WHEN Product.PurchaseDate < '1900-01-01 00:00:00' THEN '1900-01-01 00:00:00' ELSE Product.PurchaseDate END AS PurchaseDate

Of course, you can set it to null also, your choice.

Immolation

Answer 4

I have also had this same issue and it turned out to be an access issues. Try giving these access to the folder where the mdf and ldf files will be landing: NT Service\MSSQLSERVER, Owner Creator, System

Louvar

Answer 5

"which implies that I need to transfer logins AFTER I transfer databases."

not really, logins are on a server (instance) level so you can transfer logins and then the database. You would need to worry about users later, of course

a point here, I dont think SSIS would be prepared to transfer 2005 -> 2012. I mean, It wouldn't make sense to "skip" a version. You said you are using VS 2012, so it would be SSIS 2012. It think it can read only 2008 databases. The fact that you tested on the same server and it worked also makes this point stronger.

Kootenay

I am using (visual studio) 2010, not 2012. It also worked on BOTH servers (SQL 2005 AND SQL 2012) independently, but not from one to the other. I will update my post to make that more clear - thanks! That being said, this still may be the answer... does it have to be the full version of SQL 2008? or can I use Express version? – Vaticide

yes it was a typo, I meant to write VS2010, SSIS 2012. Regarding the version, you should be fine with Express – Kootenay

I Just tried SQL 2008 Express and it failed. See my deeper explaination here: social.technet.microsoft.com/Forums/en-US/… – Vaticide

Answer 6

I had a somewhat similar problem and i have a suggestion about tracking down these errors. I had a copy database task that worked locally but not remotely.

The problem in my case seemed to be that i tried to rename the database while copying and that did not work! The database was named x on the sql-server "x server" and should be renamed to y on the sql-server "y-server" during the copy process. The log on the y-server showed an sql statement that accessed database x and an error like "user has no access to database x or it doesnt exist". Of course thats an error because x doesnt exist on that server. After i copied without renaming the db it worked fine.

I tracked the problem down by using the Profiler and enabling the event "Exception" under "Errors and Warnings"

i guess the problem lies even deeper because i cannot believe that renaming while copying is not supported but at least one might be able to specify the "inner exception" of that useless error message in the ui.

Calefaction