SSIS – Transfer SQL Server Objects Debugged

Question

Post reply

SSIS – Transfer SQL Server Objects Debugged

drgbg1

SSC Enthusiast

Points: 142
More actions
April 5, 2007 at 9:00 am

#177144

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

kgayda SSCrazy Points: 2157 More actions · Answer 1

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

Karen Gayda
MCP, MCSD, MCDBA

gaydaware.com

David McKinney SSChampion Points: 10472 More actions · Answer 2

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.

John McC Hall of Fame Points: 3410 More actions · Answer 3

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

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Aldo Bittel SSC Journeyman Points: 81 More actions · Answer 4

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

Sasha Risner Valued Member Points: 74 More actions · Answer 5

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?

Aldo Bittel SSC Journeyman Points: 81 More actions · Answer 6

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

Leslie Gordon-343299 Valued Member Points: 69 More actions · Answer 7

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!

Josh Grant SSChasing Mays Points: 646 More actions · Answer 8

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.

WHug Ten Centuries Points: 1248 More actions · Answer 9

caveat: Good luck changing a system table in 2005

Jim Wylie Grasshopper Points: 13 More actions · Answer 10

Jim Wylie

Grasshopper

Points: 13

April 27, 2007 at 12:28 am

#702852

Super sleuthing. Thanks.

Paul Lach-293939 SSC Rookie Points: 29 More actions · Answer 11

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!

seiiv SSC Rookie Points: 25 More actions · Answer 12

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.

Ahmad Drshen-386043 Grasshopper Points: 14 More actions · Answer 13

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;)

Mike Byrd Default port Points: 1458 More actions · Answer 14

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