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 Control Flow indicates success, but objects are not transferred Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 7:05 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 (see my other posts), I am attempting to transfer all objects and data out of one database into another using SSIS. I've encountered and fixed numerous errors, and now I have a new issue.

I have 2 different schemas, dbo and DMUSER. When the DMUSER stored procedures transfer, *some* of them successfully have the DMUSER schema and *some* come over incorrectly as dbo because of how the stored procs were originally scripted:

CREATE PROCEDURE CheckAS400DealerCode
VS.
CREATE PROCEDURE DMUSER.CheckAS400DealerCode

As a result, it was my intent to transfer over all objects *except* for the DMUSER stored procs (this part works correctly) - and then, in a second transfer, change my authentication to the SQL DMUSER user (who has a default schema of DMUSER and is a member of sysadmin) to pull them over. However, the second transfer completes *successfully* (turns green and all) but the stored procs are not transferred to the destination database. No errors or anything!

Has anyone else encountered this or have any suggestions?! If you could provide any sort of help, I'd love you forever and be your best friend!
Post #1352783
Posted Friday, August 31, 2012 7: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
FYI: DMUSER is also a db_owner for the source and destination dbs.

...also, I apolgize for my frequent use of emoticons, I just think they help me get my point across!
Post #1352799
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse