woensdag 24 november 2010

SQL11 (Denali) CTP1 First look at SSIS Package Configuration or is Parameters?

Hello,

Today I was looking at package configurations in SSIS SQL11 (Denali) CTP1, clear is that the current CTP is not completed yet as Jamie Thomson and Hennie de Nooijer did discuss on there blogs.

I created a basic package with a connection manager and a single SQL Task that creates a table. It's simple but I want to explain Package Configurations and not sophisticated packages :-).
SQL11_Denali_SSIS_Package_Configuration_Parameters_1


The first thing that intrigued me was that when I right-clicked on the control flow page the option to choose Package Configuration did not appear.
SQL11_Denali_SSIS_Package_Configuration_Parameters_2


Looking a little further you will discover it's still there, click the control flow background again to display the package properties. On the properties pane browse to the MISC section, Configuration and click the [...] button that is displayed behind (Collections). The trusted enable Package Configuration pane will appear!
SQL11_Denali_SSIS_Package_Configuration_Parameters_3


Again when you now right-click on the Control Flow background Package Configurations is even showed in the pop-up menu.
SQL11_Denali_SSIS_Package_Configuration_Parameters_4


So why is it not there in the first place as we know how to find it in SQL 2005/2008? Is it going to be depreciated in the SQL Server editions that will follow on SQL11 or is it just because the CTP1 edition and will be there in the RTM?

It could be depreciated because now SSIS introduces Package Parameters! The way they work is how I like to use variables for setting up database connections based on Package Variables and Expressions. Only now you can also manage them in SQL Server Management Studio for SQL 11 Denali. Let's have a look.

Back to the previous package, the first thing is to display the Parameters and Variables, when you look at the SSIS menu after selecting the control flow the first change is we see not only Variables but it has changed to Parameters and Variables.

SQL11_Denali_SSIS_Package_Configuration_Parameters_5

When enabled we see a new pane displaying Parameters and Variables
SQL11_Denali_SSIS_Package_Configuration_Parameters_6


What I now like to do is make my Connection TargetDB parameterized. The first thing we need to do is to add a Parameter, click the Add button, a new Parameter entry will be enabled, enter your desired naming of the Parameter.
SQL11_Denali_SSIS_Package_Configuration_Parameters_7


The second step is to configure the Connection Manager TargetDB to use the created Parameter. Select the Connection Manager TargetDB, Go to the Properties pane Expressions section and add a new Expression property for the Connectionstring. The first thing you see that there is no Parameters section displayed only Variables, something to mention on connect.microsoft  to create a separated Parameters section?
SQL11_Denali_SSIS_Package_Configuration_Parameters_8


Expanding the Variables section brings us further, new kind of Variable is listed, this should be the Parameter, again naming is ugly I hope it is the CTP1 edition. Lets select the Parameter and evaluate.
SQL11_Denali_SSIS_Package_Configuration_Parameters_9


Seems to be fine so click OK. We now have a Parameterized Connection Manager. When you change the Parameter in the Package and Save, Close and re-open the package the Connection Manager is changed. Just like I used to with Package Configurations, Variables used for the Connection Manager connectionstring expression.

Only now in Denali we have an SSIS Catalog on the SQL Server that helps with using the Parameters for quick change of the processing environment of the packages when running on the server.

First thing to do is create a SSIS Catalog on the SQL Server instance. Start SSMS, open a Connection to the SQL Server Database Engine instance you Like to create the Catalog database on. Right-Click the Integration Services Folder. No there is no seperated SSIS Engine to connect to available anymore in Denali :-) it's standard under the Database Engine.
Fill in the requested information, I can not change the Catalog name and you can only create one Catalog on each instance. Password is required, connect.microsoft enable OK only when password is entered.
SQL11_Denali_SSIS_Package_Configuration_Parameters_10


I did take a look or I could simpel create the database by T-SQL, it should be possible only the database is created by a restore file \MSSQL11.MSSQL01\MSSQL\DATA\..\Backup\SSISDBBackup.bak and maintenance task (SSIS Server Maintenance Job) is created during the process. So let's skip that for now :-)

