SQLServerCentral / Discuss Content Posted by Stephen LaPlante / Article Discussions / Article Discussions by Author / SSIS – Transfer SQL Server Objects Debugged / Latest Posts

RE: SSIS – Transfer SQL Server Objects Debugged

kanuri.sandeep — Mon, 11 Oct 2010 12:31:52 GMT

Excellent article...the communication is very clear. I have a similar problem with my database and have implemented a solution without using vb.net in which such procedures with changed names are found and filtered rather than being changed and compiled again in the destination database. I wonder if we can create a DDL trigger that can prevent users from changing names of Stored Procs from Object Explorer.

RE: SSIS – Transfer SQL Server Objects Debugged

Preet_S — Wed, 21 Jan 2009 04:51:06 GMT

It is a real bug-bear how MS rely on users to do their debugging for them and as somebody pointed out, for free! Actually, its at a cost to us having to pay for support and be able to log a call, for which they then accept as a bug!

RE: SSIS – Transfer SQL Server Objects Debugged

Bob Clayton — Mon, 05 Jan 2009 11:07:26 GMT

Microsoft took an intuitive, easy-to-use tool (DTS), and turned into a needlessly complicated, buggy disaster called SSIS. I believe Microsoft has completely lost touch with who their customers really are. Most SQL Server users are people like me, who work on multiple projects with tight budgets and timelines. I need a tool that solves the most common problems quickly, reliably, and intuitively. SSIS is not intuitive, is not easy to use, and is buggy and unreliable.Granted, there are a handful of SQL Server uber-studs (and studettes) who need obscure programming features and capabilities that I could probably not even dream up. However, that is a small percentage of the SQL Server user community. The rest of us need to accomplish the most common tasks quickly.I sincerely hope that Microsoft would get re-focused on the real users of SQL Server, and make SSIS usable for for the broad community of users.

RE: SSIS – Transfer SQL Server Objects Debugged

scogan — Wed, 29 Oct 2008 15:59:26 GMT

Steve, Well done!!! I am not a SQL developer at all but have been forced into this because I have 3GL background LOL and appearently overly ambitious. So I have a new 2005 database going live in two months and have to create a monthly migration package. I am learning SSIS on my own because my Company cannot afford training. Enough of my sad story. I have some questions on your diagram because I need to do the same thing and I got the same errors and I want to make it dynamic like yours. I have questions on the Looping and passing the list of objects from the storage to the DROP FOR LOOP. I hate beg but if you have a moment you might be able to get me on the right track.My name is Steve CoganMy email is scogan@zoomtown.com if you are intested in answering some questions send me an email and I will email back. Sincerely,Steve Cogan

RE: SSIS – Transfer SQL Server Objects Debugged

Tom Brown — Fri, 04 Jul 2008 04:11:32 GMT

:) At last! the answer to Transfer Server ObjectsSet CopySchema to True!Many thanks I've been looking for this for a while.

RE: SSIS – Transfer SQL Server Objects Debugged

omar alsawwa — Wed, 04 Jun 2008 01:18:51 GMT

:) Many Thanks.

RE: SSIS – Transfer SQL Server Objects Debugged

Ian Yates — Tue, 08 Apr 2008 23:01:17 GMT

Good article. I've always as a matter of habit scripted drop & create commands because of the potential problems they may cause but, over time, had forgotten exactly why I started doing it :) Good article! I'm not really well versed in SSIS but have occasionally used the copy tasks with mixed results - at least I can keep this in mind when using it.Also, you cannot really disable sp_rename because it's used by SQL Server diagram schema saving code generation (I'm sure it has a better name than that!) extensively. If you change the schema of a table it creates a table with a similar name and the new schema, transfers the data, drops the old table, renames the new table and recreates referential integrity, etc. If sp_rename was crippled then you'd run into interesting issues :)

RE: SSIS – Transfer SQL Server Objects Debugged

Mike Byrd — Tue, 08 Apr 2008 09:22:06 GMT

Thanks for the article. I too have wondered about the 'features' that Microsoft has in their SSIS objects. I just completed my first large SSIS project and ran into similar (although different) problems. I had thought about doing an article similar to yours, but you beat me to it. I agree with your views on how Microsoft could release code for us developers to debug. Makes you really wonder about their QA staff and if they have anybody on the QA side that knows how businesses really operate.Cheers,Mike Byrd

RE: SSIS – Transfer SQL Server Objects Debugged

Ahmad Drshen-386043 — Wed, 28 Nov 2007 01:10:22 GMT

