8

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." Error message

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:enter image description here

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

Community
  • 1
  • 1
Watki02
  • 4,696
  • 7
  • 34
  • 36
  • After you transfer the logins, can you remove those and give it a shot? Will that help in any way? – rvphx Jun 05 '12 at 21:29
  • remove what? the logins I just transferred? – Watki02 Jun 05 '12 at 21:37
  • 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](http://dtloggedexec.codeplex.com/) Never played with that task so I can't speak to the particulars of the error you're encountering – billinkc Jun 05 '12 at 21:52
  • @Watki02 Perhaps flagging this to be moved to [DBA.SE](http://dba.stackexchange.com/) would get you some answers, too. – swasheck Jun 05 '12 at 22:13
  • 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." – Russell Fox Jun 05 '12 at 22:25
  • @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? – Watki02 Jun 06 '12 at 13:41
  • 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.) – Watki02 Jun 06 '12 at 15:23
  • "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. – Russell Fox Jun 06 '12 at 18:17

5 Answers5

6

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.

Watki02
  • 4,696
  • 7
  • 34
  • 36
  • 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? – Diego Jun 08 '12 at 14:24
  • 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). – Watki02 Jun 12 '12 at 18:40
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.

gjspaho
  • 314
  • 3
  • 12
1

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.

Stephan
  • 41,764
  • 65
  • 238
  • 329
Jim Denny
  • 11
  • 1
1

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

Matt
  • 11
  • 4
0

"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.

Diego
  • 34,802
  • 21
  • 91
  • 134
  • 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? – Watki02 Jun 06 '12 at 13:32
  • yes it was a typo, I meant to write VS2010, SSIS 2012. Regarding the version, you should be fine with Express – Diego Jun 06 '12 at 14:07
  • I Just tried SQL 2008 Express and it failed. See my deeper explaination here: http://social.technet.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cda53c80-8da6-4ed1-898a-9f3ff8464ae2 – Watki02 Jun 06 '12 at 19:13