Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS Transfer SQL Server Object Control Flow Error Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 7:13 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:47 PM
Points: 52, Visits: 449
Due to database corruption (referenced in a previous post), I am attempting to transfer all objects and data out of one database into another using SSIS. After encountering numerous errors, I finally was able to get the data and objects to successfully move to the destination database!

However, I attempted to do the same thing to another database and started encountering this error:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=0 description=************************************************************* helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".

Since then, I returned to the original (successful db transfer) and re-ran the package and it, too, began getting this error!

My boss suggested that the astrisks could be coming from within a view or stored proc, etc. (/********************comment**************/) so I though that maybe a new object may have recently been created in the db I was restoring... So I restored the db from last week (known good) and still get the error. I've also looked online to see if I could determine the date objects were created so that I could look for something that was recently created, but was unsuccessful in finding anything. And then I ran a "SQL Search" to find objects with tons of asktrisks in them and, of course, there are about 40 or so that have them. Since my known-good, week-old db is now getting the error, I'm wondering if I'm going down the wrong path anyways.

Any suggestions on what this error could mean or how I could go about decypherdeciphering it? I'm completely frustrated!
Post #1345256
Posted Thursday, August 16, 2012 2:07 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:27 AM
Points: 114, Visits: 1,220
Please check this article: http://www.kodyaz.com/articles/transfer-sql-server-objects-task.aspx
The article has full information about the error!!


SQL Database Recovery Expert
Post #1345728
Posted Thursday, August 16, 2012 6:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:47 PM
Points: 52, Visits: 449
I'm sorry, but I don't see how the article relates to the *ambiguous* error that I am getting. I've encountered the schema error before, but I don't think this is related. I certainly appreciate your suggestion, though!

So far, I have re-built a new package, turned off (and on) all of the copy properties in turn, and I am now turning off the 110 (not 40, as I mentioned before) objects that contain at least as many ***********s as the error...so far no luck.

It's just frustrating that I had it working and all the sudden it's not. And if I had more of "something to go on" with the error, it would help. Even week-old data that had worked before doesn't work now (so I'm not sure if it's even an object causing the error).

Post #1345891
Posted Thursday, August 16, 2012 8:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 5, 2014 12:47 PM
Points: 52, Visits: 449
I'm doing a happy dance, but I'm also kicking myself at the same time! (You're welcome for the visual.)

There were two triggers running on the server, one that prevents table creation (unless you're a db admin) and one that prints a message when the dbo schema is not used. The stars were coming from the print statement of the schema-trigger. I should have known to check the triggers, but *assumed* that they were not enabled on our test server. If I had not tried to run the process authenticating as another user, I would not have gotten the following error:



[Execute SQL Task] Error: Executing the query "
/****** Object: Database [database] Script..." failed with the following error: "The transaction ended in the trigger. The batch has been aborted.
*************************************************************
If you need to Add/Delete/Modify a database on this server,
please contact the SQL DB Admin group.
---- Command not completed. ----
*************************************************************
Mail queued.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



The triggers were in my list of 110 objects that had *********s in them. Which means I would have eventually found it by disabeling the transfer of each of those 110 offending objects but I should have known to look at the triggers first!

I hope this might help someone avoid frustration in the future.

Mods, if there's a way to mark this post as resolved, please do!


Post #1345988
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse