Create a simple project

This recipe will show you how to create an SSIS project to be able to use the custom logging level that we just created in the previous part of the recipe.

First, start SQL Server Data Tools and create a new Integration Services project: File | New | Integration Services Project. The following screenshot suggests how the project can be named:

Once the project has been created, we should see a package called Package.dtsx. We'll right-click on it and select Rename, as shown in the following screenshot:

We'll name it CustomLogging.dtsx. The solution should now look like the next screenshot:

We will now add a Data Flow Task in our package. A Data FlowTask is a container that will allow us to do data transformations. Its toolbox has a rich set of data transformation tools.

From the SSIS Toolbox, drag a Data Flow Task to the package's control flow as shown in the following screenshot:

Rename the Data Flow Task dft_dbo_CustomLogging, as shown in the following screenshot:

In the next chapter, we'll talk about the way we name our SSIS tasks and transforms.

In the next few steps, we'll start customizing our SSIS toolbox. Double-click on the dft_dbo_CustomLogging Data Flow Task to go into it.

Throughout the book, we'll often customize the SSIS Toolbox to suit our needs. You'll notice that the toolbox has sections such as: Favorites, Common, Azure (this will be covered in the next recipe), Other Sources, Other Transforms, and Other Destinations.

For now, we remove the source and destination assistants and add OLE DB Source and destination to the favorites transforms:

1. In the SSIS Toolbox, scroll down to Other Sources and right-click on OLE DB Source. From the contextual menu that appears, select Move to Favorites.

1. Next, scroll down again to Other Destinations, right-click on OLE DB Destination, and select Move to Favorites as shown in the following screenshot:
   
   
2. Now scroll up to the Favorites group at the top. We'll remove the source and destination assistants from the Favorites. As the following screenshot demonstrates, right-click on the Destination Assistant and select Move to Other Destinations. Repeat the same process for the Source Assistant:

1. Repeat same process for the Source Assistant. We'll move it to the Other Sources group.
2. Now, we're ready to create a connection manager. We'll use it to read or insert data into the TestCucstomLogging database that we created earlier. As shown in the following screenshot, right-click in the Connection Managers area and select New OLE DB Connection from the menu that appears.

1. Click on New in the Configure OLE DB Connection window that appears and the following screen will appear. Set the Server Name to the name of your machine or the named instance you might have chosen when you set up SQL Server. Select TestCustomlogging as the Database Name. The following screenshot shows the two properties set up for my PC. Click OK once finished.

1. You're back on click OK in the Configure OLE DB Connection Manager window as shown in the following screenshot:

1. We'll now rename our connection manager. Select the newly created connection manager (SSISCOOKBOOK TestCustomLogging) to cmgr_TestCustomLogging as shown in the following screenshot:

1. Now, from the favorite section of the SSIS toolbox, drag and drop an OLEDB source on the surface of the Data Flow. Now, as shown in the following screenshot, rename it to ole_src_SELECT_1. Double-click on it to get to the OLE DB Source Editor window. Set the OLE DB connection manager to cmgr_TestCustomLogging as demonstrated in the screenshot. Set the Data access mode to SQL command.

1. Now, enter the following SQL command in the command text and click on OK:

    SELECT 1 AS LogID
    UNION ALL
    SELECT 2 AS LogID

1. Again, from the Favorites section of the SSIS Toolbox, drag an OLE DB Destination in the Data Flow. Connect the source to the destination and rename the OLE_DB Destination ole_dst_dbo_CustomLogging. Double-click on it, assign the OLEDB connection manager property, and click OK on the New button at the right of the name of the table or the view property. The Create Table window appears. Modify the command as shown in the following screenshot and click on OK:

1. Click on Mappings in the list at the top left of the OLE DB Destination Editor. You should get the same screen as in the following screenshot:

1. Click on OK to close the OLE DB Destination Editor.
2. Now, we'll bring in the Layout toolbar. We'll use it throughout this book to format our package objects properly. Right-click anywhere in an empty section of the quick access toolbar and select Layout, as highlighted in the following screenshot:

1. Now, from the Edit menu, select Select All or press Ctrl + A to select the entire Data Flow content. In the Layout toolbar, click on the *, Make Same Width button, as shown in the following screenshot:

1. From the menu, select Auto Layout | Diagram. This will format the data flow objects, as shown in the following screenshot:

1. Now, right-click anywhere in the data flow task background and select Execute Task from the contextual menu that appears. The data flow should execute successfully as follows.

1. Now, we're ready to deploy our project to the SSIS Catalog that we created at the beginning of the chapter, in the recipe Creating an SSIS Catalog. Right-click on the CustomLogging project in the Solution Explorer at the top right of SSDT and hit Deploy. The project deployment wizard starts, as shown in the following screenshot:

1. The first page explains the step performed by the wizard. Check Do not show this page again as in the following screenshot if you want to skip this step in future deployments of the project or individual packages, as we'll see later in this chapter.

1. The wizard is now asking for an SSIS Catalog and folder. Since we don't have any folder yet in the catalog, we'll create one called CustomLogging, as shown in the following screenshot:
   1. Select the Server name by clicking on the Browse button. Select your machine name from the list and click on OK.
   2. The Path property specifies where the project will be deployed in the SSIS Catalog. To assign a path to the project deployment, click on Browse at the right of it. The Browse for Folderor Project window appears. We're going to create a folder for our project. Click on New folder. From the Create New Folder window that appears, fill the text boxes as shown in the following screenshot. Click OK to save and close the window.

1. Your screen will look like the following screenshot. Click OK again to close the Browse for Folder or Project window.

1. Back on the Integration Services Deployment Wizard, click on the Next button to go to the next deployment step. You should have a window similar to the following screenshot:

1. Click on Deploy, as shown in the following screenshot:

1. As the following screenshot shows, the project is deployed in the SSIS Catalog. If this fails, click on the Report button to investigate the error details. Whenever deployment errors occur, you can click on Previous to make a correction. Click Close to terminate the deployment wizard.