As a software developer, I recently had the opportunity to write my first SSIS package. While I was impressed with the capabilities of SSIS, I encountered some significant problems that I believe Microsoft should have addressed. In an effort to help others facing similar issues, I want to share my experience and provide some solutions.

The purpose of my package was to copy Stored Procedures and User Functions from one database to another. Initially, I used the “Transfer SQL Server Objects” task provided by Microsoft, but it didn’t copy any objects to the destination database. After some research, I discovered that I needed to set the “CopySchema” property to True. It was frustrating that this property had to be explicitly set for the task to work.

However, even after setting the “CopySchema” property, I encountered another problem. The task gave me an error stating that a procedure already existed at the destination database. After examining the Task Editor, I realized that I needed to set the “DropObjectsFirst” property to True to resolve this issue. But then, I faced a new error stating that a procedure did not exist at the destination database.

At this point, I turned to Google for help and found a suggestion to write a script task to handle dropping the procedures from the destination database before running the Transfer SQL Server Objects task. I implemented this solution and it worked, but it was not an ideal workaround.

Further investigation revealed that the Transfer SQL Server Objects task was attempting to create two versions of the same procedure at the destination database. This puzzled me because you cannot have two procedures with the same name on the same database instance. After extensive debugging, I discovered that the name of the procedure in the definition text did not match the name stored in the sys.objects view. This inconsistency caused the task to create duplicate procedures.

To solve this problem, I added code to the script task to compare the procedure name in the definition text with the name stored in the sys.objects view. If there was a mismatch, I changed the definition text procedure name to match the name from the sys.objects view. This solution successfully transferred all the stored procedures and user functions.

It is important to note that Microsoft should address this issue where renaming a stored procedure in the Object window of SQL Server Management Studio does not update all tables in the underlying database. This oversight can lead to potential problems, such as overwriting the current version of a procedure with an older version.

I hope this article helps you navigate the common issues encountered when working with SSIS packages in SQL Server. You can download the sample packages and code provided to help you overcome these limitations and customize them to meet your specific needs. Let’s continue pushing the limits of SSIS technology to meet the demands of our profession.