Hello, I'm using the Transfer SQL Server Objects Task in SSIS to migrate a set of table and views between servers. This operation fails when creating a nested view. One of the two views it is based upon has not been created on the target causing the script to fail with the following error: "Invalid object name <vw_A>." Does deferred name resolution not work for views? Like stored procedures and other objects, names may not be resolved until runtime. Is there a workaround?Orlanzo

Do you have the vw_A in the target db? or did you select it in sql server objects to transfer? maybe this object name comes from a view script.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

No, it doesn't exist on the target. I included all objects in the view collection.Orlanzo

No, it doesn't exist on the target. I included all objects in the view collection. Orlanzo you should include any objects ( tables or views ) which your transfer view ( the view(s) you want to transfer ) contains them. with Transfer Objects task you transfer the object exactly, not its data, so you need to provide the structure completely, so if your view contains 10 tables, you should transfer those tables too. But if you want to transfer data from a view to another database use data flow task for this and set view as source in oledb source, and set destination table and do mappings. you can also do this with import/export wizard simplyhttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

As I mentioned, I included ALL views and base tables in the transfer. The issue appears to reside within the Transfer SQL Server Objects Task. It may not be resolving object dependency chains when it creates the scripts for transfer. When transferring a nested view, it should ensure the base views are created first. I don't believe it is doing so and as a result, vw_A is being created before the base objects. I had thought this would not be an issue with deferred name resolution. It doesn't appear to apply to views - just stored procedures. For example, I can create stored procedures without the underlying objects existing in the target database.Orlanzo

There is a property in transfer sql server objects task's editor, in objects tab, named IncludeDependentObjects which is false by default. did you try to set it to true? please let me know the resulthttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Reza, Yes, I had tried setting that option. The behavior was the same. Orlanzo

I checked both of following scenarios and both was successful: 1- created a view of two other tables, and just select the view in transfer sql server objects with IncludeDependentObjects to true, and everything transfered fine. 2- create a view of two other tables, and select all views and all tables in transfer sql server objects, and everything transfered fine. So, as a result I think about maybe you have an external table or such thing in source views, did you used linked server? did you refered another table from another database in your views ? could you checked design of views exactly? there should be something which can not transfer maybe an external thinghttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Are you testing in SSIS 2005? You need to create and transfer a nested view: CREATE VIEW ... SELECT ... FROM vw_1 INNER JOIN vw_2 ... INNER JOIN vw_n ... INNER JOIN tbl_1 ---Orlanzo

I tested with 2008, I haven't 2005 box to check it therehttp://www.rad.pasfu.com My Submitted sessions at sqlbits.com