CODEWITHSUNDEEP
sql-serversql-server-2005ssisdtssql-server-data-tools
Watki02
Watki02

Reputation: 4876

SSIS Transfer Database task cryptic error message 0x80131500

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.

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

Upvotes: 8

Views: 22853

Answers (6)

André
André

Reputation: 111

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"

Enabled Exception Event and parts of the Profiler Window

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.

Upvotes: 0

gjspaho
gjspaho

Reputation: 314

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.

Upvotes: 2

Matt
Matt

Reputation: 11

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

Upvotes: 1

Jim Denny
Jim Denny

Reputation: 11

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.

Upvotes: 1

Watki02
Watki02

Reputation: 4876

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.

Upvotes: 6

Diego
Diego

Reputation: 36146

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

Upvotes: 0

Related Questions