hey that's really great both writer and package :).I used it and customized according to my needs but one thing where i am going to tuck is how i can transfer Table with all of its elements like defaults,constrains,clustered and nonclustered indexes , primray and without data , how i can generate script for tables this pkg only does for stored procedures and functions i try my best to do it through MS pakage but unable to it its internal bugg . Reply Soon , Thanks in advance;)

RE: SSIS – Transfer SQL Server Objects Debugged

seiiv — Tue, 15 May 2007 09:31:00 GMT

From SQL server books online:

http://msdn2.microsoft.com/en-us/library/aa214392(sql.80).aspx

Topic: Modifying and Renaming a Stored Procedure

Note Renaming a stored procedure does not change the name of the stored procedure in the text of the procedure's definition. To change the name of the stored procedure in the definition, modify the stored procedure directly.

RE: SSIS – Transfer SQL Server Objects Debugged

Paul Lach-293939 — Mon, 30 Apr 2007 12:41:00 GMT

I ran into the same issue using Microsoft's SMO objects in C#. I opened a case with Microsoft and they confirmed that there was a bug. As you discovered, the problem is that when you rename an object whose definition is contained in sys.sql_modules the object gets renamed in sys.objects, but the source code is not modified in sys.sql_modules.

Interestingly, the people responsible for SQL Server Management Studio have "fixed" the problem in their code. Whenever you script a stored procedure through the right-click menus the new (correct) name appears in the script even though the old name appears in the sys.sql_modules record for that stored procedure. Apparently, they rebuild the definition line up to the AS keyword and then concatenate the rest of the script from sys.sql_modules. What a kludge!!!

Why not fix the underlying problem? When you rename an object whose definition appears in sys.sql_modules, replace the old name with the new name in the object definition script in sys.sql_modules!

RE: SSIS – Transfer SQL Server Objects Debugged

Jim Wylie — Fri, 27 Apr 2007 00:28:00 GMT

Super sleuthing. Thanks.

RE: SSIS – Transfer SQL Server Objects Debugged

WHug — Thu, 26 Apr 2007 11:29:00 GMT

caveat: Good luck changing a system table in 2005

RE: SSIS – Transfer SQL Server Objects Debugged

Josh Grant — Thu, 26 Apr 2007 09:38:00 GMT

A closely related issue exists with the sp_rename stored proc in SQL 2000. If you use sp_rename to rename a view (which Enterprise Manager and Management Studio seem to do under the covers), the view still retains its old name in syscomments. Scripting that view to a file (using Management Studio or Enterprise Manager) and then running the resulting script file will create the view with its OLD name.

RE: SSIS – Transfer SQL Server Objects Debugged

Leslie Gordon-343299 — Thu, 26 Apr 2007 09:04:00 GMT

I've found that this is not the only task that has execution errors. I recently received SSIS training as well, and from a DBA perspective, everything worked great in class. When I got back to the office, the problems began. Specifically, the copy database task and the transfer login tasks did not do what they were supposed to do. I'll post more information when I get the time to gather the results.

Cheers!

RE: SSIS – Transfer SQL Server Objects Debugged

Aldo Bittel — Thu, 26 Apr 2007 07:30:00 GMT

Yes, as a premier support customer we opened 2 Calls. They know the error but they won't change it in the next time.

RE: SSIS – Transfer SQL Server Objects Debugged

Sasha Risner — Thu, 26 Apr 2007 07:23:00 GMT

GREAT debugging & documentation!

I've seen this happen before in my databases and did narrow it down to renaming. I just try not to rename things in Management Studio anymore. From experience, I found it's more reliable to DROP & CREATE again.

Did you submit this bug to Microsoft?

RE: SSIS – Transfer SQL Server Objects Debugged

Aldo Bittel — Thu, 26 Apr 2007 03:43:00 GMT

There are a lot of other erros in this task. Microsoft told us, they will be corrected maybe in SQL2k8. thank you Microsoft!! Very helpful

RE: SSIS – Transfer SQL Server Objects Debugged

John McC — Thu, 26 Apr 2007 01:42:00 GMT

Hate to think how much coffee and hair pulling it took to weed this out

RE: SSIS – Transfer SQL Server Objects Debugged

David McKinney — Thu, 26 Apr 2007 00:52:00 GMT

Refreshingly well written article....but this 'issue' has been present for a very long time (long before SSIS) in Enterprise Manager. You can fall victim to its consequences, no matter how you script your database.

RE: SSIS – Transfer SQL Server Objects Debugged

kgayda — Thu, 05 Apr 2007 10:28:00 GMT

Thanks Steve, this was helpful that you debugged this down to the internal sql code.

SSIS – Transfer SQL Server Objects Debugged

drgbg1 — Thu, 05 Apr 2007 09:00:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/sLaPlante/2955.asp