Introduction

In yesterday’s discussion we looked at creating a current flag, creating views and looking at some of our data.  Today we shall be looking at creating a simple SQL Server Integration Services (SSIS) package to pull the data from Master Data Services and how to place it in another production database.

This reasons that I am tackling this subject are:

1)      We may want to limit access to the MDS database to only those folks with clearance to do so.

2)      There are GENUINELY folks who would like to pick up a little knowledge pertaining to creating a simple SSIS package.

3)      This background will help some folks further understand the logic that I plan to discuss in the creation of the daily MDS load discussion, to be covered in Part 07.

If you are well versed with Integration Services please stay tuned for Part 06 in which I shall be showing new and innovative ways of reporting, utilizing SQL Server Reporting Services 2012.

Getting started

As the data upon we wish to report, will be viewed by many decision makers (most of whom do NOT have access to Master Data Services), we are first going to create a data pump to push the data down to a production database to which decision makers do have access.

Another reason one may export the data is to bring it down from SQL Server 2012 or 2014 to a server running SQL Server 2008 R2.

The data pump

Bring up SQL Server Data Tools or SSDT. We shall be using SQL Server Integration Services or SSIS to pump the data from MDS down to our production data base.

Once open, we wish to create a new Project.

Simply select New and Project from the context menus (as shown above).

The ‘New Project’ data screen will be brought up. Simply select the top option, “Integration Services Project”. Give your project a name, and a location in which to reside (see the screen shot below).

Click OK.

Some users prefer to utilize the wizard (the second option shown above). The steps involved utilizing the wizard are beyond the scope of this paper.
Upon completing the steps mentioned above, you will find yourself on a screen where you will be doing all of your work.

From the SSIS Tool box in SQL Server 2012, WHICH IS DIFFERENT FROM PREVIOUS VERSIONS OF SSIS, select a ‘Data Flow Task’ and drag it onto the drawing surface (See below).

Now, right click in the Connection Manger at the bottom of the main screen and select a new connection manager for our project.

We shall describe the creation of only one connection. It is left to the reader to create the second connection.  The process of creating THIS second connection is the same.
To create a connection, simply choose the ‘New OLE DB Connection’ option from the drop down box.

The configure OLD DB screen will appear.

Click New (above the OK button)

The ‘Connection Manager’ screen is then brought up.

Simply enter the name of your server (as shown above), the authentication mode that you wish to use and finally the name of the database where your MDS data must be pumped to. In our case, the ORIGINAL product data that we have been looking at (in past within the MDS Database) will be copied to our ‘AmsterdamRally’ production database. Obviously and as discussed above, it is left to the reader to create a source connection to the MDS database in order for us to access the data in the first place. Again, the set up process is the same.

Test the connection, then click OK and you will be returned to the Configure OLE DB Connection Manager’ screen. Click OK to exit this screen.

The main drawing surface should look as shown below:

Click on the ‘Data Flow’ tab. The ‘Data Flow’ screen will then appear. Drag an OLE DB source and OLDE DB destination onto the screen (as shown below). The astute reader will note that I have included the second data connection (as discussed above) on the following screen dump.

Let us now configure our OLE DB source. Double click on the OLE DB source and the OLE DB source editor will appear. Select the MDS database as a source.

Click OK.

Simply find our view within the drop down box. In our case, we decided to call it ‘MyArticleProducts’ and click OK to complete the process and you will be brought back to the main ‘drawing’ surface.

Your screen should now appear as follows:

Double click on the OLE DB source and the editor will open.

Click the columns button on the upper left portion of the OLE DB Source Editor.

All of the columns within our view are shown and we most certainly do not need them all as many of these columns are system related and not required for THIS exercise. Simply UNCHECK the columns that you do not want.

Click OK to complete this step.
Once again, click on the OLE DB source and you will note that a gray arrow will appear. Drag the arrow to the OLE DB Destination as shown below.

Double click the OLE DB Destination and we shall now configure this connection.

Once again set your connection manager. This time I chose the ‘AmsterdamRally’ connection. As we do not have a destination table prepared for our data at this point in time, I choose the ‘New’ option for the ‘Name of the table or the view’ drop down.

You will note that the ONLY those fields that we told the system, we needed are present. Also note, the system wishes to call the table ‘OLE DB Destination’. This must be changed. We shall call it ‘MyProducts’.

Click OK and you will be brought back to the OLE DB Destination Editor.

You will probably note that the lovely table that we just created somehow does not appear in the ‘table or view’ text box. Simply click on the drop down box and find the table and accept that table name.

Click on the mappings tab to see how the Source and Destination fields have been
mapped. It should appear as follows:

Click OK to accept. Your screen should appear as follows:

Note that all those ugly red ‘X’ s should now have disappeared.

DEPENDING upon your SSIS installation, you may have to run the ‘job’ that we have just created in 32 bit mode. I know that I have to do so.

Click on the Project Tab at the top of the screen.

At the bottom of the context menu, you will note that there is an option, with your project name on it and it mentions properties. Select this choice (highlighted above).

On the ‘Configuration Properties’ tab, select the ‘Debugging’ mode tab and change the Run64BitRunTime textbox to ‘False’.

Click apply and OK. We are now set to load our data into the table.

Loading our data

Simply select the ‘Debug’ option above and the ’Start Debugging’ option as shown below:

If everything has been configured correctly you screen should appear as shown below:

Let us save everything now and exit from SQL Server Integration Services.
Within SQL Server Management Studio we can see the results of our efforts.

Wrapping up

Today we have seen

1)      How to create how to create a simple SQL Server Integration Services package to pull data from MDS to a production database.In a future discussion, I shall be showing you how one may incorporate our package into a daily load cycle.

In the next part of this article, we shall be starting off in SQL Server Reporting Services and we shall see how our data may be utilized.

As always, should you have any questions, comments or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy Programming