Connecting to DB2 using SSIS

To start, first download and install the Microsoft OLE DB Provider for DB2. Click here to download. After it is installed I suggest rebooting your machine. In some cases a reboot is not required; however I have encountered problems without rebooting.

Next open an SSIS project and create a new data source. On the Connection Manager screen select Microsoft OLE DB Provider for DB2 from the drop down list labeled Provider. In the text box labeled Server or file name enter the IP address of your server. Then select the radio button labeled Use a specific user name and password. Enter you user name and password and select the checkbox labeled allow saving password if you want to persist those values. Then enter you database name in the drop down list labeled Initial Catalog.

Now you must click the button labeled Data Links. The Data Source, User Name and Initial Catalog textboxes should be populated. You will only need to enter values for the textboxes labeled Package Collection and Default Schema. Enter your database schema in both textboxes.

Then click on the Advanced tab and check the box labeled Process binary as character.  To understand why you need to do this please read, How to Retrieve IBM AS 400 /JD Edwards “for bit data” via SSIS, by Alberto Munera.

Now click on the ellipses next to the Network drop down list and enter your IP address in the textbox labeled IP Address.

Click Ok twice and you will be returned to the Connection Manager Screen. Click the button labeled Test Connection to verify that your settings are correct. Now that you have a successful connection to your DB2 database click OK and the data source is ready to use. I have to give credit to my colleagues at TBC for assisting me in putting this document together, thanks Roberto and Chad.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Upcoming SQL Events

I realized this morning that I will be participating in three SQL Events this week and I want to share the event information with everyone.  Two of the events are available online and the other is a local user group meeting.  If you have time logon onto one of the webcasts or join me at the Baton Rouge SQL Server User Group.  The events are as follows:

Baton Rouge SQL Server User Group

Date and Time: 5/25/2010 5:45 PM CST

Event Location:  Lamar Advertising, 5551 Corporate BLVD, Baton Rouge, LA 70808

Presenter:  William Assaf

Description:  What are DMV's in SQL 2005/2008, and how can you use them to improve query performance, discover indexing needs and more?  Dynamic Management Views are essential tools for the SQL 2005/2008 administrator but have a wide range of applications by developers, network engineers and DBAs.

SQL PASS Appdev Presents

Date and Time:  5/25/2010 8:00 AM EST

Event URL:  https://www.livemeeting.com/cc/usergroups/join?id=PRNP75&role=attend

Presenter:  Patrick LeBlanc

Topic:  Introduction to SQL Profiler

Description: The SQL Server Profiler is a powerful, but underused tool. Often developers and some DBAs don’t realize the potential the tool has to offer. In this session I will show you how to use the tool to identify poor performing queries. I will also detail steps that can be used to identify blocking and deadlocking. Finally, I will provide sample scripts and methods that can be used to proactively monitor your SQL Server for inefficient queries and stored procedures.

SQL Lunch

Date and Time:  5/25/2010 11:30 AM CST

Event URL:  https://www.livemeeting.com/cc/usergroups/join?id=Q5CP7H&role=attend

Presenter:  Max Trinidad

Topic:  SQL Server and PowerShell Working w/Databases

Description:  Looking into using SQLPS.exe how to work with databases on multiple SQL Servers. See How-To Configure your PowerShell profile to make your scripting environment more productive. This session will have a lot of samples".

If you have time, try to attend these events.  If you are interested in speaking at any of these events please email me at pleblanc@sqllunch.com. 

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Report Builder 3.0 – Using Report Parts

Report Builder 3.0 offers many new features to its users.  One of the best features, in my opinion, is the ability to search and use Report Parts that have been published to the Report Server.  If you are looking for a tool that will provide Self-Service to your end-users this is a tool that you should consider adding to your arsenal. 

During many of my client engagements I am asked to create reports.  I will typically spend time reviewing existing reports and then I will recreate a report or two adding additional functionality and formatting.  Once the clients, specifically the business users view the reports there are comments such as:  this is good, but….

• can you move this table
• can you move this chart
• can you change the format of this column
• can you change the color of the header row

Then I go back and make the requested changes and present the reports once again.  Kind of a tedious process.  Even worse, in some cases more changes are required.  However, with the release of SQL Server Reporting Services R2, report developers can now publish parts of reports.  These parts include, maps, tables, charts, images, etc…  See my blog post, Publishing Report Parts, for more details on this topic.

Now instead of you, the report developer or DBA, spending your time shooting at a moving target recreating or modifying reports.  You can now enable the end-user to leverage all of your hard work without spending a tremendous amount of time teaching them how to use a new tool.

So to get started, open Report Builder 3.0.  There are a couple of ways to do this.  You can open it from the start menu or you can use the Report Builder button on Report Manager. 

Once the Report Builder is open you may need to connect to a Report Server.  Once you are connected, locate the tab on the right side of the Report Builder labeled Report Gallery. 

In the search box enter your search criteria or leave it blank and click the magnifying glass.  The results of the search will appear directly below. 

Either double-click an item in the list or drag into onto the report design surface.  Any datasets are parameters that the report part depends on will automatically be added to the report.

At the point you can continue to add items to the report and format it to me your needs.  Once you are done you have the ability to publish the report.  Click the save icon and type the Report Server path in the drop down list labeled Look in.  Select the location where you want to publish the report and click save.  The report can now be viewed by any that has permissions.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

SQL Server Reporting Services R2 – Publishing Report Parts

SQL Server Reporting Services R2 (SSRS) allows report developers to not only publish reports and data sources to the report server, but now they have the ability to publish report parts.  You can publish parts of a report, such as charts, tables, logos, etc…  In addition to the report part, any data set and/or parameter that the part depends on accompanies it as metadata.   End-users will have the ability, using Report Builder 3.0, to create reports based on the published Report Parts.  So, how do you publish the report parts?

First, create  a report that contains a few charts, tables and maybe an image.  Similar to the following:

Each item on the report, the five charts and the logo, can be published to the report server as a report part.  Ensure that you give each part that you plan on publishing a descriptive and meaningful name.  Once that is done, select Report from the menu bar and select Publish Report Parts.  The following screen will appear:

Then select the checkbox next to each item that you want to publish as a report part and click OK.  The next time you deploy your report project each selected item will be published to the report server.  Finally, prior to deploying the report parts, you can specify the location where these items will be deployed.  Right-click on the project in the solution explorer and select Properties:

 

Under the deployment section in the row labeled TargetReportPartFolder, you can specify the location where you want to deploy the report parts.  Your end-users can now connect to the report server and use these report parts in their reports.  I will explain how they do this in my next blog post.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Upcoming SQL Lunch Webcasts

First, I would like to thank all that have attended and supported the SQL Lunch during our first year.  We are continuing on and have some great Lunches scheduled for May and June.  In May I will be presenting Configuring, Deploying and Scheduling SSIS Packages and Max Trinidad will be presenting SQL Server and PowerShell Working with Databases.  In June we will have first time SQL Lunch Speaker, Stefan Bauer, presenting Warehouse Disk Layout 101.

The SQL Lunch Team has several upcoming topics on SQL Server 2008 R2, Power Pivot and Report Builder 3.0.  We are always looking for new presenters and new topics.  If you are interested in presenting on the SQL Lunch please email us at webmaster@sqllunch.com.  No matter if you are a seasoned presenter or a first time presenter, we welcome all. 

Thanks Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Creating Sparklines in PowerPivot

Sparklines are new to Microsoft Excel 2010 and Reporting Services 2008 R2.  In this post I will outline the steps required to create sparklines in an existing PowerPivot workbook.  Sparklines provide you with the ability to create a trending graph inline with your data.  So how do create these Sparklines.

Let’s assume that you already have a PowerPivot workbook that contains data.  In this example I will be using the AdventureWorks2008 database.  I have imported the following tables:  DimDate, DimSalesTerritory and FactInternetSales.   I am also assuming that you have already created a Pivot Table that resembles the following:

My columns in the Pivot Table are the CalendarYear from the DimDate table, the rows are the SalesTerritoryCountry from the DimSalesTerritoryTable and the SalesAmount from the FactInternetSales table is the value. 

Now that I have the Pivot Table created, I decided that I would like to see a graphical depiction of the sales for each country over each year inline with the data.  To accomplish this you can use sparklines.  To create the sparkline, click the tab labeled Insert and click the icon labeled Line in the sparkline section of the ribbon.  See the following:

When you click the icon the following dialogue box will appear:

In the textbox labeled Date Range you must specify the values that you want to trend.  In my sheet I selected all the data for every country for every year, excluding the column labeled Grand Total.  Similar to the following image:

In the above diagram note that all the data within the dashed lines are selected as the Data Range for my sparklines.  Next you must specify where you want to place the sparklines or the Location Range.  Select the empty columns to the left of the Grand Total column.  Ensure that you select the same number of rows that selected for the Data Range.  See the following image:

Click on the button labeled OK on the Create Sparklines dialogue box and now you have the sparklines.  Your speadsheet should resemble the following:

Now you not only have data, but you can quickly see how the data is trending for each country.  As always if you have any questions or comments please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

SQL Server Performance Tuning – Webcast

I recently gave a presentation on SQL Server Performance Tuning via Live Meeting.  I discussed how you could use the DMVs, SQL Server Profiler and Execution Plans to solve some of your performance problems.  There were over One hundred attendees in the session.  I was asked several times, if I would share my presentation and the demo scripts.  Since emailing each individual would be a true pain I stated that I would post all of the information in a blog post.  So for all of you that attended my session and anyone that may be interested click the below link to download.

Download: SQL Server Performance Tuning.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

2010 1st Quarter Review

I know it’s kind of late to write a review of my first quarter goals, since we are well into the second quart of 2010.  However, I did state in my blog post earlier this year, 2010 1st Quarter Goals, that I would keep you all abreast of my progress.  So, how did it do?  Unfortunately, I failed miserably on a few goals, but, on the other hand,I exceeded my expectations on others.  Here is the list from my initial post with a few comments about my progress:

1. Learn everything I can about SQL Server Analysis Server – I have been on several engagements since my posting and I have to say that my SSAS skills have definitely improved.  MDX is still somewhat of a challenge, but I am working on it. 

2. Two blog postings per week -   Yeah right.   In the first quarter I posted 22 blogs, which is pretty close to two, but there wasn’t any consistency to my posts.  This is something I am really going to work hard at improving.

3. Publish two articles – I did publish two articles on www.BIDN.com.  If you would like to give them a read, here they are:

Incremental Loads with Change Data Capture – Part: 2  Detecting Changes

SSRS – Creating a Master/Detail Report

4. Speak at a minimum of three events (SQLSaturday, SQLLunch, PASS Virtual Chapter, etc…)

I was able to speak at four events, speaking at 5 sessions.  The sessions are as follows:

Tampa Bay SQL Saturday – SQL Server Backup and Data Compression 101

Tampa Bay SQL Saturday – Iron Chef Data Warehouse

SQL Lunch Webcast – Introduction to Partitioning

Pragmatic Works February SQL Server 101 Series – Beginning T-SQL

Charlotte SQL Saturday - SQL Server Backup and Data Compression 101

5. Schedule SQL Lunch speakers for the rest of the year (at least 2 per month) – This I found was a very optimistic goal.  I have decided to scale it back to finding speakers for each quarter.  So if you want to speak please send an email to webmaster@sqllunch.com. 

6. Submit 2 videos to SQL Share – Unfortunately, I did not get around to recording one video for SQL Share, but I have two topics that are ready to go and I will get them submitted before the second quarter of 2010 is over.

7. Submit an abstract to SQL Server Standard (Grant Fritchey) – This item has been removed from my list until further notice.  I have be asked to participate in a few larger writing commitments that really monopolize a lot of my free time.  Once I have completed those obligations I will reevaluate and I try to get an abstract submitted.

As I stated in my first Goals post, my goals tend to change as the year progresses. With the exception of number 7 in the above list of the items, all of the goals will remain for the Second quarter of 2010.  I have already fallen behind in regards to my blog posts.  Therefore the goal is not to post twice a week, that goal has been modified to post at least once a week on a consistent basis.  In addition to that modification I have added one item to the list and it is as follows:

Submit three abstracts to the PASS conference. 

Maybe I will get selected.  Either way I plan on attending and I look forward to meeting many of you there.

Talk to you soon,

Patrick LeBlanc, MCTS

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.