Step types: Object

Hello,

In this blog post series, I am writing about the package step types in TransSQL. This is the second step type I write about and in my previous blog post I wrote about the Query step type, package and step concept. You can find that post using this link: http://www.transsql.com/2014/08/step-types-query.html

Now it's time to talk about the Object step type! Below, you can find a screen shot of a package which contains 4 steps. 3 of them are Object step types and one of them, the 4th one is a Query step type. You can combine Object and Query step types in a package, they are fully compatible with each other.

Object package step type. There are 4 steps in the package as can be seen in the "Package content" window. "Step details" window shows the content of the selected step.

So what are Objects? What do we mean by saying "Object"? We name Tables, Stored Procedures, Views, Functions, Triggers and Schemas as objects in TransSQL. For instance, if you want to deploy a new Function you wrote on your development database server to a production server, or if you want to deploy the new version of a Stored Procedure, then you can easily script that Stored Procedure by adding it as an Object step type to a package as shown in the screen shot above.

The script of the Object will be a CREATE script by default, during deployment the target database will be checked for the existence of that object and if found then the CREATE script will be modified as an ALTER script on the fly and the ALTER script will be applied to the target database server. The target Object will not be DROPPED and CREATED again, it will be ALTERED. I will tell you much more details about the deployment procedure when I will be writing about that particular topic. Now let's do not lose our focus and wrap up.

If you want to DROP an existing object at the target database server, then you need to type a DROP PROCEDURE ...  e.g. command using the Query step type. You can use the Object step type only to deploy a new Object or alter an existing one.

If your deployment fails or succeeds then a date and time value appears at right side of the object name under the "Latest deployment" header. You can see this in the screen shot. If your deployment is rejected by the administrator or stopped, for instance if you are trying to create a table which already does exist at the target database, then this value is not updated.

You can also select an object and click on the Object History button to see the details of the previous deployments of this object.

In the next blog post, I will be writing about the Export step type.

Thanks for your time!