SSIS : DTEXEC CHECKPOINITNG ON/OFF

When a job with a SSIS package execution is scripted i've noticed that the DTEXEC commandline option is set to /CHECKPOINTING OFF. In my packages i've built checkpoints so this does alarms me a bit. I decided to find out more about this.

In BOL the following fragment of text can be found: "Optional). Sets a value that determines whether the package will use checkpoints during package execution. The value on specifies that a failed package is to be rerun. When the failed package is rerun, the run-time engine uses the checkpoint file to restart the package from the point of failure.

The default value is on if the option is declared without a value. Package execution will fail if the value is set to on and the checkpoint file cannot be found. If this option is not specified, the value set in the package is retained. For more information, see Restarting Failed Packages by Using Checkpoints.

The /CheckPointing on option of dtexec is equivalent to setting the SaveCheckpoints property of the package to True, and the CheckpointUsage property to Always."

This phrase only describes the ON option. The OFF option is not described anyway!

Scenario 1 
In this scenario lets try the option /CHECKPOINTING OFF when the checkpoints are enabled in the SSIS package. In the snippet below you can see that i've set the checkpoint properties:

I opened a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:

and the checkpoint file is created:

Conclusion: the option CHECKPOINTING OFF doesn't turn off checkpointing (?!), strange and not very intuitive.

Scenario 2
In the next scenario i tried the following setting:

I open a DOS window and i executed the following command: DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING OFF and below you can see the result:

and no checkpointfiles are created.

Conclusion: as i expected no checkpointfile is created.

Scenario 3
In this scenario let's try the CHECKPOINTING ON option. As in the former scenario the properties for the checkpoints stays the same:

 

and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:

And the checkpointfile is created:

Conclusion: the option CHECKPOINTING ON overrrides the properties 'CheckpointUsage' and 'SaveCheckpoints' in the SSIS packages.

Scenario 4
In this scenario i left property CheckpointFileName blank. Something like this :

and executing  DTEXEC /FILE TestWithCheckpointfiles.dtsx /CHECKPOINTING ON in the DOS windows shows this:

and offcourse no checkpoint file exists in the folder and that means that it is a goed a idea of specifying the checkpointfile in commandline options of DTEXEC: DTEXEC /FILE CheckpointDTEXEC.dtsx /CHECKPOINTING ON /Checkfile E:\tmp\NowviaCommandline.chkpnt and the properties stays the same. The output of the commandline executions shows this :

 and the checkpoinfile is created:

Conclusion
The option CHECKPOINTING OFF doesn't turn of the checkpointing in the SSIS package, but CHECKPOINTINGON does turn on checkpointing!

Greetz,
Hennie

SSIS : Setting a database connection with parameters in Denali

Parameters is a new feature in Denali and they are a replacement of package configurations. Package configurations will stay in Denali but now there is another approach available. They are a fundamental new way of how SSIS will handle, manage and execute packages  inside the catalog.  They look al lot like variables but they are slightly different. There are two types of variables: Project- and package parameters. For this blog w'll use the projectparameters.

1. First w'll create a demopackage in SSIS. In this package i've dragged an Execute SQL Task on the control flow and named it something like this:

 I created an connection and added the following SQL code at the Execute SQL task.

CREATE TABLE YES(
    [YES] [int]
) ON [PRIMARY]

And i added an project parameter to the project:

Assigned the project parameter to the connection in the expression builder:

2. I created four databases in SSMS (for this demo we won't be using them all, just Dev and Test):

3. Now let's deploy the package at the Catalog. First build it and then Deploy this project. Below you can see that the parameter is deployed together with the project.

 

 4. Okay now lets run SSMS. and go the catalog and now the project/solution and the package is deployed in the catalog:

 5. Add the variable (now its called a variable) parCONN to the environment properties of the Dev environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)
 

 
 6.  Add the variable (now its called a variable) parConn to the environment properties of the Test environment (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive).

 7. Add references to the project properties:

 8.  The next step is to set the parameter value with the environment variable that is created earlier (in the snippet below you can see parConn; i changed it later to parCONN. At this moment i'm not sure if it's case sensitive)

9. We are are now ready to test this package. So when i run this package against the Dev environment the table should be created in the Dev environment. Let's check this first:

And the results are shown below:

And the table is created in the Dev database:

10. The next step is to check out whether the package created the table 'YES' in the Test database. Lets found out.

Running the package will show the following details:

 

And here are the results. The table is created in the Test database!!

Conclusion:
So this is an example of setting a connection with parameters and enviroments. We have created an projectparameter in the package, created environments, pointed the projects to the enviroments and finally i ran the packages against two environments.

Greetz,
Hennie

SSIS : Catalogs and Denali

Today, i want to talk about the catalog.The catalog is used in the new deployment model of Denali. There are four stages of the project deployment life cycle:

1. Build: Prepares your project for deployment.
2. Deploy: Adds your project to the Integration Services catalog on an instance of SQL Server.
3. Import: Loads a project into BIDS from the Integration Services catalog or a project deployment file.
4. Migrate: Converts legacy packages and configurations so that they can be used in the project deployment model.

As Jamie Thomson states in his blog, the SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance:

Before using the catalog you need to enable the CLR:

EXEC sp_configure 'show advanced options' , '1';
reconfigure;
EXEC sp_configure 'clr enabled' , '1' ;
reconfigure;
EXEC sp_configure 'show advanced options' , '0';
reconfigure;

and now it's possible to create the Catalog in the SSMS:

You can create only one catalog per SQL Server instance. The database is always called SSISDB. It would be great if this could be changed in the future release of Denali. This would create flexibility in corporate naming convention.

It's possible to create multiple environments and according to the Technet wiki  an environment is a container of variables that can be referenced by Integration Services projects. Each project can have multiple environment references, but a single instance of package execution can only reference variables from a single environment. Environments allow you to organize the values that you assign to a package. For example, you might have environments named "Dev", "Test", and "Production". Something like this:

At the center of the project deployment model is the project deployment file (.ispac extension). The project deployment file is a self-contained unit of deployment that includes only the essential information about the packages and parameters in the project. Okay lets build this thing! An..ispac file is created in the bin folder.

So how do deploy your solution and packages into the catalog. Well there is a deployment option in BIDS. Right click on the project and choose 'deploy'. You have to walktrhough the wizard and following screens will appear:

Choose the deployment file, that is created during the build, in the bin folder. When the project is deployed for the first time the first option should be choosed. The second time you can take the catalog deployment.

Choose integration services project where the deployment should take place. The message below indicates that project already exist ( i deployed it earlier)

 

In this window you can choose the parameters for the project. I'll look into this in the future.

Press next and following window showed that everything was deployed succesfully.

 The review window:

And now you can see that the project is deployed in the SSMS tree in the object explorer.

Conclusions:

• I have enabled the CLR because the catalog uses CLR functions.
• I have created the catalog.
• I understand what environments are.
• I have created a couple of environments in SSMS.
• I have deployed a project from BIDS to SSMS catalog.

Greetz,
Hennie

SSIS : Importing a SQL Server 2008 package in Denali.

Hi, At this moment we are working with a framework in SQL Server 2008 and i was wondering whether we could import this framework in Denali. So i decided to import a package built in 2008 into Denali. Below you can see the errors:

.1 

There were three errors that were discovered by Denali:

• Native client conversion error. Changed this to the right native client.
• Missing environmentvariable. Ok that's clear.
• Script component conversion error. For some reason the input column was lost. I needed to add this again. 

I noticed that protectionlevel is also changed to EncryptSensitiveWithUserKey from Don'tSaveSensitive.I had to change the query in the source adapter and therefore an error appears on the screen. I needed to map some fields again. I deleted them in the source query and therefore i deleted them from the dataflow.

And the followin window appears:

I corrected all the errors and then i tried to execute the package. The following window appears:

Okay! Well reading jamie thomson blog about Projects tells me that i have to build the project first. Okay. Lets build this thing! Woops. Yet another error occurs:

As it seems there now multiple projectproperties like Protection level in Denali. When you set these to certain values a newly created package will take this projectproperty as by default. I compared the project properties with SQL Server 2008 (i installed on environment BIDS helper) :

  

Left is Denali and right is 2008. New are the common properties.

I changed the protection level in order to get the protection level of the project the same as the package. Let's build. Now it succeeds! Two folders are created in the solution folder: obj and bin and in the bin folder you can find now an ispac file. This is a ZIP file and when i unzip this the following files are visible:

Three files are visible: 

• @project.manifest.
• [Content_Types].xml.
• package.

Opening the  @project.manifest file will show:

and opening the [Content_Types].xml will show this

It seems that the execution of the package is allowed now and below you can see the result. My first package executed in Denali:

Conclusion:

• You need to build the package before you can execute this. 
• A deployment file is created (.ispac) with at least 3 files in it zipped.

Gr,
Hennie