Boston BI Blog

Speaking at SQLSaturday #39 New York City, April 24th

Slava Kokaev — Sun, 11 Apr 2010 18:37:57 GMT

I am going to be speaking at at SQLSaturday #39 New York City this month. I am going to be talking about Building Cusome Business Intelligence Web Application and BI Integration. This event is taking place on Saturday, April 24, 2010 at the Microsoft New York City offices in Midtown Manhattan. If you want to know more about it or are thinking of coming then head over to http://www.sqlsaturday.com/39/eventhome.aspx

Northeast Community Leader Social - February 2010

Slava Kokaev — Thu, 11 Feb 2010 09:33:11 GMT

I would like to say Thank you to Jim O'Neil, Chris Bowen, and Dan Stolts from Microsoft for the Great Event and opportunity to meet with New England .NET folks.

US ISV Developer Community : Free BI Developer Training Kit for SQL Server 2008 R2 Released

Slava Kokaev — Wed, 10 Feb 2010 14:36:18 GMT

Bruce Kyle highlights a free Business Intelligence Training Kit for SQL Server 2008 R2 from Microsoft which consists of videos, presentations, demonstrations and hands on labs introducing the key concepts of BI using SQL Server 2008 R2

US ISV Developer Community : Free BI Developer Training Kit for SQL Server 2008 R2 Released

Dec 1 meeting recording available for Download

Slava Kokaev — Mon, 07 Dec 2009 20:31:25 GMT

December 1 meeting recorded by Brian Knight now available for download from here: Introduction to SSIS by Brian Knight .

MCTS Seminars and Tutorials

Slava Kokaev — Sun, 29 Nov 2009 15:21:54 GMT

Business Intelligence Fundamentals - Developing SSIS Packages

Creating New Packages, Data Sources, and Connections

The following tutorials familiarize you with the common tasks of creating a new project in

BIDS and working with data sources and connections.

Creating New Packages, Data Sources, and Connections

1 Create the Project and Packages

2 Create Project Data Sources

3 Create New Package Connections from the Project Data Sources

Creating and Editing a Control Flow Task

1 Create a Control Flow Task and Test Package Execution

2 Modify the DimCustomer ETL Package Control Flow

References

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

Developing SSIS Packages - Modify the DimCustomer ETL Package Control Flow

Slava Kokaev — Sun, 29 Nov 2009 14:55:17 GMT

In this tutorial, you will start the process of building the DimCustomer SSIS package that will

handle the ETL process from the AdventureWorks2008 database to the AdventureWorks-

DW2008 database.

1. If necessary, start SQL Server Business Intelligence Development Studio (BIDS), open

the project SSIS Project you created in tutorial, “Creating SSIS Packages

and Data Sources,” and then open the empty DimCustomer package.

2. From the toolbox, drag two Execute SQL Tasks onto the control flow workspace and

then drag one Data Flow Task onto the workspace.

3. Next, connect the first Execute SQL Task to the Data Flow Task by dragging the green

precedence constraint from the Execute SQL Task onto the Data Flow Task. Then connect

the green precedence constraint from the Data Flow Task to the second Execute

SQL Task.

4. Rename the first Execute SQL Task to Truncate Update Table, and rename the second

Execute SQL Task to Batch Updates.

Your control flow for the DimCustomer package should contain an Execute SQL Task,

followed by a Data Flow Task, followed by another Execute SQL Task.

5. Before editing the tasks in SSIS, open SSMS, connect to the Database Engine, and create

a new query against the AdventureWorksDW2008 database. Execute the following

code.

USE AdventureWorksDW2008

CREATE TABLE [dbo].[stgDimCustomerUpdates](

[CustomerAlternateKey] [nvarchar](15) NULL,

[AddressLine1] [nvarchar](60) NULL,

[AddressLine2] [nvarchar](60) NULL,

[BirthDate] [datetime] NULL,

[CommuteDistance] [nvarchar](15) NULL,

[DateFirstPurchase] [datetime] NULL,

[EmailAddress] [nvarchar](50) NULL,

[EnglishEducation] [nvarchar](40) NULL,

[EnglishOccupation] [nvarchar](100) NULL,

[FirstName] [nvarchar](50) NULL,

[Gender] [nvarchar](1) NULL,

[GeographyKey] [int] NULL,

[HouseOwnerFlag] [nvarchar](1) NULL,

[LastName] [nvarchar](50) NULL,

[MaritalStatus] [nvarchar](1) NULL,

[MiddleName] [nvarchar](50) NULL,

[NumberCarsOwned] [tinyint] NULL,

[NumberChildrenAtHome] [tinyint] NULL,

[Phone] [nvarchar](25) NULL,

[Suffix] [nvarchar](10) NULL,

[Title] [nvarchar](8) NULL,

[TotalChildren] [tinyint] NULL,

[YearlyIncome] [nvarchar](100) NULL) ON [PRIMARY]

6. After you have successfully created the table, switch back to the DimCustomer SSIS

package and edit the Execute SQL Task named Truncate Update Table.

7. In the Execute SQL Task Editor dialog box, set the Connection property to Adventure-

WorksDW2008, and then enter the following SQL code in the SQLStatement property

before clicking OK to save it:

TRUNCATE TABLE dbo.stgDimCustomerUpdates

8. Edit the last Execute SQL Task, named Batch Updates, and set the Connection property

to AdventureWorksDW2008.

9. In the SQLStatement property, enter the following UPDATE statement.

UPDATE dbo.DimCustomer

SET AddressLine1 = stgDimCustomerUpdates.AddressLine1

, AddressLine2 = stgDimCustomerUpdates.AddressLine2

, BirthDate = stgDimCustomerUpdates.BirthDate

, CommuteDistance = stgDimCustomerUpdates.CommuteDistance

, DateFirstPurchase = stgDimCustomerUpdates.DateFirstPurchase

, EmailAddress = stgDimCustomerUpdates.EmailAddress

, EnglishEducation = stgDimCustomerUpdates.EnglishEducation

, EnglishOccupation = stgDimCustomerUpdates.EnglishOccupation

, FirstName = stgDimCustomerUpdates.FirstName

, Gender = stgDimCustomerUpdates.Gender

, GeographyKey = stgDimCustomerUpdates.GeographyKey

, HouseOwnerFlag = stgDimCustomerUpdates.HouseOwnerFlag

, LastName = stgDimCustomerUpdates.LastName

, MaritalStatus = stgDimCustomerUpdates.MaritalStatus

, MiddleName = stgDimCustomerUpdates.MiddleName

, NumberCarsOwned = stgDimCustomerUpdates.NumberCarsOwned

, NumberChildrenAtHome = stgDimCustomerUpdates.NumberChildrenAtHome

, Phone = stgDimCustomerUpdates.Phone

, Suffix = stgDimCustomerUpdates.Suffix

, Title = stgDimCustomerUpdates.Title

, TotalChildren = stgDimCustomerUpdates.TotalChildren

FROM dbo.DimCustomer DimCustomer

INNER JOIN dbo.stgDimCustomerUpdates

ON DimCustomer.CustomerAlternateKey

= stgDimCustomerUpdates.CustomerAlternateKey

10. Click OK in the Execute SQL Task Editor dialog box, and then save the package. In the

next lesson, you will complete the data flow portion of this package and then test the

execution.

References

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

Developing SSIS Packages - Create a Control Flow Task and Test Package Execution

Slava Kokaev — Sun, 29 Nov 2009 14:32:37 GMT

In this tutorial, you will work with control flow tasks and execute packages in the SSIS Designer.

1. If necessary, start SQL Server Business Intelligence Development Studio (BIDS), open

the project SSIS Project you created in this tutorial , “Creating SSIS Packages