Next is to create a folder to deploy our package project to. Right-Click the just created Catalog database and select Create the Folder.
SQL11_Denali_SSIS_Package_Configuration_Parameters_12SQL11_Denali_SSIS_Package_Configuration_Parameters_11

T-SQL statements to create the folder:

Declare @folder_id bigint
EXEC [SSISDB].[catalog].[create_folder] @folder_name=N'FirstLook', @folder_id=@folder_id OUTPUT
Select @folder_id
EXEC [SSISDB].[catalog].[set_folder_description] @folder_name=N'FirstLook', @folder_description=N'My First SSIS Catalog Folder'

When folder is created it contains 2 sub folders, Projects and Environments.
SQL11_Denali_SSIS_Package_Configuration_Parameters_13


The projects folder will hold the SSIS Project after deployment, In the environments folder we create the desired environments.
Let's create the Environment we need, see my previous blog for the T-SQL statements, for now right-click the Environment Folder and select Create Environment. Fill the pane with the information needed and click OK.
SQL11_Denali_SSIS_Package_Configuration_Parameters_14


A Environment section is created.
SQL11_Denali_SSIS_Package_Configuration_Parameters_15


Double click the Environment envFirstLook and select the Variables tab, note to my self connect.microsoft should this not be Parameters or Parameters and Variables. Enter the needed information, click OK.
SQL11_Denali_SSIS_Package_Configuration_Parameters_16


Now we have a SSIS Catalog, a Catalog Folder and the Environment. Next is to deploy our SSIS Project to the server.

In BIDS go to the Project Properties, build the project and then start the deployment process by right-click the project in the properties pane and select deploy.
The first step can be skipped, the second step is to select the source where to create the deployment package from. You can select the catalog and catalog folder to create a deployment from an existing deployed environment, for now we select the Project deployment file and click Next
SQL11_Denali_SSIS_Package_Configuration_Parameters_17


Now we need to select the destination where we like to deploy to. This is a three-way path // you can not skip the projectname.
SQL11_Denali_SSIS_Package_Configuration_Parameters_18


The next thing is to configure the Parameters, I left is as is and clicked Next.
SQL11_Denali_SSIS_Package_Configuration_Parameters_19


Now review your settings and click Deploy, when deployment is finished this is displayed as result, click Close.
SQL11_Denali_SSIS_Package_Configuration_Parameters_20


After deployment is finished take a look at SSMS to see of your deployment has been successful. When correct right-click the SSIS Project that is created. In the Project Properties pane select page References and select the previous created reference.
SQL11_Denali_SSIS_Package_Configuration_Parameters_21


Change the Parameter property for the created parameter by selecting Parameters in the Projects Properties Pane.
SQL11_Denali_SSIS_Package_Configuration_Parameters_28


We now can run the Package using the just created environment....
In SSMS right click the Package and select Run. In the run package pane Select at Environment Reference the previous created References and Click OK.
SQL11_Denali_SSIS_Package_Configuration_Parameters_23


The package progress is shown in the Package Running Pane. View it and look at the TargetDB you configured in the environment or the CREATE TABLE Statement is executed.
SQL11_Denali_SSIS_Package_Configuration_Parameters_24


On SQL01\MSSQL01 instance a see a TestAF table as mentioned in the CREATE TABLE statement. To make sure my Environments are working I created a New environment envFirstLookSQL03 that point to .... guess ofcourse SQL03\MSSQL01. And added it to the Project as a Second Reference.
SQL11_Denali_SSIS_Package_Configuration_Parameters_25

SQL11_Denali_SSIS_Package_Configuration_Parameters_26


Run the package againg but now select the envFirstLookSQL03 reference.
SQL11_Denali_SSIS_Package_Configuration_Parameters_27


On the Package run information we can see it has been successful and see on the Parameters tab that the connection has changed to the one that was entered in the envFirstLookSQL03 environment.
SQL11_Denali_SSIS_Package_Configuration_Parameters_29
SQL11_Denali_SSIS_Package_Configuration_Parameters_30
 

This it it for now, I would like to show how to use this when executing packages from a SQL Agent Job but this seems not to be implemented yet in SQL11 Denali CTP1 or I can just not find it!

Geen opmerkingen: