SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Transfer SQL Server Object Control Flow Error


SSIS Transfer SQL Server Object Control Flow Error

Author
Message
lisa.randles
lisa.randles
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 500
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! :-D

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! w00t
prettsons
prettsons
SSChasing Mays
SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)SSChasing Mays (616 reputation)

Group: General Forum Members
Points: 616 Visits: 1388
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 :-)
lisa.randles
lisa.randles
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 500
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).
lisa.randles
lisa.randles
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 500
I'm doing a happy dance, but I'm also kicking myself at the same time! (You're welcome for the visual.) :-P

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. :-D

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search