Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Master Data Services for the Beginner Part 05

 

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.

MDS for the beginner part0501

Once open, we wish to create a new Project.

MDS for the beginner part0502

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).

MDS for the beginner part0503

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.

MDS for the beginner part0504

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).

MDS for the beginner part0505

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

MDS for the beginner part0506

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.

MDS for the beginner part0507

Click New (above the OK button)

The ‘Connection Manager’ screen is then brought up.

MDS for the beginner part0508

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:

MDS for the beginner part0509

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.

MDS for the beginner part0510

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.

MDS for the beginner part0511

Click OK.

MDS for the beginner part0512

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:

MDS for the beginner part0513

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

MDS for the beginner part0514

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

MDS for the beginner part0515

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.

MDS for the beginner part0516

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.

MDS for the beginner part0517

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

MDS for the beginner part0518

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.

MDS for the beginner part0519

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’.

MDS for the beginner part0520

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.

MDS for the beginner part0521

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

MDS for the beginner part0522

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

MDS for the beginner part0523

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.

MDS for the beginner part0524

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).

MDS for the beginner part0525

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

MDS for the beginner part0526

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:

MDS for the beginner part0527

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

MDS for the beginner part0528

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.

MDS for the beginner part0529

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.