Stored Procedure Deployment and Backup Using SSIS

I am trying to create an SSIS package for Stored Procedure Deployment and Backup for our project.

I have some .sql file, each file contains one stored procedure definition and the name of the file is the stored procedure name itself. I am trying to do the following by using SSIS

1. Read all files names one by one
2. Find the definition of each stored procedure if it exists in the database
3. If exists, then save the definition with the same name in a different folder (In my case it's a ROLLBACK folder)
4. For all new SP it save in a same file named DropNewSp.sql with DROP STORED PROCEDURE command.
5. After completing the backup process, execute all files in the destination database.

I am able to generate the desire .sql files, but I am faceing the following problem 1. The package also generated unwanted blank .sql file for all new Stored Procedure 2. The execution process failed if the stored procedure has some dependency on subsequent stored proc

In this answer, I will provide the main steps with some references to get more information on how to achieve each step. Even if I agree with the comments mentioned above that this is not the job of SSIS.

1. Add a foreach loop container that loop over .sql files and store the file name inside a variable:

   • Looping over files with the Foreach Loop
   • Lesson 2-2: Add and configure the Foreach Loop container
   • Load multiple source files

2. Add an Expression Task to retrieve the file name from the File Full Path (variable)

   @{User::FileNameWithoutExtension] = SUBSTRING (@[User::FullFilePath], LEN( @[User::FullFilePath] ) - FINDSTRING( REVERSE( @[User::FullFilePath] ), "\\", 1) + 2,  LEN (RIGHT( @[User::FullFilePath], FINDSTRING( REVERSE( @[User::FullFilePath] ), "\\", 1 ) - 1 ) ) - FINDSTRING( REVERSE( @[User::FullFilePath] ), ".", 1 )  )
   

3. Add an Execute SQL Task inside the foreach loop container to check if the stored procedure is found in the database:

   SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = ?)
   

   • Pass the procedure name as parameter to the execute sql task:

     • Passing Variables to and from an SSIS task

   • Store the count result inside a variable of type integer using Resultsets:

     • SSIS Basics: Using the Execute SQL Task to Generate Result Sets

4. Using precedence constraints with expressions add 2 paths from the execute sql task

   • the first using expression @[User::Count] == 0
   • the second using expression @[User::Count] > 0

Other references:

• Working with Precedence Constraints in SQL Server Integration Services

• Defining Workflow in SSIS using Precedence Constraints

  5. On the second path add an Execute SQL Task to get the procedure definition using the same approach above:

     SELECT OBJECT_DEFINITION (OBJECT_ID(N'<databasename>.<schemaname>.' + CAST(? as VARCHAR(100))));  
     

And store the result inside a variable using a result set.

6. Add a Script Task to write the procedure definition into the destination file

7. On the first path add a File system task to move the file into the directory specified

8. Add another foreach loop to read new files and execute the content.