Database Transfer Task fails for mirrored database
Hi All, I'm using SQL 2008 and SQL 2008 R2. I've created a simple SSIS package to copy a database to the same or a different server and append a suffix to its name. It uses the Database Transfer Task. It has been working great up until I used it on a database that is mirrored. I'm getting the following error and have failed to figure out how to handle it. Started: 12:14:42 PM Error: 2011-03-25 12:14:48.70 Code: 0xC0024104 Source: Transfer Database Task Description: The Execute method on the task returned error code 0x80131500 (ERROR : errorCode=-1073548784 description=Executing the query "ALTER DATABASE [Mirror_Test_Copy] SET PARTNER TIME..." failed with the following error: "Database "Mirror_Test_Copy" is not configured for database mirroring.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}). The Execute method must succeed, and indicate the result using an "out" parameter. End Error I've tried increasing the MaximumErrorCount for both the parent and the task to no avail. The new database that is a copy is not mirrored and fails when the partner timeout setting is set. Any ideas on how to get this to work on mirrored databases or on how to ignore errors would be appreciated. If I can ignore the errors and get the database transfered I can always add another task to check if the items I want to come over worked or if it was an error I really cared about. Joe MoyleJoe Moyle
March 25th, 2011 9:16pm

You must provide the mirror failover partner in connection string. Please visit here to configure SSIS package http://www.sqlservercentral.com/blogs/sqldownsouth/archive/2010/01/07/handling-database-mirror-failover-within-an-ssis-package.aspx
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2011 10:20pm

Hi RamJaddu, Thank you for answering but that doesn't help. The principal database is my source and it is available. There has been no failover. Let me clarify the issue I'm having some more. My package copies a database to any other server that is reachable. It may be copying to itself or some other server. In the test above I have a source database called Mirror_Test. My package appends something to that name when it creates the target database such as Copy or Report. In this case my target database is called Mirror_Test_Copy and it happens to be on the same server. There is no mirror involved in the target database and there has been no failover. The source database is the current principal of the mirrored set. In my package the Transfer Database Task has the DestinationOverwrite value set to true. So the tasks drops and rebuilds from scratch the target database if it already exists. Regardless of whether or not it already exists when it is created all SET options from the database it is being copied from are getting executed. One of those options set is "ALTER DATABASE [Mirror_Test_Copy] SET PARTNER TIMEOUT 10" Since the newly created database is not mirrored that command fails. It has nothing to do with the failover partner of the source database I am copying from since the target I am copying to isn't mirrored. I always set the failover partner in the connection string for applications that need to be able to connect to the current principal but in this case I can and that isn't the issue. I think the real issue is how to handle any failures in the Transfer Database Task that are always going to fail for some reason such as this one. But it could be some other option failing that has nothing to do with mirroring that fails. Is the Transfer Database Task intolerent of any errors?Joe Moyle
March 25th, 2011 10:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics