Importing CSV file into SQLServer and create table dynamically

Create a script task in SSIS and edit that script and paste the following code.

/*

   Microsoft SQL Server Integration Services Script Task

   Write scripts using Microsoft Visual C# 2008.

   The ScriptMain is the entry point class of the script.

*/

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;

namespace ST_f8e01f62d1ea477ca1a1d9370108e8ad.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

        /*

            The execution engine calls this method when the task executes.

            To access the object model, use the Dts property. Connections, variables, events,

            and logging features are available as members of the Dts property as shown in the following examples.

            To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;

            To post a log entry, call Dts.Log("This is my log text", 999, null);

            To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

            To use the connections collection use something like the following:

            ConnectionManager cm = Dts.Connections.Add("OLEDB");

            cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

            Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

           

            To open Help, press F1.

      */

        //public void Main()

        //{

        //    SqlConnection myADONETConnection = new SqlConnection();

        //    myADONETConnection = (SqlConnection)(Dts.Connections["Test1"].AcquireConnection(Dts.Transaction) as SqlConnection);

        //    MessageBox.Show(myADONETConnection.ConnectionString, "Test1");

        //    string line1 = "";//Reading file names one by one

        //    string SourceDirectory = @"D:\Project Documents\iCIS Enhancements\Damco\Oem Customization\HDE\HDE_iCIS_Historical_Data\";// TODO: Add your code

        //    string[] fileEntries = Directory.GetFiles(SourceDirectory);

        //    foreach (string fileName in fileEntries)

        //    {// do something with fileNameMessageBox.Show(fileName);

        //         string columname = "";//Reading first line of each file and assign to variable

        //        System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);

        //        string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".csv", "")).Replace("\\", ""));

        //        line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly + "') AND type in (N'U')) DROP TABLE [dbo]." + filenameonly + " Create Table dbo." + filenameonly + "(" + file2.ReadLine().Replace(",", " VARCHAR(100),") + " VARCHAR(100))").Replace(".txt", ""); file2.Close();

        //        //MessageBox.Show(line1.ToString());

        //        SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);

        //        myCommand.ExecuteNonQuery();

        //        //line1 = "BULK INSERT " + filenameonly + " FROM '" + fileName + "' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\\n' ) ";

        //        //SqlCommand myCommand1 = new SqlCommand(line1, myADONETConnection);

        //        //myCommand1.ExecuteNonQuery();

        //        //MessageBox.Show("TABLE IS CREATED");//Writing Data of File Into Table

        //        int counter = 0;

        //        string line;

        //        System.IO.StreamReader SourceFile =new System.IO.StreamReader(fileName);

        //        while ((line = SourceFile.ReadLine()) != null)

        //        {

        //                if (counter == 0){

        //                    columname = line.ToString();

        //                    //MessageBox.Show("INside IF");

        //                }

        //                else{

        //                    //MessageBox.Show("Inside ELSE");

        //                    string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";

        //                    //MessageBox.Show(query.ToString());

        //                    SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);

        //                    myCommand1.ExecuteNonQuery();

        //                }

        //            counter++;

        //            }

        //            SourceFile.Close();

        //        }

        //        Dts.TaskResult = (int)ScriptResults.Success;

        //    }

        public void Main()

        {

            try

            {

                // TODO: Add your code here

                SqlConnection myADONETConnection = new SqlConnection();

                myADONETConnection = (SqlConnection)(Dts.Connections["Test1"].AcquireConnection(Dts.Transaction) as SqlConnection);

                //MessageBox.Show(myADONETConnection.ConnectionString, "Test1");

                string line1 = "";//Reading file names one by one

                string filenameonly1 = "TempCSVParticipation";

                string SourceDirectory = Dts.Variables["mFilePath"].Value.ToString();//+ @"D:\Share\HBFR\Extracts\201201\Participation\";// TODO: Add your code

                MessageBox.Show(SourceDirectory);

                //string SourceDirectory = @"D:\Share\HBFR\Extracts\201201\Participation\";// TODO: Add your code

                string[] fileEntries = Directory.GetFiles(SourceDirectory);

                

                foreach (string fileName in fileEntries)

                {

                    //MessageBox.Show(fileName.ToString());

                    if (fileName.ToString().Contains("Actual"))

                    {

                        System.IO.StreamReader file2 = new System.IO.StreamReader(fileName);

                        string filenameonly = (((fileName.Replace(SourceDirectory, "")).Replace(".csv", "")).Replace("\\", ""));

                        line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." + filenameonly1 + "') AND type in (N'U')) DROP TABLE [dbo]." + filenameonly1 + " Create Table dbo." + filenameonly1 + "([" + file2.ReadLine().Replace(",", "] NVARCHAR(4000), [") + "] NVARCHAR(4000))").Replace(".txt", ""); file2.Close();

                        SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);

                        myCommand.ExecuteNonQuery();

                        line1 = "BULK INSERT " + filenameonly1 + " FROM '" + fileName + "' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\\n' ) ";

                        SqlCommand myCommand1 = new SqlCommand(line1, myADONETConnection);

                        myCommand1.CommandTimeout = 0;

                        myCommand1.ExecuteNonQuery();

                        //MessageBox.Show(fileName.ToString() + " Completed");

                    }

                }

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (Exception e)

            {

                MessageBox.Show(e.Message);

            }

        }

        }

    }

Working with Precedence Constraints in SQL Server Integration Services

Working with Precedence Constraints in SQL Server Integration Services

In SSIS, tasks are linked by precedence constraints.  A task will only execute if  the condition that is set by the precedence constraint preceding the task is met. By using these constraints, it is possible to ensure different execution paths depending on the success or failure of other tasks. This means that you can use  tasks with precedence constraints to determine the workflow of an SSIS package. We challenged Rob Sheldon to provide a straightforward  practical example of how to do it.

The control flow in a SQL Server Integration Services (SSIS) package defines the workflow for that package. Not only does the control flow determine the order in which executables (tasks and containers) will run, the control flow also determines under what conditions they’re executed. In other words, certain executables will run only when a set of defined conditions are met.

You configure the workflow by using precedence constraints. Precedence constraints link the individual executables together and determine how the workflow moves from one executable to the next. Figure 1 shows the control flow for the PrecedenceConstraints.dtsx package. The precedence constraints are the green and red arrows (both solid and dotted) that connect the tasks and container to each other. (There can also be blue arrow, as you’ll learn later in the article.)

Figure 1: Control Flow in the PrecedenceConstraints.dtsx SSIS package

As you would expect, the arrows define the direction of the workflow as it moves from one executable to the next. For example, after the first Execute SQL task runs, the precedence constraints direct the workflow to the next Execute SQL task and the Sequence container. One or both of these executables will run, depending on how the precedence constraints have been configured.

When a precedence constraint connects executables, the originating executable (the first to run) is referred to as the precedence executable. Multiple precedence constraints can originate from the precedence executable. In the first Execute SQL task in Figure 1, two precedence constraints originate from that precedence executable.

The task or container that is on the downstream end of the precedence constraint is referred to as the constrained executable. The constrained executable will run only if the conditions defined on the precedence constraint are met. If the conditions are not met, the constrained executable will not run. As a result, by configuring the precedence constraints, you can create complex workflows, while minimizing the need to configure duplicate tasks and containers.

Note that I created the PrecedenceConstraints.dtsx package in SSIS 2005. However, I upgraded the package to SSIS 2008 to ensure that precedence constraints are implemented the same in both versions. You can download the SSIS 2005 version of the PrecedenceConstraints.dtsx package in the speech bubble.

If you want to run the PrecedenceConstraints.dtsx package, you should first run the following Transact-SQL code against the AdventureWorks sample database:

IF EXISTS(

  SELECT table_name FROM information_schema.tables

  WHERE table_name = 'Employees')

DROP TABLE Employees

GO

CREATE TABLE Employees

(

  EmployeeID INT PRIMARY KEY,

  FirstName NVARCHAR(50) NOT NULL,

  LastName NVARCHAR(50) NOT NULL,

  Jobtitle NVARCHAR(50) NOT NULL

)

GO

IF EXISTS(

  SELECT table_name FROM information_schema.tables

  WHERE table_name = 'EmployeeLog')

DROP TABLE EmployeeLog

GO

CREATE TABLE EmployeeLog

(

  LogID INT IDENTITY PRIMARY KEY,

  LogFile VARCHAR(50) NULL,

  LogDateTime DATETIME NOT NULL DEFAULT GETDATE()

)

The code creates the Employees table and EmployeeLog table, both of which are necessary to run the PrecedenceConstraints.dtsx package. The package itself archives employee data, logs information about the package execution, truncates the Employees table if necessary, retrieves data through the HumanResources.vEmployee view, and loads it into the Employees table. The package uses different types of precedence constraints to control the workflow for these various operations. We’ll look at the workflow and precedence constraints in closer detail as we work through the article.

Defining Workflow by Success or Failure

By default, when a precedence constraint connects two executables, the constrained executable will run after the precedence executable successfully runs. However, if the precedence executable fails, that part of the workflow is interrupted, and the constrained executable does not run. You can override this behavior by setting the Value property on the precedence constraint. The property supports three options:

• Success: The precedence executable must run successfully for the constrained executable to run. This is the default value. The precedence constraint is set to green when the Success option is selected.
• Failure: The precedence executable must fail for the constrained executable to run. The precedence constraint is set to red when the Failure option is selected.
• Completion: The constrained executable will run after the precedence executable runs, whether the precedence executable runs successfully or whether it fails. The precedence constraint is set to blue when the Completion option is selected.

If you refer back to Figure 1, you’ll see that the two precedence constraints originate from the Data Flow task, one green and one red. If the Data Flow task runs successfully, the first Send Mail task will run because the green precedence constraint connects to that Send Mail task. Because the Value property of the precedence constraint is set to Success, the precedence constraint will evaluate to true and the workflow will continue along that path.

However, if the Data Flow task fails, the red precedence constraint will evaluate to true because its Value property is set to Failure. As a result, the second Send Mail task will run. This way, you can configure each Send Mail task differently so that you’re sending a unique email based on whether the Data Flow task succeeds or fails.

Note: The two Send Mail tasks and the SMTP connection manager are included here for demonstration purposes only. If you want to tests these tasks, you will need to point the connection manager to an actual SMTP server and configure the tasks appropriately. Otherwise, you should disable the tasks or they will fail when you try to run the package. (Even if they do fail, however, the package will still run and load the data as expected.)

Defining Workflow by Expressions

Although defining workflow by execution outcome (success, failure, or completion) can be useful, the workflow logic is still limited to that outcome. However, you can further refine your workflow by adding expressions to the precedence constraints. Any expression you add must be a valid SSIS expression and must evaluate to true or false.

To add an expression, double-click the precedence constraint to open the Precedence Constraint Editor dialog box, as shown in Figure 2. (The editor shown in this figure is the one for the precedence constraint that connects the first and second Execute SQL tasks.)

Figure 2: Defining an expression in the Precedence Constraint Editor dialog box

When adding an expression to a precedence constraint, the first step you must take is to select one of the following options from the Evaluation operation drop-down list:

• Constraint: The precedence constraint is evaluated solely on the option selected in the Value property. For example, if you select Constraint as the Evaluation operation option and select Success as the Value option (the default settings for both properties), the precedence constraint will evaluate to true only if the precedence executable runs successfully. When the precedence constraint evaluates to true, the workflow continues and the constrained executable runs. (When the Constraint option is selected, the Expression property is greyed out.)
• Expression: The precedence constraint is evaluated based on the expression defined in the Expression text box. If the expression evaluates to true, the workflow continues and the constrained executable runs. If the expression evaluates to false, the constrained executable does not run. (When the Expression option is selected, the Value property is greyed out.)
• Expression and Constraint: The precedence constraint is evaluated based on both the Value property and the expression. Both must evaluate to true for the constrained executable to run. For example, in the PrecedenceConstraints.dtsx package, the first Execute SQL task must run successfully and the expression must evaluate to true for the precedence constraint to evaluate to true and the constrained executable to run.
• Expression or Constraint: The precedence constraint is evaluated based on either the Value property or the expression. At least one of these properties must evaluate to true for the constrained executable to run.

After you’ve selected an option from the Evaluation operation list (and set the Value property, if appropriate), you’re next step is to define the expression. The expression I’ve used in this case (shown in Figure 2), determines whether the @EmployeeCount property equals 0 (@EmployeeCount == 0).

To better understand how this works, let’s take a quick look at the first Execute SQL task. The task runs the following SELECT statement to retrieve the number of rows in the Employees table:

SELECT COUNT(*) FROM Employees

The task then assigns the statement’s results (a scalar integer value) to the @EmployeeCount variable, which I defined when I set up the SSIS package.

The precedence constraint expression then uses the variable value to determine whether it equals 0. If it does, the expression evaluates to true. If not, it evaluates to false. Because the Value property precedence constraint is also set to Success, the first Execute SQL task must run successfully and the expression must evaluate to true for the precedence constraint as a whole to evaluate to true. If it does, the second Execute SQL task runs.

The precedence constraint that connects the first Execute SQL task to the Sequence container uses similar logic. However, the expression itself is slightly different:

@EmployeeCount > 0

In this case, the @EmployeeCount value must be greater than 0 for the expression to evaluate to true. If it does evaluate to true and the first Execute SQL task runs successfully, the Sequence container and the tasks within it will run.

Notice that the expressions in the two precedence constraints that originate from the first Execute SQL task are mutually exclusive. That is, only one of the two expressions can ever evaluate to true during a specific execution. That does not mean that you cannot have multiple precedence constraints with expressions that all evaluate to true during a single execution, but it does mean that you want to use caution when implementing expression to insure that they reflect exactly the logic you’re trying to implement in your workflow. In this case, I want to ensure that only one precedence constraint evaluates to true during a single execution.

Note: SSIS expressions are an entity unto themselves and unique to SSIS packages and their components. It is beyond the scope of this article to get into the details of expressions, but it is important to get them right. Be sure to refer to the topic “Integration Services Expression Reference” in SQL Server Books Online if you have any questions about SSIS expressions.

Defining Workflow by Logical AND or Logical OR

Two other important configuration options in a precedence constraint are the Logical OR and Logical AND settings. These settings apply only to constrained executables and only if those executables have more than one precedence constraint directed to it. For example, the Data Flow task in the PrecedenceConstraints.dtsx package (shown in Figure 1) has two precedence constraints pointing to it: one from the second Execute SQL task and one from the Sequence container.

If you refer back to Figure 2, you’ll find the following two options at the bottom of the Precedence Constraint Editor dialog box:

• Logical AND: All precedence constraints that point to the constrained executable must evaluate to true in order for that executable to run. This is the default option. If it is selected, the arrow is solid.
• Logical OR: Only one precedence constraint that points to the constrained executable must evaluate to true in order for that executable to run. If this option is selected, the arrow is dotted.

In the case of the precedence constraints that point to the Data Flow task, it is the second option that is selected, as indicated by the dotted lines. As a result, the workflow can originate from either the second Execute SQL task or from the Sequence container, and only one of the precedence executables has to run successfully.

The Logical OR and the Logical AND options let you define multiple execution paths, yet share those elements that are common to each path, such as the Data Flow task in the PrecedenceConstraints.dtsx package. The logical AND/OR settings, along with the Value property and the use of expressions, provide you with the options necessary to define intricate workflows while minimizing duplicate efforts.

Despite its simplicity, the PrecedenceConstraints.dtsx package described in this article demonstrates all these elements and should provide you with the foundation you need to use precedence constraints to their fullest. However, you can find additional information about precedence constraints in the topic “Setting Precedence Constraints on Tasks and Containers” in SQL Server Books Online.

Understanding the SSIS Package Protection Level

http://sqlblog.com/blogs/eric_johnson/archive/2010/01/12/understanding-the-ssis-package-protection-level.aspx

One property of all SSIS packages that you must understand is the ProtectionLevel. This property tells SSIS how to handle sensitive information stored within your packages. Most commonly this is a password stored in a connection string. Why is this information important? If you don’t set the ProtectionLevel correctly, the package may become unusable. Other developers may be unable to open the package or the package may fail when you go to execute it. Understanding these options lets you get out in front of possible problems and will help you to fix an issue if a problem crops up. In a perfect world, you would not need to store sensitive data, but each and every environment is different. Let’s look at each of the ProtectionLevel options.

DontSaveSensitive

When the package is saved, sensitive values will be removed. This will result in passwords needing to be supplied to the package, through a configuration file or by the user.

EncryptSensitiveWithUserKey

This will encrypt all sensitive data on the package with a key based on the current user profile. This sensitive data can only be opened by the user that saved it. It another user opens the package, all sensitive information will be replaced with blanks. This is often a problem when a package is sent to another user to work on.

EncryptSensitiveWithPassword

Sensitive data will be saved in the package and encrypted with a supplied password. Every time the package is opened in the designer, you will need to supply the password in order to retrieve the sensitive information. If you cancel the password prompt, you will be able to open the package but all sensitive data will be replaced with blanks. This works well if a package will be edited by multiple users.

EncryptAllWithPassword

This works the same as EncryptSensitiveWithPassword except that the whole package will be encrypted with the supplied password. When opening the package in the designer, you will need to specify the password or you won’t be able to view any part of the package.

EncryptAllWithUserKey

This works the same as EncryptSensitiveWithUserKey except that the whole package will be encrypted. Only the user that created the package will be allowed to open the package.

ServerStorage

This option will use SQL Server database roles to encrypt information. This will only work if the package is saved to an SSIS server for execution.

So that’s it. This option is pretty basic but it is important to understand so that you can be spared unnecessary frustration.

SSIS Package Deployment Model in SQL Server 2012 (Part 2 of 2)

Problem

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012. In this tip I cover what it is and how to get started to simplify your SSIS package deployments.

Solution

SSIS enhancements in SQL Server 2012 brings a brand new deployment model for SSIS project deployment. This new deployment model is called Project Deployment Model and unlike the Legacy Deployment Model where each package was a single unit of deployment, this new model creates a deployment packet containing everything (packages and parameters) needed for deployment in a single file with an ispac extension and hence streamlines the deployment process.
Apart from handling the deployment issues, managing the configuration file for each environment for each package was also a pain in the Legacy Deployment model. The new Project Deployment Model includes Project/Package Parameters, Environments, Environment variables and Environment references.
So before I jump into an example, let me first explain some of the key terms:
Project Deployment ModelAs I said before, this is the new deployment model for SSIS projects. By default all SSIS projects you create are created using this model only (you can also migrate your existing projects to this model). You can also revert back to the Legacy Deployment model if needed in Solution Explorer. To learn more about the differences between Legacy Deployment model and Project Deployment model click here.
When a project in this model is built, a deployment packet is created with an ispac extensions which includes all your packages and parameters in one packet. The deployment packet does not contain any additional files like text files, image files if you added them in the project. To learn more about Project Deployment Model click here.
Integration Service CatalogYou can create one Integration Services catalog per SQL Server instance. It stores application data (deployed projects including packages, parameters and environments) in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. When you create a catalog you need to provide a password which will be used to create a database master key for encryption and therefore it's recommended that you back up this database master key after creating the catalog.
The catalog uses SQLCLR (the .NET Common Language Runtime(CLR) hosted within SQL Server), so you need to enable CLR on the SQL Server instance before creating a catalog (I have provided the step below for this). This catalog also stores multiple versions of the deployed SSIS projects and if required you can revert to any of the deployed versions. The catalog also stores details about the operations performed on the catalog like project deployment with versions, package execution, etc.... which you can monitor on the server. There is one default job provided for cleanup of operation data and can be controlled by setting catalog properties. To learn more about this click here.
Project Parameters and Package ParametersIf you are using the project deployment model, you can create project parameters or package parameters. These parameters allow you to set the properties of package components at package execution time and change the execution behavior.
The basic difference between project parameters and package parameters is the scope. A project parameter can be used in any package of the project whereas the package level parameter is specific to the package where it has been defined. The best part of these parameters is that you can mark any of them as sensitive and it will be stored in an encrypted form in the catalog.
There can be three default values for these parameters:

• Design Default value is assigned and used in BIDS (Business Intelligence Development Studio),
• Server Default value is assigned when project comes in the catalog and overwrites the Design Default value and
• Execution value is assigned in reference to a specific environment variable during execution. To learn more click here.

Environments and Environment variables
An environment (development, test or production) is a container for environment variables which are used to apply different groups of values to the properties of package components by means of environment reference during runtime.
An environment reference is the mapping between an environment variable to pass a value to a property of a package component. A project can have multiple environment references, but a single instance of package execution can only use a single environment reference. This means that when you are executing your project/package you need to specify a single environment to use for that execution instance.
When defining a variable you can mark it sensitive and hence it will be stored in an encrypted form and NULL will be returned if you query it using T-SQL. To learn more click here.

---

Example - Development Need

What I want to do in this example, is create a project with multiple packages and build a deployment packet for the deployment. Then I will deploy the project to the Integration Services catalog and create different environments (TEST and PROD) and finally I will update the deployed project properties to use an environment reference. For execution, depending on the environment selected the data should be moved to the respective environment. The design of each individual package is very simple, it first truncates the destination table, moves data from the source (always the same for this example) to the destination (which varies depending on the environment reference chosen at execution time) and finally sends a confirmation email. For example, if I am choose the TEST environment the data should move to test database or if I choose the PROD environment the data should move to the production database.
These are the steps we will cover in this tip series:

1. Create an Integration Services Catalog
2. Create a SSIS project with Project Deployment Model
3. Deploy the project to Integration Services Catalog
4. Create Environments, Environment variables (Covered in the Part 2 tip of this series)
5. Set up environment reference in the deployed project (Covered in the Part 2 tip of this series)
6. Execute deployed project/package using the environment for example either for TEST or PROD (Covered in the Part 2 tip of this series)
7. Analyze the operations performed on the Integration Services Catalog (Covered in the Part 2 tip of this series)
8. Validate the deployed project or package (Covered in the Part 2 tip of this series)
9. Redeploy the project to Integration Services Catalog (Covered in the Part 2 tip of this series)
10. Analyze deployed project versions and restored to desired one (Covered in the Part 2 tip of this series)

---

1 - Creating Integration Services Catalog...

First of all we need to create an Integration Services catalog on the SQL Server instance (note: we can create only one Integration Service catalog on the SQL Server instance, though a catalog may contain many folders and inside each folder many projects). To create an Integration Services catalog connect to a SQL Server instance in SSMS (SQL Server Management Studio) and right click on the Integration Services node under the connected server in the Object Explorer and click on Create Catalog as shown below:

By default the catalog name appears as SSISDB and cannot be changed. You need to provide a strong password for the SSISDB Integration Services catalog you are creating which is used for the database master key. Let me tell you why you need to provide this; actually the Integration Services catalog stores application data in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. For encryption, it needs to have a database master key and for that only you need to provide a password here. It's recommended to backup the database master key after the Integration Services catalog creation.

The Integration Services catalog uses CLR based stored procedures and by default CLR is not enabled for a SQL Server instance, so you need to enable it before creating an Integration Services catalog.

To enable CLR on a SQL Server instance you can execute the script below. Once you have enabled CLR you can go ahead and create the Integration Service catalog as discussed above.

--Script #1 - Enabling CLR on the SQL Server Instance
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO 

Once an Integration Services Catalog gets created you can verify it in SSMS as shown below. As I mentioned before, an Integration Services Catalog stores application data in a SQL Server database and hence a database has been created with the same name as the Integration Services Catalog. If you browse through the database you will notice there are several tables which store the data, several views built on top of these tables and several stored procedure to access and manage this data.

Even though you can have a single Integration Services Catalog on each instance, each Integration Services Catalog can have several projects deployed to it. Let me first create a folder (AdventureWorks) for my project which I will be deploying next.

To create a folder, right click on the Integration Services Catalog name under Integration Services node in SSMS as shown above and click on the Create Folder menu item. This will launch the Create Folder dialog box as shown below; specify a name for the folder to create and a folder description:

Each folder that you create inside an Integration Services catalog will have two subfolders inside it by default as shown below. The Projects folder is a place where you will be deploying your SSIS project and the Environments folder is a place where you will be creating multiple environments like development, test, PPE (Pre Production environment), Production, etc... I will discuss these folders more in a later tip in this series.

---

2 - Creating a SSIS project with Project Deployment Model...

The way we develop SSIS project/package in SQL Server 2012 remains the same as what we have been doing, so I am not going to talk in detail about SSIS project/package creation (to learn basics of SSIS you can refer to this tutorial) but rather directly jump into the development for the example. One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on "Convert to Legacy Deployment Model".
A SSIS project in Project Deployment model creates a deployment packet (with *.ispac extension) that contains everything (all packages/parameters) needed for deployment unlike the Legacy Deployment mode in which each SSIS package is separate unit of deployment.
As per our requirement we need to have one SSIS project with a couple of packages. I will keep the design of the package very simple, it first truncates the target table, moves the data from the source to the target table and sends the confirmation email at the end.

Now what I want is to move data to a table in a different database depending on the environment. For example, if the package is executed with the TEST environment reference then data should be moved to AdventuresWorks2008R2Test database and if the package is executed with the PROD environment reference then data should be moved to AdventuresWorks2008R2Prod database. So let me create one Project Parameter to hold the name of the database which will be passed from an environment variable and used in the TargetConnection's connection string to connect to the target database. To create a project parameter, right click on the project in the Solution Explorer and click on the Project Parameters menu item as shown below:

On the Project Parameters window, click on the New Parameter icon in the upper left and specify the name of the project parameter. In this case I want the parameter to hold the database name hence I have specified DatabaseName name for the parameter and the default value specified as "AdventureWorks2008R2Test". This means if the package is executed in the designer, data will move to AdventureWorks2008R2Test database.

The package also contains two connection managers to connect to the databases. SourceConnection connection manager connects to the source to pull data from and will remain constant whereas TargetConnection connection manager gets the database name from a project parameter and varies from environment to environment. Apart from these two connection managers to connect to the databases, I have one SMTP connection manager which is used to send confirmation email as shown in the package design.

To make a connection manager to dynamically use the database name, you need to configure the Expression property of the TargetConnection connection manager and specify the value of InitialCatalog property to come from an expression (in this case project parameter which we created above) as shown below:

As we have specified a default value for the project parameter, if you execute the package it will execute successfully and will move the data to AdventureWorks2008R2Test database (the default value specified for the project parameter).

---

3 - Deploying the project to Integration Services Catalog...

Now that we are done with project and package development, we need to deploy the project to the Integration Services catalog we created above. To deploy the project, right click on the project in the Solution Explorer and click on the Deploy menu as shown below:

Clicking on the Deploy menu item will launch the Integration Services Deployment wizard and the first screen of the wizard is a welcome screen as shown below. You can choose not to show this screen next time by clicking on the checkbox on bottom. Click on Next button to move ahead:

The second screen of the wizard is the place where you actually specify the location to deploy from; you can either choose the deployment packet (*.ispac) file or choose an already deployed package from the Integration Services catalog as the source for the deployment. Since I want to deploy from the SSIS project, I have specified the *.ispac deployment packet name:

The third screen of the wizard lets you specify the destination where you want to deploy the project. You need to choose the name of the server where you have created the Integration Services catalog and the folder in the catalog where you want to deploy the project. I will use the folder I created above to deploy the project as shown below:

As I mentioned before, a parameter can have a server default value, the next screen is the place where you define the server default values for all the parameters of the project. Here you can either use the same design default value, specify a new value or choose the value to come from a variable as shown below:

The next screen of the wizard is a review screen where you review your selections, you can click on the Deploy button to start the deployment of the project as shown below:

The final screen of the wizard shows the deployment progress and deployment status as you can see below. If there are any failures they will be marked red and you can click on the Result column to see the reason for the failure.

Since our deployed was successful as shown above, we can connect to the Integration Services Catalog using SSMS and verify the deployment as shown below:

---

Summary

In this article I talked about the basics of the new SSIS deployment model called Project Deployment Model, how it differs from the Legacy Deployment Model, how to create an Integration Service Catalog, how to create a project with the Project Deployment Model and finally how to deploy a SSIS project to the Integration Services Catalog.
Stay tuned for my next tip in this series, in which I will discuss creating environments, environment variables, setting up an environment reference in the deployed project, executing deployed project/package using the environment for example either TEST or PROD, analyzing the operations performed on the Integration Services Catalog, validating the deployed project or package, redeploying the project to Integration Services Catalog, analyzing the deployed project versions and restoring to a previous version.
Notes:

• I have shown features and power of Integration Services catalog using the UI (User Interface), but you can also manage and control it using T-SQL commands.
• The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in further CTPs or in the final/RTM release.

SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2)

http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012-part-1-of-2/

Problem

Deployment has always been a challenge for SSIS developers to deploy packages. SSIS developers are envious of SSRS/SSAS developers as they have an easy way to create a single unit of deployment (deployment package) that contains everything needed for the deployment. The good news is the inclusion of the SSIS Package Deployment Model in SQL Server 2012. In this tip I cover what it is and how to get started to simplify your SSIS package deployments.

Solution

SSIS enhancements in SQL Server 2012 brings a brand new deployment model for SSIS project deployment. This new deployment model is called Project Deployment Model and unlike the Legacy Deployment Model where each package was a single unit of deployment, this new model creates a deployment packet containing everything (packages and parameters) needed for deployment in a single file with an ispac extension and hence streamlines the deployment process.
Apart from handling the deployment issues, managing the configuration file for each environment for each package was also a pain in the Legacy Deployment model. The new Project Deployment Model includes Project/Package Parameters, Environments, Environment variables and Environment references.
So before I jump into an example, let me first explain some of the key terms:
Project Deployment ModelAs I said before, this is the new deployment model for SSIS projects. By default all SSIS projects you create are created using this model only (you can also migrate your existing projects to this model). You can also revert back to the Legacy Deployment model if needed in Solution Explorer. To learn more about the differences between Legacy Deployment model and Project Deployment model click here.
When a project in this model is built, a deployment packet is created with an ispac extensions which includes all your packages and parameters in one packet. The deployment packet does not contain any additional files like text files, image files if you added them in the project. To learn more about Project Deployment Model click here.
Integration Service CatalogYou can create one Integration Services catalog per SQL Server instance. It stores application data (deployed projects including packages, parameters and environments) in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. When you create a catalog you need to provide a password which will be used to create a database master key for encryption and therefore it's recommended that you back up this database master key after creating the catalog.
The catalog uses SQLCLR (the .NET Common Language Runtime(CLR) hosted within SQL Server), so you need to enable CLR on the SQL Server instance before creating a catalog (I have provided the step below for this). This catalog also stores multiple versions of the deployed SSIS projects and if required you can revert to any of the deployed versions. The catalog also stores details about the operations performed on the catalog like project deployment with versions, package execution, etc.... which you can monitor on the server. There is one default job provided for cleanup of operation data and can be controlled by setting catalog properties. To learn more about this click here.
Project Parameters and Package ParametersIf you are using the project deployment model, you can create project parameters or package parameters. These parameters allow you to set the properties of package components at package execution time and change the execution behavior.
The basic difference between project parameters and package parameters is the scope. A project parameter can be used in any package of the project whereas the package level parameter is specific to the package where it has been defined. The best part of these parameters is that you can mark any of them as sensitive and it will be stored in an encrypted form in the catalog.
There can be three default values for these parameters:

• Design Default value is assigned and used in BIDS (Business Intelligence Development Studio),
• Server Default value is assigned when project comes in the catalog and overwrites the Design Default value and
• Execution value is assigned in reference to a specific environment variable during execution. To learn more click here.

Environments and Environment variables
An environment (development, test or production) is a container for environment variables which are used to apply different groups of values to the properties of package components by means of environment reference during runtime.
An environment reference is the mapping between an environment variable to pass a value to a property of a package component. A project can have multiple environment references, but a single instance of package execution can only use a single environment reference. This means that when you are executing your project/package you need to specify a single environment to use for that execution instance.
When defining a variable you can mark it sensitive and hence it will be stored in an encrypted form and NULL will be returned if you query it using T-SQL. To learn more click here.

---

Example - Development Need

What I want to do in this example, is create a project with multiple packages and build a deployment packet for the deployment. Then I will deploy the project to the Integration Services catalog and create different environments (TEST and PROD) and finally I will update the deployed project properties to use an environment reference. For execution, depending on the environment selected the data should be moved to the respective environment. The design of each individual package is very simple, it first truncates the destination table, moves data from the source (always the same for this example) to the destination (which varies depending on the environment reference chosen at execution time) and finally sends a confirmation email. For example, if I am choose the TEST environment the data should move to test database or if I choose the PROD environment the data should move to the production database.
These are the steps we will cover in this tip series:

1. Create an Integration Services Catalog
2. Create a SSIS project with Project Deployment Model
3. Deploy the project to Integration Services Catalog
4. Create Environments, Environment variables (Covered in the Part 2 tip of this series)
5. Set up environment reference in the deployed project (Covered in the Part 2 tip of this series)
6. Execute deployed project/package using the environment for example either for TEST or PROD (Covered in the Part 2 tip of this series)
7. Analyze the operations performed on the Integration Services Catalog (Covered in the Part 2 tip of this series)
8. Validate the deployed project or package (Covered in the Part 2 tip of this series)
9. Redeploy the project to Integration Services Catalog (Covered in the Part 2 tip of this series)
10. Analyze deployed project versions and restored to desired one (Covered in the Part 2 tip of this series)

---

1 - Creating Integration Services Catalog...

First of all we need to create an Integration Services catalog on the SQL Server instance (note: we can create only one Integration Service catalog on the SQL Server instance, though a catalog may contain many folders and inside each folder many projects). To create an Integration Services catalog connect to a SQL Server instance in SSMS (SQL Server Management Studio) and right click on the Integration Services node under the connected server in the Object Explorer and click on Create Catalog as shown below:

By default the catalog name appears as SSISDB and cannot be changed. You need to provide a strong password for the SSISDB Integration Services catalog you are creating which is used for the database master key. Let me tell you why you need to provide this; actually the Integration Services catalog stores application data in a SQL Server database and uses SQL Server encryption to encrypt sensitive data. For encryption, it needs to have a database master key and for that only you need to provide a password here. It's recommended to backup the database master key after the Integration Services catalog creation.

The Integration Services catalog uses CLR based stored procedures and by default CLR is not enabled for a SQL Server instance, so you need to enable it before creating an Integration Services catalog.

To enable CLR on a SQL Server instance you can execute the script below. Once you have enabled CLR you can go ahead and create the Integration Service catalog as discussed above.

--Script #1 - Enabling CLR on the SQL Server Instance
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO 

Once an Integration Services Catalog gets created you can verify it in SSMS as shown below. As I mentioned before, an Integration Services Catalog stores application data in a SQL Server database and hence a database has been created with the same name as the Integration Services Catalog. If you browse through the database you will notice there are several tables which store the data, several views built on top of these tables and several stored procedure to access and manage this data.

Even though you can have a single Integration Services Catalog on each instance, each Integration Services Catalog can have several projects deployed to it. Let me first create a folder (AdventureWorks) for my project which I will be deploying next.

To create a folder, right click on the Integration Services Catalog name under Integration Services node in SSMS as shown above and click on the Create Folder menu item. This will launch the Create Folder dialog box as shown below; specify a name for the folder to create and a folder description:

Each folder that you create inside an Integration Services catalog will have two subfolders inside it by default as shown below. The Projects folder is a place where you will be deploying your SSIS project and the Environments folder is a place where you will be creating multiple environments like development, test, PPE (Pre Production environment), Production, etc... I will discuss these folders more in a later tip in this series.

---

2 - Creating a SSIS project with Project Deployment Model...

The way we develop SSIS project/package in SQL Server 2012 remains the same as what we have been doing, so I am not going to talk in detail about SSIS project/package creation (to learn basics of SSIS you can refer to this tutorial) but rather directly jump into the development for the example. One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on "Convert to Legacy Deployment Model".
A SSIS project in Project Deployment model creates a deployment packet (with *.ispac extension) that contains everything (all packages/parameters) needed for deployment unlike the Legacy Deployment mode in which each SSIS package is separate unit of deployment.
As per our requirement we need to have one SSIS project with a couple of packages. I will keep the design of the package very simple, it first truncates the target table, moves the data from the source to the target table and sends the confirmation email at the end.

Now what I want is to move data to a table in a different database depending on the environment. For example, if the package is executed with the TEST environment reference then data should be moved to AdventuresWorks2008R2Test database and if the package is executed with the PROD environment reference then data should be moved to AdventuresWorks2008R2Prod database. So let me create one Project Parameter to hold the name of the database which will be passed from an environment variable and used in the TargetConnection's connection string to connect to the target database. To create a project parameter, right click on the project in the Solution Explorer and click on the Project Parameters menu item as shown below:

On the Project Parameters window, click on the New Parameter icon in the upper left and specify the name of the project parameter. In this case I want the parameter to hold the database name hence I have specified DatabaseName name for the parameter and the default value specified as "AdventureWorks2008R2Test". This means if the package is executed in the designer, data will move to AdventureWorks2008R2Test database.

The package also contains two connection managers to connect to the databases. SourceConnection connection manager connects to the source to pull data from and will remain constant whereas TargetConnection connection manager gets the database name from a project parameter and varies from environment to environment. Apart from these two connection managers to connect to the databases, I have one SMTP connection manager which is used to send confirmation email as shown in the package design.

To make a connection manager to dynamically use the database name, you need to configure the Expression property of the TargetConnection connection manager and specify the value of InitialCatalog property to come from an expression (in this case project parameter which we created above) as shown below:

As we have specified a default value for the project parameter, if you execute the package it will execute successfully and will move the data to AdventureWorks2008R2Test database (the default value specified for the project parameter).

---

3 - Deploying the project to Integration Services Catalog...

Now that we are done with project and package development, we need to deploy the project to the Integration Services catalog we created above. To deploy the project, right click on the project in the Solution Explorer and click on the Deploy menu as shown below:

Clicking on the Deploy menu item will launch the Integration Services Deployment wizard and the first screen of the wizard is a welcome screen as shown below. You can choose not to show this screen next time by clicking on the checkbox on bottom. Click on Next button to move ahead:

The second screen of the wizard is the place where you actually specify the location to deploy from; you can either choose the deployment packet (*.ispac) file or choose an already deployed package from the Integration Services catalog as the source for the deployment. Since I want to deploy from the SSIS project, I have specified the *.ispac deployment packet name:

The third screen of the wizard lets you specify the destination where you want to deploy the project. You need to choose the name of the server where you have created the Integration Services catalog and the folder in the catalog where you want to deploy the project. I will use the folder I created above to deploy the project as shown below:

As I mentioned before, a parameter can have a server default value, the next screen is the place where you define the server default values for all the parameters of the project. Here you can either use the same design default value, specify a new value or choose the value to come from a variable as shown below:

The next screen of the wizard is a review screen where you review your selections, you can click on the Deploy button to start the deployment of the project as shown below:

The final screen of the wizard shows the deployment progress and deployment status as you can see below. If there are any failures they will be marked red and you can click on the Result column to see the reason for the failure.

Since our deployed was successful as shown above, we can connect to the Integration Services Catalog using SSMS and verify the deployment as shown below:

---

Summary

In this article I talked about the basics of the new SSIS deployment model called Project Deployment Model, how it differs from the Legacy Deployment Model, how to create an Integration Service Catalog, how to create a project with the Project Deployment Model and finally how to deploy a SSIS project to the Integration Services Catalog.
Stay tuned for my next tip in this series, in which I will discuss creating environments, environment variables, setting up an environment reference in the deployed project, executing deployed project/package using the environment for example either TEST or PROD, analyzing the operations performed on the Integration Services Catalog, validating the deployed project or package, redeploying the project to Integration Services Catalog, analyzing the deployed project versions and restoring to a previous version.
Notes:

• I have shown features and power of Integration Services catalog using the UI (User Interface), but you can also manage and control it using T-SQL commands.
• The sample code, example and UI is based on SQL Server 2012 CTP 1, it might change in further CTPs or in the final/RTM release. 

CTE, Recursive Call, Find out number of working days between a date range

http://www.sqlservercentral.com/Forums/Topic779830-338-1.aspx


DECLARE @STARTDATE datetime;
DECLARE @EntDt datetime;
set @STARTDATE = '01/01/2009';
set @EntDt = '12/31/2009';
declare @dcnt int;
;with DateList as  
 (  
    select @STARTDATE DateValue  
    union all  
    select DateValue + 1 from    DateList    
    where   DateValue + 1 < convert(VARCHAR(15),@EntDt,101)  
 )  
 select count(*) as DayCnt from (  
  select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList
  where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )    
  )a
option (maxrecursion 365);