Master Data Management 101: Standardize Units of Measure

Introduction

 

   There’s quite a bit of hoopla about MDM lately, mostly due to awareness. While “Lacks needed data” was cited as the number 1 problem (with 21 votes) in data warehouses in the recent IBM Data Warehousing Satisfaction Survey (2007), “Insufficient or inadequate master data” made a decent showing at second with 17 votes (see Table 3).

 

   The new Microsoft MDM site is a good place to start learning about Master Data Management and Microsoft’s efforts in this area.

   Master Data is a pretty common thing for engineers. I learned about it way back in my manufacturing engineering days.

 

The Issue

 

   Consider this scenario: Conglomerate C (CC) makes widgets and starts acquiring businesses that also make widgets. CC sells widgets by the pound, but Acquisition A (AA) measures them by counting individual widgets, while Acquisition B (AB) sells them by the case (gross, or 144 ea).

 

   CC now wants all this data in a data warehouse so they can compare apples to apples and know, among other things, how many widgets they’re actually making and selling on a given day.

 

Note: Instrumentation and measurement are scientific disciplines in their own rite. There’s a lot more to this, which I hope to cover here and in my new Applied MDM blog.

 

   The Unit of Measure in the existing database, dbCC, is pounds. The Widgets tables from the three companies look like this:

 

dbCC.dbo.Widgets

ID

Date

Weight

1

1/1/2007

2076

2

1/2/2007

2100

3

1/3/2007

1977

 

dbAA.Product.Widgets

ProductID

Date

Count

F0932E13-218D-458A-BE09-3286AFDE0280

1 Jan 2007

10,265

F68BF7AC-553E-4A32-B1CB-442DD310194C

2 Jan 2007

13,009

8C0C7511-1386-4C13-84B8-2351248280E6

3 Jan 2007

17,121

 

dbAB.dbo.Widgets

ID

Date

Cases

1

20070101

84

2

20070102

82

3

20070103

99

 

 

Standards, Standards, Standards

 

   One facet of MDM is standardizing this data. The keys to standardizing this data are recognizing traits in the data types, along with the quality of any proposed conversion.

   For example, the Cases to Count ratio is most likely stable and predictable. Conversion is easily accomplished using multiplication (or division, depending on which way you go in the standardization). Quality will be high.

   But the weight to count (individual or case count) conversion is going to be impacted by other factors. Most notably, do all widgets weigh the same? If not, what’s the acceptable tolerance? Quality will be adversely affected by tolerance.

 

   Dimensional analysis (the multiplication or division you do to convert known quantities) is also a question about measurement granularity. Trust me: You will want to store as fine a grain as possible.

    Looking at the sample data, you will want to store WidgetCount somewhere. dbAA is already in this format. Yay. dbAB is easy enough: dbAB.dbo.Widgets.Cases * 144 gives you WidgetCount. Again, the math on widget Weight in the dbCC data becomes fuzzy. But our customer assures us each pound contains five widgets. Five widgets per pound is probably a high quality number. It’s much higher than, say, five hundred or five thousand per pound!

 

   “Fuzziness” will impact the integrity of your data. A couple important measures of data warehouse integrity are data accuracy and signal to noise (usually defined by the percentage of “unknowns” in the data).

   When I have encountered this scenario in the field, I have informed the customer of the dangers and begged them to collect better metrics at the WidgetWeight station for company CC.

 

Enter Stage Left…

 

   There are other issues in these examples: date and ID standardization. Dates are fairly straightforward. The IDs can be a little tricky. To standardize the IDs in this example I may consider a LocationID and ProductDateID (ID) composite key on the first pass.

 

   I might create a couple tables in the data warehouse staging database that look like this:

 

Staging.Products.Widget

LocationID

ID

Date

Count

1

1

1/1/2007

10380

1

2

1/2/2007

10500

1

3

1/3/2007

9885

2

1

1/1/2007

10,265

2

2

1/2/2007

13,009

2

3

1/3/2007

17,121

3

1

1/1/2007

12,096

3

2

1/2/2007

11,808

3

3

1/3/2007

14,256

Staging.Products.Location

LocationID

LocationDescription

1

dbCC

2

dbAA

3

dbAB

 

   I’ve assumed (based on customer feedback) I get 5 widgets / pound from dbCC, and I know the math for the rest. Here I’ve prepared Location data for a star schema via a LocationID and Location table. But I haven’t touched dates and IDs, they will need some work before reaching the destination data warehouse.

 

Just the Fact, Ma’am

 

   When I load the destination data warehouse, LocationID maps nicely to the Location dimension surrogate key, while I could simply make the column now labeled ID into a Date dimension surrogate key (removing the Date column from the fact table altogether because it’s now redundant because it’s now redundant).

   This three-column table then makes a decent Fact:

 

DW.Product.FactWidget
LocationIDDateIDCount
1110,380
1210,500
139,885
2110,265
2213,009
2317,121
3112,096
3211,808
3314,256

 

   There’s more to Master Data Management, but this is one type of business problem folks are trying to solve when they talk about MDM and how it ultimately relates to data warehouse quality and customer satisfaction.

 

:{> Andy

 

Technorati Tags:

Master Data Management 101: Standardize Units of Measure

Introduction

 

   There’s quite a bit of hoopla about MDM lately, mostly due to awareness. While “Lacks needed data” was cited as the number 1 problem (with 21 votes) in data warehouses in the recent IBM Data Warehousing Satisfaction Survey (2007), “Insufficient or inadequate master data” made a decent showing at second with 17 votes (see Table 3).

 

   The new Microsoft MDM site is a good place to start learning about Master Data Management and Microsoft’s efforts in this area.

   Master Data is a pretty common thing for engineers. I learned about it way back in my manufacturing engineering days.

 

The Issue

 

   Consider this scenario: Conglomerate C (CC) makes widgets and starts acquiring businesses that also make widgets. CC sells widgets by the pound, but Acquisition A (AA) measures them by counting individual widgets, while Acquisition B (AB) sells them by the case (gross, or 144 ea).

 

   CC now wants all this data in a data warehouse so they can compare apples to apples and know, among other things, how many widgets they’re actually making and selling on a given day.

 

Note: Instrumentation and measurement are scientific disciplines in their own rite. There’s a lot more to this, which I hope to cover here and in my new Applied MDM blog.

 

   The Unit of Measure in the existing database, dbCC, is pounds. The Widgets tables from the three companies look like this:

 

dbCC.dbo.Widgets

ID

Date

Weight

1

1/1/2007

2076

2

1/2/2007

2100

3

1/3/2007

1977

 

dbAA.Product.Widgets

ProductID

Date

Count

F0932E13-218D-458A-BE09-3286AFDE0280

1 Jan 2007

10,265

F68BF7AC-553E-4A32-B1CB-442DD310194C

2 Jan 2007

13,009

8C0C7511-1386-4C13-84B8-2351248280E6

3 Jan 2007

17,121

 

dbAB.dbo.Widgets

ID

Date

Cases

1

20070101

84

2

20070102

82

3

20070103

99

 

 

Standards, Standards, Standards

 

   One facet of MDM is standardizing this data. The keys to standardizing this data are recognizing traits in the data types, along with the quality of any proposed conversion.

   For example, the Cases to Count ratio is most likely stable and predictable. Conversion is easily accomplished using multiplication (or division, depending on which way you go in the standardization). Quality will be high.

   But the weight to count (individual or case count) conversion is going to be impacted by other factors. Most notably, do all widgets weigh the same? If not, what’s the acceptable tolerance? Quality will be adversely affected by tolerance.

 

   Dimensional analysis (the multiplication or division you do to convert known quantities) is also a question about measurement granularity. Trust me: You will want to store as fine a grain as possible.

    Looking at the sample data, you will want to store WidgetCount somewhere. dbAA is already in this format. Yay. dbAB is easy enough: dbAB.dbo.Widgets.Cases * 144 gives you WidgetCount. Again, the math on widget Weight in the dbCC data becomes fuzzy. But our customer assures us each pound contains five widgets. Five widgets per pound is probably a high quality number. It’s much higher than, say, five hundred or five thousand per pound!

 

   “Fuzziness” will impact the integrity of your data. A couple important measures of data warehouse integrity are data accuracy and signal to noise (usually defined by the percentage of “unknowns” in the data).

   When I have encountered this scenario in the field, I have informed the customer of the dangers and begged them to collect better metrics at the WidgetWeight station for company CC.

 

Enter Stage Left…

 

   There are other issues in these examples: date and ID standardization. Dates are fairly straightforward. The IDs can be a little tricky. To standardize the IDs in this example I may consider a LocationID and ProductDateID (ID) composite key on the first pass.

 

   I might create a couple tables in the data warehouse staging database that look like this:

 

Staging.Products.Widget

LocationID

ID

Date

Count

1

1

1/1/2007

10380

1

2

1/2/2007

10500

1

3

1/3/2007

9885

2

1

1/1/2007

10,265

2

2

1/2/2007

13,009

2

3

1/3/2007

17,121

3

1

1/1/2007

12,096

3

2

1/2/2007

11,808

3

3

1/3/2007

14,256

Staging.Products.Location

LocationID

LocationDescription

1

dbCC

2

dbAA

3

dbAB

 

   I’ve assumed (based on customer feedback) I get 5 widgets / pound from dbCC, and I know the math for the rest. Here I’ve prepared Location data for a star schema via a LocationID and Location table. But I haven’t touched dates and IDs, they will need some work before reaching the destination data warehouse.

 

Just the Fact, Ma’am

 

   When I load the destination data warehouse, LocationID maps nicely to the Location dimension surrogate key, while I could simply make the column now labeled ID into a Date dimension surrogate key (removing the Date column from the fact table altogether because it’s now redundant because it’s now redundant).

   This three-column table then makes a decent Fact:

 

DW.Product.FactWidget
LocationIDDateIDCount
1110,380
1210,500
139,885
2110,265
2213,009
2317,121
3112,096
3211,808
3314,256

 

   There’s more to Master Data Management, but this is one type of business problem folks are trying to solve when they talk about MDM and how it ultimately relates to data warehouse quality and customer satisfaction.

 

:{> Andy

 

Technorati Tags:

Master Data Management 101: Standardize Units of Measure

Introduction

 

   There’s quite a bit of hoopla about MDM lately, mostly due to awareness. While “Lacks needed data” was cited as the number 1 problem (with 21 votes) in data warehouses in the recent IBM Data Warehousing Satisfaction Survey (2007), “Insufficient or inadequate master data” made a decent showing at second with 17 votes (see Table 3).

 

   The new Microsoft MDM site is a good place to start learning about Master Data Management and Microsoft’s efforts in this area.

   Master Data is a pretty common thing for engineers. I learned about it way back in my manufacturing engineering days.

 

The Issue

 

   Consider this scenario: Conglomerate C (CC) makes widgets and starts acquiring businesses that also make widgets. CC sells widgets by the pound, but Acquisition A (AA) measures them by counting individual widgets, while Acquisition B (AB) sells them by the case (gross, or 144 ea).

 

   CC now wants all this data in a data warehouse so they can compare apples to apples and know, among other things, how many widgets they’re actually making and selling on a given day.

 

Note: Instrumentation and measurement are scientific disciplines in their own rite. There’s a lot more to this, which I hope to cover here and in my new Applied MDM blog.

 

   The Unit of Measure in the existing database, dbCC, is pounds. The Widgets tables from the three companies look like this:

 

dbCC.dbo.Widgets

ID

Date

Weight

1

1/1/2007

2076

2

1/2/2007

2100

3

1/3/2007

1977

 

dbAA.Product.Widgets

ProductID

Date

Count

F0932E13-218D-458A-BE09-3286AFDE0280

1 Jan 2007

10,265

F68BF7AC-553E-4A32-B1CB-442DD310194C

2 Jan 2007

13,009

8C0C7511-1386-4C13-84B8-2351248280E6

3 Jan 2007

17,121

 

dbAB.dbo.Widgets

ID

Date

Cases

1

20070101

84

2

20070102

82

3

20070103

99

 

 

Standards, Standards, Standards

 

   One facet of MDM is standardizing this data. The keys to standardizing this data are recognizing traits in the data types, along with the quality of any proposed conversion.

   For example, the Cases to Count ratio is most likely stable and predictable. Conversion is easily accomplished using multiplication (or division, depending on which way you go in the standardization). Quality will be high.

   But the weight to count (individual or case count) conversion is going to be impacted by other factors. Most notably, do all widgets weigh the same? If not, what’s the acceptable tolerance? Quality will be adversely affected by tolerance.

 

   Dimensional analysis (the multiplication or division you do to convert known quantities) is also a question about measurement granularity. Trust me: You will want to store as fine a grain as possible.

    Looking at the sample data, you will want to store WidgetCount somewhere. dbAA is already in this format. Yay. dbAB is easy enough: dbAB.dbo.Widgets.Cases * 144 gives you WidgetCount. Again, the math on widget Weight in the dbCC data becomes fuzzy. But our customer assures us each pound contains five widgets. Five widgets per pound is probably a high quality number. It’s much higher than, say, five hundred or five thousand per pound!

 

   “Fuzziness” will impact the integrity of your data. A couple important measures of data warehouse integrity are data accuracy and signal to noise (usually defined by the percentage of “unknowns” in the data).

   When I have encountered this scenario in the field, I have informed the customer of the dangers and begged them to collect better metrics at the WidgetWeight station for company CC.

 

Enter Stage Left…

 

   There are other issues in these examples: date and ID standardization. Dates are fairly straightforward. The IDs can be a little tricky. To standardize the IDs in this example I may consider a LocationID and ProductDateID (ID) composite key on the first pass.

 

   I might create a couple tables in the data warehouse staging database that look like this:

 

Staging.Products.Widget

LocationID

ID

Date

Count

1

1

1/1/2007

10380

1

2

1/2/2007

10500

1

3

1/3/2007

9885

2

1

1/1/2007

10,265

2

2

1/2/2007

13,009

2

3

1/3/2007

17,121

3

1

1/1/2007

12,096

3

2

1/2/2007

11,808

3

3

1/3/2007

14,256

Staging.Products.Location

LocationID

LocationDescription

1

dbCC

2

dbAA

3

dbAB

 

   I’ve assumed (based on customer feedback) I get 5 widgets / pound from dbCC, and I know the math for the rest. Here I’ve prepared Location data for a star schema via a LocationID and Location table. But I haven’t touched dates and IDs, they will need some work before reaching the destination data warehouse.

 

Just the Fact, Ma’am

 

   When I load the destination data warehouse, LocationID maps nicely to the Location dimension surrogate key, while I could simply make the column now labeled ID into a Date dimension surrogate key (removing the Date column from the fact table altogether because it’s now redundant because it’s now redundant).

   This three-column table then makes a decent Fact:

 

DW.Product.FactWidget
LocationIDDateIDCount
1110,380
1210,500
139,885
2110,265
2213,009
2317,121
3112,096
3211,808
3314,256

 

   There’s more to Master Data Management, but this is one type of business problem folks are trying to solve when they talk about MDM and how it ultimately relates to data warehouse quality and customer satisfaction.

 

:{> Andy

 

Technorati Tags:

Master Data Management 101: Standardize Units of Measure

Introduction

 

   There’s quite a bit of hoopla about MDM lately, mostly due to awareness. While “Lacks needed data” was cited as the number 1 problem (with 21 votes) in data warehouses in the recent IBM Data Warehousing Satisfaction Survey (2007), “Insufficient or inadequate master data” made a decent showing at second with 17 votes (see Table 3).

 

   The new Microsoft MDM site is a good place to start learning about Master Data Management and Microsoft’s efforts in this area.

   Master Data is a pretty common thing for engineers. I learned about it way back in my manufacturing engineering days.

 

The Issue

 

   Consider this scenario: Conglomerate C (CC) makes widgets and starts acquiring businesses that also make widgets. CC sells widgets by the pound, but Acquisition A (AA) measures them by counting individual widgets, while Acquisition B (AB) sells them by the case (gross, or 144 ea).

 

   CC now wants all this data in a data warehouse so they can compare apples to apples and know, among other things, how many widgets they’re actually making and selling on a given day.

 

Note: Instrumentation and measurement are scientific disciplines in their own rite. There’s a lot more to this, which I hope to cover here and in my new Applied MDM blog.

 

   The Unit of Measure in the existing database, dbCC, is pounds. The Widgets tables from the three companies look like this:

 

dbCC.dbo.Widgets

ID

Date

Weight

1

1/1/2007

2076

2

1/2/2007

2100

3

1/3/2007

1977

 

dbAA.Product.Widgets

ProductID

Date

Count

F0932E13-218D-458A-BE09-3286AFDE0280

1 Jan 2007

10,265

F68BF7AC-553E-4A32-B1CB-442DD310194C

2 Jan 2007

13,009

8C0C7511-1386-4C13-84B8-2351248280E6

3 Jan 2007

17,121

 

dbAB.dbo.Widgets

ID

Date

Cases

1

20070101

84

2

20070102

82

3

20070103

99

 

 

Standards, Standards, Standards

 

   One facet of MDM is standardizing this data. The keys to standardizing this data are recognizing traits in the data types, along with the quality of any proposed conversion.

   For example, the Cases to Count ratio is most likely stable and predictable. Conversion is easily accomplished using multiplication (or division, depending on which way you go in the standardization). Quality will be high.

   But the weight to count (individual or case count) conversion is going to be impacted by other factors. Most notably, do all widgets weigh the same? If not, what’s the acceptable tolerance? Quality will be adversely affected by tolerance.

 

   Dimensional analysis (the multiplication or division you do to convert known quantities) is also a question about measurement granularity. Trust me: You will want to store as fine a grain as possible.

    Looking at the sample data, you will want to store WidgetCount somewhere. dbAA is already in this format. Yay. dbAB is easy enough: dbAB.dbo.Widgets.Cases * 144 gives you WidgetCount. Again, the math on widget Weight in the dbCC data becomes fuzzy. But our customer assures us each pound contains five widgets. Five widgets per pound is probably a high quality number. It’s much higher than, say, five hundred or five thousand per pound!

 

   “Fuzziness” will impact the integrity of your data. A couple important measures of data warehouse integrity are data accuracy and signal to noise (usually defined by the percentage of “unknowns” in the data).

   When I have encountered this scenario in the field, I have informed the customer of the dangers and begged them to collect better metrics at the WidgetWeight station for company CC.

 

Enter Stage Left…

 

   There are other issues in these examples: date and ID standardization. Dates are fairly straightforward. The IDs can be a little tricky. To standardize the IDs in this example I may consider a LocationID and ProductDateID (ID) composite key on the first pass.

 

   I might create a couple tables in the data warehouse staging database that look like this:

 

Staging.Products.Widget

LocationID

ID

Date

Count

1

1

1/1/2007

10380

1

2

1/2/2007

10500

1

3

1/3/2007

9885

2

1

1/1/2007

10,265

2

2

1/2/2007

13,009

2

3

1/3/2007

17,121

3

1

1/1/2007

12,096

3

2

1/2/2007

11,808

3

3

1/3/2007

14,256

Staging.Products.Location

LocationID

LocationDescription

1

dbCC

2

dbAA

3

dbAB

 

   I’ve assumed (based on customer feedback) I get 5 widgets / pound from dbCC, and I know the math for the rest. Here I’ve prepared Location data for a star schema via a LocationID and Location table. But I haven’t touched dates and IDs, they will need some work before reaching the destination data warehouse.

 

Just the Fact, Ma’am

 

   When I load the destination data warehouse, LocationID maps nicely to the Location dimension surrogate key, while I could simply make the column now labeled ID into a Date dimension surrogate key (removing the Date column from the fact table altogether because it’s now redundant because it’s now redundant).

   This three-column table then makes a decent Fact:

 

DW.Product.FactWidget
LocationIDDateIDCount
1110,380
1210,500
139,885
2110,265
2213,009
2317,121
3112,096
3211,808
3314,256

 

   There’s more to Master Data Management, but this is one type of business problem folks are trying to solve when they talk about MDM and how it ultimately relates to data warehouse quality and customer satisfaction.

 

:{> Andy

 

Technorati Tags:

SSIS Design Pattern – Read a DataSet From Variable In a Script Task

Introduction

One of the things I appreciate about SSIS is the script elements. There is a lot of functionality built into the engine out of the box but I like the idea of having a Script Task on the Control Flow and a Script Component on the Data Flow just in case.

Last month I used a Script Task to build a more flexible FTP client class. Last week I re-used that code to perform a bunch of uploads. I had a list of files to upload in a dataset stored inside an SSIS Object variable. I could have pulled my FTP client Script Task into a ForEach loop and simply iterated the Object variable with the ADO Enumerator, but I decided against it for a couple reasons:

1. What fun would that be?
2. Seriously, I wanted to avoid the overhead of opening and closing the FTP connection once for each file. I wanted to open the connection, log in, send all the files in a list, then close the connection.

A Note About The ActiveX Script Task

Don’t use it.

Why? It has “I’m not going to be around much longer” written all over it. The only time I use this task is during DTS conversion.

Use the Script Task. .Net is fun. You’ll like it, I promise.

On To The Project!

Poking around the blogosphere, I found some good information and nifty code written by Jamie Thomson to get me started. Note: There are two types of SSIS developers, those who read Jamie Thomson’s blog and those who will.

Follow these steps to build an SSIS project that demonstrates how to read (or shred) a dataset in an SSIS Object variable from a Script Task.

First, create a new SSIS project. I called mine “DatasetVariableInScriptDemo”. When the project opens, drag an Execute SQL Task and Script Task onto the Control Flow canvas.

Double-click the Execute SQL Task to open the editor. Click the Connection property and define a new connection (<New connection…>) to your local AdventureWorks database. (Note: If you do not have the AdventureWorks sample databases installed, you can download them here.)

Set the SQLStatement property by clicking the ellipsis in the SQL Statement textbox and adding the following code to the “Enter SQL Query popup:

SELECT Top 10
ContactID
,Title
,FirstName
,LastName
FROM Person.Contact

This query returns ten rows from the Person.Contact table.

Set the ResultSet property to “Full result set”.

Click “Result Set” from the list on the left to map the result set properties to variables. Click the Add button and change the NewResultName text to “0”. Under Variable Name, click “<New variable…>” and define a new package-scoped, Object type variable named dsVar. Click the OK button to close the Add Variable dialog and create the variable.

Click the OK button to close the Execute SQL Task editor.

Connect the Execute SQL Task to the Script Task by dragging an Execute SQL Task precedence constraint (the green arrow visible when you click the Execute SQL Task) to the Script Task.

Double-click the Script Task to open the editor. Click Script from the list on the left to open the Script property page. Add dsVar to the ReadOnlyVariables property.

Click the Design Script button to open the Visual Studio for Applications editor.

Open the Project Explorer. Right-click the References logical folder and click Add Reference. Select System.XML and click the Add button, then click the OK button to add a System.XML reference.

Return to the script editor and replace the supplied code with the following:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Xml
Imports System.Data.OleDb

Public Class ScriptMain

Public Sub Main()

Dim oleDA As New OleDbDataAdapter
Dim dt As New DataTable
Dim col As DataColumn
Dim row As DataRow
Dim sMsg As String

oleDA.Fill(dt, Dts.Variables(“dsVar”).Value)

For Each row In dt.Rows
For Each col In dt.Columns
sMsg = sMsg & col.ColumnName & “: ” & _
row(col.Ordinal).ToString & vbCrLf
Next
MsgBox(sMsg)
sMsg = “”
Next

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

This script uses an OLEDbDataAdapter (oleDA) to fill a DataTable (dt) with the contents of the dsVar SSIS package variable, then iterates each row and column to build a string containing the data in the row. It then pops up a messagebox for each row displaying the row’s contents before moving to the next row.

Your code would replace the lines that build and display the message.

Conclusion

I like the flexibility offered here. Because of this approach I was able to accomplish my goal of building and re-using a more flexible FTP client in an SSIS Script Task.

Get the code!

:{> Andy

PS – The PASS Summit 2015 Call for Speakers closes at 9:00 PM PDT Sunday, 15 Mar 2015 (04:00 GMT 16 Mar 2015). There’s still time, but hurry!

Learn more:
Expert SSIS Training (Live, Online)
IESSIS1: Immersion Event on Learning SQL Server Integration Services (In-person)
Stairway to Biml
Stairway to Integration Services

SSIS2014DesignPatterns200

Technorati Tags:

Accessing Parent Package Variables In Child Packages… Without Configurations

If you’ve needed to close the information loop in an SSIS Parent-Child package architecture (also known as Master Package Design), Steve Fibich has a post is for you!

This is the neatest thing I’ve seen in a long time. Once a child package is called, the parent package variables are available for reference and update – without a single mapping operation. They’re just there.

I tried the following:

Create a Parent package with a Script Task and an Execute Package Task. Create a int variable named MyParentVar and accept the default value at 0. 

Configure the script to popup the value of MyParentVar using the code MsgBox(Dts.Variables(“MyParentVar”).Value.ToString)

Create a Child package and add an Execute SQL Task. Set up any connection manager for the Task and enter “Select 1 as One” in the SQL Statement property. Set the resultset property to Single Row. On the resultset page set ordinal 0 to MyParentVar. In the Execute SQL Task’s properties you have to set DelayValidation to clear the red X circle.

Configure the Execute Package task in the Parent to call the Child package.

Copy the Parent package script task and paste it below the Execute (child) Package task. Wire it up so you get a popup, then the call to the child, then another popup.

Now. Execute the child. Error – there’s not a variable named MyParentVar in the child.

Execute the Parent. Popup 1 says MyParentVar = 0, which it should. Ok that, the child runs and then Popup 2 says MyParentVar = 1. The child updated it – kind of like ByRef arguments in VB.

Steve is using this to pass Object Variables containing datasets back up the pipe. The implications of this little trick are astounding! Good work Steve!

:{> Andy

From my PASS presentations: Custom Test Condition Code: ETL Counts (Team Edition for Database Professionals)

I’d like to thank the people that attended my sessions at the 2007 PASS Summit in Denver this week! I had a blast and hope you all did as well.

The code will be available from the PASS website and on a DVD. For those in my sessions, I promised I would post the code here for my custom Counts test condition for Team Edition for Database Professionals. Free registration at andyleonardconsulting.com is required.

Extending Team Edition for Database Professionals with custom test conditions is fairly straightforward once you understand it. (Like everything else in life, Mr. Obvious…)

If you are completely new to coding and would like to build a custom test condition for database testing, fear not! The last part of Chapter 4 (entitled Testing the Database) of the Wrox book Professional Software Testing with Visual Studio 2005 Team System: Tools for Software Developers and Test Engineers is dedicated to a beginner’s walk-through of building your first custom test condition.

Yes I am promoting my new book. There’s a reason: it’s a good book.

:{> Andy

Technorati Tags: Database testing Team Edition for Database Professionals Custom test condition PASS

PASS Presentation – Team Edition for Database Professionals

I am honored and so excited to be presenting a couple sessions at the PASS Summit in Denver next month!

One presentation is entitled Applied Team Edition for Database Professionals. The focus is TEDP in action for database development. I cover a lot of the material in my Testing the Database chapter of the new Wrox book: Professional Software Testing with Visual Studio 2005 Team System: Tools for Software Developers and Test Engineers – due out the week before the PASS Summit. Included is a walk-through of how to create and integrate a custom Team Edition for Database Professionals Test Condition.

I hope to see you there!

:{> Andy

PASS Presentation – SSIS Development And Testing

I am honored and so excited to be presenting a couple sessions at the PASS Summit in Denver next month!

One presentation is entitled Integrate Your SSIS Development. The focus is on SSIS development and testing. I touch on continuous integration and test-first development – using Team Foundation Server for the CI and Team Edition for Database Professionals to test ETL. I use the data collected by ETL Instrumentation in the testing, so this presentation highlights a lot of the pieces of my integrated SSIS development methodology (and previews some upcoming books!).

I hope to see you there!

:{> Andy