and Data Sources,” or open the completed exercise file from the companion CD, and

then edit the package named MyPackage.dtsx (right-click the package in Solution

Explorer, and then click Open).

2. Open the Toolbox window by selecting Toolbox from the View menu, locate the Execute

SQL Task item, and drag it to the control flow workspace of your package.

3. Edit the Execute SQL Task object by double-clicking the task icon or by right-clicking

the task icon and then clicking Edit.

4. Change the Connection property to use the AdventureWorks2008 connection.

5. In the SQL Statement property of the Execute SQL Task Editor dialog box, type the

following code:

UPDATE Production.Product

SET ProductLine = 's'

WHERE ProductLine IS NULL

6. Click OK in the Execute SQL Task dialog box to return to the SSIS Designer. Right-click

the Execute SQL Task, click Rename, and type Update ProductLine.

7. Next, drag a Sequence Container object from the toolbox onto the control flow

workspace.

8. Drag the Update ProductLine Execute SQL Task you just created into the Sequence

Container so that the task is nested in the Sequence Container box.

9. To test the execution of the package, click Start Debugging on the Standard toolbar or

choose Start Debugging from the Debug menu.

10. When the package execution is complete, your Sequence Container and Execute SQL

Task should be green.

11. Click the Execution Results tab (named Progress while the package is executing) in the

SSIS Designer to view the execution details.

12. Select the Stop button from the tool menu to stop the debugger (or choose Debug,

Stop Debugging from the Debug menu).

13. Click the Save All button on the BIDS toolbar.

References

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

Developing SSIS Packages - Create New Package Connections from the Project Data Sources

Slava Kokaev — Sun, 29 Nov 2009 12:41:36 GMT

In this exercise, you will add the project data sources you just created to the two packages

that you have developed.

1. If necessary, start SQL Server Business Intelligence Development Studio (BIDS), open

the project you created in tutorial, SSIS Project, and then open Solution

Explorer. Edit your MyPackage.dtsx package by double-clicking the package in Solution

Explorer.

2. Locate the Connection Managers pane (at the bottom of the SSIS Designer window),

right-click in the pane, and then click New Connection From Data Source.

3. In the Select Data Source dialog box, select both the AdventureWorks2008 and

AdventureWorksDW2008 data sources from the list, and then click OK to accept. This

puts the two project data sources into the package’s Connection Managers pane.

4. Perform the same steps in the DimCustomer.dtsx package to add the Adventure-

Works2008 and AdventureWorksDW2008 project data sources as connection managers

for the package.

5. When you are finished creating the connection managers, click the Save All button on

the BIDS toolbar.

References

Developing SSIS Pacages - Create Project Data Sources

Slava Kokaev — Sun, 29 Nov 2009 12:23:21 GMT

In this tutorial, you will create two project data sources, which will be used in your packages

as the source and the destination.

1. If necessary, start SQL Server Business Intelligence Development Studio (BIDS), open

the project you created in tutorial, SSIS Project, and then open Solution

Explorer (if it is not already displayed). You can open Solution Explorer by clicking the

Solution Explorer button on the Standard toolbar.

2. In Solution Explorer, right-click the Data Sources folder, and then click New Data

Source. When the Welcome page of the Data Source Wizard appears, click Next.

3. On the Select How To Define The Connection page, select Create A Data Source Based

On An Existing Or New Connection.

4. Click New to open the Connection Manager dialog box.

5. In the Provider drop-down list, select the Native OLE DB\SQL Server Native Client 10

provider and click OK. Type (local) in the Server Name field.

6. In the Select Or Enter A Database Name drop-down list, select AdventureWorks2008.

7. Click the Test Connection button, and then click OK. Click OK again to close the Connection

Manager dialog box.

8. Select the (local).AdventureWorks2008 data connection in the Data Connections list,

and then click Finish in the Data Source Wizard.

9. The Completing The Wizard page prompts you to enter a name for the new project

data source. Type AdventureWorks2008 in the Data Source Name box, and then click

Finish. Be sure to remove the space between Adventure and Works2008.

10. Next, repeat steps 2 to 9 to create a new project data source for the (local).Adventure-

WorksDW2008 database, and name this data source AdventureWorksDW2008.

11. When you are finished creating the data sources, click the Save All button on the BIDS

toolbar.

References

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

Developing SSIS Packages - Create the Project and Packages

Slava Kokaev — Sun, 29 Nov 2009 12:07:33 GMT

In this tutorial, you will create a new SSIS project and then work with a couple of SSIS packages

by adding data sources and connections.

1. Start SQL Server Business Intelligence Development Studio (BIDS), by clicking the Start

button and then selecting All Programs, Microsoft SQL Server 2008, SQL Server Business

Intelligence Development Studio.

2. Choose New, Project from the File menu. (If you have Visual Studio 2008 installed

separately from BIDS, simply choose New Project from the File menu.) The New Project

dialog box displays all the installed templates for Microsoft Visual Studio, including the

Business Intelligence Projects templates.

3. In the New Project dialog box, confirm that Business Intelligence Projects is selected in

the Project Types area, and then in the Templates area, select the Integration Services

Project template.

4. Near the bottom of the New Project dialog box, in the Name box, type SSIS Project as the name of your SSIS project.

5. In the Location box, type the path, starting with the Documents folder in your user

profile: C:\SSISProject\. This is the same location where the practice exercise files for the Training Kit will be installed

by default.

6. Next, clear the Create Directory For Solution check box, which stores the SSIS project in

the folder you specified in step 5.

7. Click OK to have BIDS create the new SSIS project.

8. When the project is created, SSIS automatically creates a new SSIS package named

Package.dtsx and opens it in the SSIS Designer. In Solution Explorer, right-click

Package.dtsx, and then click Rename.

9. Rename the package by typing MyPackage.dtsx. BIDS might prompt you to rename

the package object. If a message box appears that prompts you to rename the package

object as well, click Yes. Always click Yes if you are prompted to change the package object

when renaming a package because this updates the internal name of the package.

10. Click the Save button on the toolbar, and then close the package by clicking the Close

button in the upper-right corner of the SSIS Designer.

11. To create a new package, right-click the SSIS Packages folder in Solution Explorer, and

then click New SSIS Package. This creates a new package object named Package1.dtsx

(the number depends on how many packages you have created) in the SSIS Packages

folder in Solution Explorer.

12. To rename the new package, right-click the package, and then click Rename. Rename

the package to DimCustomer.dtsx because this package will contain logic to process

the customer dimension table. When prompted, click Yes to rename the package object.

13. Following the same steps, create one more package in your SSIS Project named

DimPromotion.dtsx.

References

MCTS Self-Paced Training Kit (Exam 70-448): Microsoft® SQL Server® 2008 Business Intelligence Development and Maintenance (Self-Paced Training Kits)

Boston BI User Group Kicked Off

Slava Kokaev — Wed, 18 Nov 2009 19:31:20 GMT

I want to say thanks to our Sponsors and Friends Jim O'Neil from Microsoft and Brian Knight, Thomas Duclos and Tim Moolic from Pragmatic Works for helping us to organize and bring to live this and upcoming events.
In December 1st meeting we are planing to have Brien Knight On-Line from Florida. He will give us an introduction to SSIS and answer some questions.

Tech Valley Code Camp, Albany, NY, November 7th

Slava Kokaev — Mon, 02 Nov 2009 11:13:55 GMT

I am going to be speaking at The Tech Valley Code Camp in Albany, NY this month. I am going to be talking about Business Analysis with SQL Server Analysis Services 2008 (SSAS). This event is taking place on Saturday, November 7th 2009 at University of Albany. If you want to know more about it or are thinking of coming then head over to Tech Valley Code Camp