Object versions Table in SSISDB

This table stores the metadata (created date, deployed by, etc.) as well as the actual binary data for each project. The number of versions retained in this table is limited on a per-project basis, and is configured at the catalog level.

select * from [internal].[object_versions]

  

It stores the project ID, deploy date and Time.

By default this table stores the 10 version. We can see in the version by Right click on the deploy package.

  

We will get the version details

  

By default, the most recently deployed version is set as the current version. However, we can promote a prior version to the current version.

Click on properties.

  

The maximum number of versions per project can be set to any whole number between 1 and 9999. We can change the maximum version as per requirement. When the setting periodically Remove Old Versions is set to true (also the default), a SQL Server Agent job will run to clean up old versions of non-current projects in the SSIS catalog.

 Let’s see an example

I have created a simple package in this package just I take script task and print a message. I deployed this package on the catalog.

  

I deployed this project first time. See the version of this project.

Now I am running this package and see the output.

Sorry to show direct Output. I have written below code in script task

Now I am going to do some changes in my package

public void Main() { // TODO: Add your code here  MessageBox.Show("Second version of this package"+ Environment.NewLine+" Welcome to SSIS"); Dts.TaskResult = (int)ScriptResults.Success; }

See the output

Now I am going to deploy this update package on the server.

Package deployed successfully. Now see the version

  

By default, the most recently deployed version is set as the current version. However, you can promote a prior version to the current version.

Now I am executing this package. See the output

  

Great!!! J 

Changes have been done.

Restoring a Project Version

Suppose we have been deploy the new version of our package and founded that some issue with the new version of the package and want to restore the previous version of the package.

Right click on the deployed package

  

Click on the Version

   

Select the version which we want to restore and click on the Restore to selected version button.

  

Click Yes.

Click Ok.

See old version has been restored.

Now I am executing the package. Let’s see the Output.

Get the same output as we can see above.

The value is changed on the table.

select * from [internal].[object_versions]

See the Output and changes

Object_versions table stores all of the versions, both current and prior, for projects stored in the catalog. When a project version is restored, the restored_by and last_restored_time fields are set to the user ID and current date/time, respectively.