Even though I haven't blogged in it yet, the topic of women in IT is dear to my heart. I've been working in IT in one form or another for almost 15 years, and have worked for all sorts of companies and all sorts of people. But with only one exception[1] every exceptional IT or project manager for whom I have worked has been female. I've also worked with many incredible women in highly technical roles - my good friend Lynn Langit leaps to mind here, but she's certainly not alone.
Anyone who knows me knows how important context is to me. If you don't know the context of a question, you'll never find the right answer. As Dr. Ivan Brady is so fond of saying, "context is practically everything when it comes to determining meaning." And in business intelligence projects, always focusing on the business context (as opposed to the technical context that most geeks love so much) is vital for the project's success.
I mention all this because different individual viewpoints are necessary to expand a business' cultural viewpoint - its context, if you will. If you only hire skinny men who love hip-hop, your company will think and act like an underweight male hip-hop fan as well. More viewpoints are a good thing.
But the female viewpoint is woefully underrepresented in the IT world today, much to our collective detriment.
Why do I mention this? The Configuresoft training session I'm attending this week[2] has over 50% female attendees. (And they're the ones asking the tough questions - I'm glad the trainer knows what he's talking about!) In all of my years of delivering IT training, I don't think I have ever seen a majority of female students.
No, I don't have any conclusion to reach, but I wanted to make the observation anyway. I hope that this is part of an overall trend and not just an anomaly, because an expanded context is good for everyone...
[1] Hi Jeff!
[2] Configuresoft is my employer, and I need to get up to speed on the inner workings of our flagship ECM product to me more effective on the amazing, new CIA product that is my primary responsibility.
Wednesday, September 19, 2007
SSIS in Sweden Part 2
I've already mentioned that I'm going to be presenting a two-day "SSIS Advanced Topics" seminar in Stockholm, Sweden next month, but now I'm going to be multitasking during my visit. I've been invited to speak at the Swedish SQL Server User Group[1] during their October 11th meeting. I'll be speaking about SSIS best practices, although given the 40-60 minute timeframe I'll have available I'm not sure how much depth we'll be able to cover. Odds are, I'll just end up running over, as per usual. ;-)
In any event, if you're going to be in Stockholm on the 11th of October, make sure you plan on attending the user group meeting - we'll have a lot of fun and can take as much time as you want with Q&A regardless of how much time is allotted for the session itself.
[1] No, I can't read anything on the web site.
In any event, if you're going to be in Stockholm on the 11th of October, make sure you plan on attending the user group meeting - we'll have a lot of fun and can take as much time as you want with Q&A regardless of how much time is allotted for the session itself.
[1] No, I can't read anything on the web site.
Monday, September 17, 2007
The Vulture Does Silverlight
I love to read The Register. It has a delightfully irreverent and decidedly anti-Microsoft take on the world of IT, and covers a broad swath of topics every day. I like to think of it as "keeping me honest," as their viewpoints are often quite different from my own, and I like to proactively fight my own biases when I can.
In any event, El Reg had an interesting article on Silverlight today, including portions of an interview with Scott Guthrie. Even though it's not technically deep at all, I enjoyed its skeptical take on how Silverlight stands up to competition from Adobe and Google. Check it out.
In any event, El Reg had an interesting article on Silverlight today, including portions of an interview with Scott Guthrie. Even though it's not technically deep at all, I enjoyed its skeptical take on how Silverlight stands up to competition from Adobe and Google. Check it out.
I wonder if I will have a room waiting...
I'm going to be in Washington, DC for most of the week this week, and booked my hotel this morning.[1] When my reservation was complete, and my confirmation was displayed, I was presented with these "Additional Actions" on the confirmation page:
Now I'm a database guy, and I love NULL as much as anyone else, but I must admit that my confidence that I'll have the correct room waiting for me is somewhat reduced. It's not nearly NULL, mind you, but still...
Yes, I called them. No, I didn't click on the "Need help?" link. ;-)
[1] I'm usually very aggressive about booking hotel rooms, rental cars and airfare as early as possible, but the hotel prices in DC were so obscene six weeks ago when I planned this trip that I thought I'd keep an eye on them to see if they went down at the last minute. I've been checking almost every day, and the prices I got this morning were the lowest I'd seen for the last six weeks...
Friday, September 14, 2007
Handling Delimited Fields
I'm working with a set of input data that has multiple logical values "encoded" within a single field for each record. The source system allows users to select multiple values from a list, and then crams all of these values in a single field in the underlying database table, with a multi-character delimiting string between them. This is (believe it or not) the first time I've had to deal with this in a production SSIS scenario, so I investigated several different approaches to splitting the values into multiple rows with one value per row.
The first approach involves loading the data from the source system into a table in the staging database without modifying the shape of the data, and then using a SQL Server table-valued function to split the delimited field during the process of loading the data from the staging database into the data warehouse. I did some searching for a decent "split" function online and found one here that did most of what I needed. The only significant thing that I added was support for a "key" field to be passed in and included in the return table, so that the records extracted from the delimited field could then be correlated easily with the data in the source record. Here's the function I ended up with:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeySplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[KeySplit]
GO
CREATE FUNCTION dbo.KeySplit
(
@Key NVARCHAR (4000)
,@String NVARCHAR (4000)
,@Delimiter NVARCHAR (10)
)
RETURNS @Results TABLE
(
KeyColumn NVARCHAR (4000)
,ItemColumn NVARCHAR (4000)
)
AS
BEGIN
DECLARE @CurrentItem VARCHAR(8000)
WHILE CHARINDEX (@Delimiter ,@String, 0) <> 0
BEGIN
SELECT
@CurrentItem = RTRIM (LTRIM (SUBSTRING (@String, 1, CHARINDEX (@Delimiter, @String, 0) -1))),
@String = RTRIM (LTRIM (SUBSTRING (@String, CHARINDEX (@Delimiter, @String, 0) + LEN (@Delimiter), LEN (@String))))
IF LEN(@CurrentItem) > 0
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @CurrentItem
END
IF LEN(@String) > 0 -- Record after final delimiter
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @String
RETURN
END
GO
I can then use it like this in the source query from to load the data warehouse:
SELECT UDF.[KeyColumn] AS [RecordKey]
,UDF.[ItemColumn] AS [ItemName]
,RFC.[DelimitedList] AS [RawItemList]
FROM dbo.RFC_DATA RFC
CROSS APPLY dbo.KeySplit (RFC.[ID], RFC.[DelimitedList], '!#!') UDF
This performs reasonably well (although as I mentioned above, I don't really have enough data to say if it performs well enough) and returns the data I need.
Next, I wanted to look at a way to perform the same "splitting by key" functionality in memory in the SSIS data flow. This was obviously a job for... the Script Component![1] I personally tend to shy away from using the Script Task and Script Component in my SSIS packages (largely because there is so rarely anything that I need done that cannot be done using the built-in tools) but this is a case that screams out for scripting. And SSIS makes this incredibly easy. Here's what I needed to do:
First, I updated the data flow I'd created to load the data into the staging database by adding a Multicast transformation, a Script Component transformation, a Row Count transformation and an OLE DB destination. The image below shows the finished data flow after everything has been configured and connected.
Next, I right-clicked on the Script Component and set up the inputs and outputs to include the columns I needed.
Next, I selected my output and set its SynchronousInputID property to None to mark this as an asynchronous transformation[2]. It's important to do this before going into Visual Studio for Applications (VSA) to write any code, because Visual Studio will put in place the method stub code appropriate for the component when you first launch VSA, and it's mildly annoying to have to change it later on.
Next, I added the Delimiter package variable (which I added earlier to my package with the data type string and the value of the delimiter in the data I needed to split) to the ReadOnlyVariables list for the Script Component.
I then clicked on the Design Script button to launch VSA, and updated the stub code with the splitting logic I needed.
As you can see, this is much cleaner in VB.NET than it is in T-SQL, since the requisite functionality is already included in the .NET System.String class, so we don't need to re-invent this particular wheel.
Finally, I executed the package and made sure everything ran as desired.
I also did a little testing in the database to verify that the two techniques produced identical results, which they did. As you can see from the final image above, there just isn't enough data at this point to reach any meaningful performance conclusions, but I'm now armed and ready with two tested techniques for once the production data is available.
[1] If you didn't read that to yourself in a superhero voice, please go back and re-read this sentence until you get the tone right.
[2] If you're not sure about the differences between synchronous and asynchronous components in SSIS data flow, check out this TechNet article, or better yet, this excellent book by former SSIS Group Program Manager Donald Farmer.
The first approach involves loading the data from the source system into a table in the staging database without modifying the shape of the data, and then using a SQL Server table-valued function to split the delimited field during the process of loading the data from the staging database into the data warehouse. I did some searching for a decent "split" function online and found one here that did most of what I needed. The only significant thing that I added was support for a "key" field to be passed in and included in the return table, so that the records extracted from the delimited field could then be correlated easily with the data in the source record. Here's the function I ended up with:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[KeySplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[KeySplit]
GO
CREATE FUNCTION dbo.KeySplit
(
@Key NVARCHAR (4000)
,@String NVARCHAR (4000)
,@Delimiter NVARCHAR (10)
)
RETURNS @Results TABLE
(
KeyColumn NVARCHAR (4000)
,ItemColumn NVARCHAR (4000)
)
AS
BEGIN
DECLARE @CurrentItem VARCHAR(8000)
WHILE CHARINDEX (@Delimiter ,@String, 0) <> 0
BEGIN
SELECT
@CurrentItem = RTRIM (LTRIM (SUBSTRING (@String, 1, CHARINDEX (@Delimiter, @String, 0) -1))),
@String = RTRIM (LTRIM (SUBSTRING (@String, CHARINDEX (@Delimiter, @String, 0) + LEN (@Delimiter), LEN (@String))))
IF LEN(@CurrentItem) > 0
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @CurrentItem
END
IF LEN(@String) > 0 -- Record after final delimiter
INSERT INTO @Results (KeyColumn, ItemColumn) SELECT @Key, @String
RETURN
END
GO
I can then use it like this in the source query from to load the data warehouse:
SELECT UDF.[KeyColumn] AS [RecordKey]
,UDF.[ItemColumn] AS [ItemName]
,RFC.[DelimitedList] AS [RawItemList]
FROM dbo.RFC_DATA RFC
CROSS APPLY dbo.KeySplit (RFC.[ID], RFC.[DelimitedList], '!#!') UDF
This performs reasonably well (although as I mentioned above, I don't really have enough data to say if it performs well enough) and returns the data I need.
Next, I wanted to look at a way to perform the same "splitting by key" functionality in memory in the SSIS data flow. This was obviously a job for... the Script Component![1] I personally tend to shy away from using the Script Task and Script Component in my SSIS packages (largely because there is so rarely anything that I need done that cannot be done using the built-in tools) but this is a case that screams out for scripting. And SSIS makes this incredibly easy. Here's what I needed to do:
First, I updated the data flow I'd created to load the data into the staging database by adding a Multicast transformation, a Script Component transformation, a Row Count transformation and an OLE DB destination. The image below shows the finished data flow after everything has been configured and connected.
Next, I right-clicked on the Script Component and set up the inputs and outputs to include the columns I needed.
Next, I selected my output and set its SynchronousInputID property to None to mark this as an asynchronous transformation[2]. It's important to do this before going into Visual Studio for Applications (VSA) to write any code, because Visual Studio will put in place the method stub code appropriate for the component when you first launch VSA, and it's mildly annoying to have to change it later on.
Next, I added the Delimiter package variable (which I added earlier to my package with the data type string and the value of the delimiter in the data I needed to split) to the ReadOnlyVariables list for the Script Component.
I then clicked on the Design Script button to launch VSA, and updated the stub code with the splitting logic I needed.
As you can see, this is much cleaner in VB.NET than it is in T-SQL, since the requisite functionality is already included in the .NET System.String class, so we don't need to re-invent this particular wheel.
Finally, I executed the package and made sure everything ran as desired.
I also did a little testing in the database to verify that the two techniques produced identical results, which they did. As you can see from the final image above, there just isn't enough data at this point to reach any meaningful performance conclusions, but I'm now armed and ready with two tested techniques for once the production data is available.
[1] If you didn't read that to yourself in a superhero voice, please go back and re-read this sentence until you get the tone right.
[2] If you're not sure about the differences between synchronous and asynchronous components in SSIS data flow, check out this TechNet article, or better yet, this excellent book by former SSIS Group Program Manager Donald Farmer.
Office 2007 Usability
I posted a long rant about usability not too long ago, and included a mild jab at the Office 2007 UI (which I personally love, but which others do not) at the end of it. Today I ran across this post from Jensen Harris (a member of the Office UI team) on the science of GUI design, and thought I should share it in the context of full disclosure and fairness:
http://blogs.msdn.com/jensenh/archive/2006/08/22/711808.aspx
Pretty cool, eh?
Now what we need is a mathematical proof that people who use keyboard shortcuts are not only more efficient but also inherently cooler than people who use the mouse. I wonder if Fitts did any work on that front...
http://blogs.msdn.com/jensenh/archive/2006/08/22/711808.aspx
Pretty cool, eh?
Now what we need is a mathematical proof that people who use keyboard shortcuts are not only more efficient but also inherently cooler than people who use the mouse. I wonder if Fitts did any work on that front...
Loading Multiple Excel Files with SSIS - Part Two
I got a comment yesterday (well, it was yesterday as I typed this post, but I'm not actually publishing it until the next day... damned air travel...) on my “Loading Multiple Excel Files with SSIS” post asking if it was possible to use the same technique to loop over multiple Excel workbook files with different worksheet names and still have a single package to do the work. The short answer is yes, but it depends on these workbooks that have different worksheet names to have identical worksheet structures, and that’s a pretty big if.
I’m personally skeptical that you’re going to run into this situation in the real world. From my experience, unless files come from a single source, there are almost always meaningful differences (although they may be small) between them. And if these hypothetical Excel workbook files come from a single source, they’re probably going to have the same name, right?
In any event, let’s work under the assumption that we have Excel workbook files with the same data but with a different worksheet name. The steps below will demonstrate how to update the sample package from my earlier post to work in this scenario. I’m not going to write all of the VB.NET code that will be required (partly because I’m writing this in the airport while waiting for my flight and don’t have the necessary components installed to make it work, and partly because this sounds like an ideal “exercise for the reader”) but I’ll cover everything else. Here’s what you need to do:
First, we need a new XLS file to serve as input. Copy one of the existing XLS files (I’m using the last post as the starting point, so if you didn’t follow those steps then, you’ll need to do it now in order to follow along) and then open the copy in Excel. Rename the first worksheet from Sheet1 to DifferentName. Save the file and close it.
Open the SSIS project created in the previous post in Visual Studio, and open the SSIS_Excel_Loop.dtsx package.
Add a new variable named WorksheetName of type string with the value “Sheet1$”
Edit the Excel data source in the data flow to use the “Table name or view name variable” Data access mode and select the WorksheetName variable from the drop-down list.
Click on the Preview button to verify that the data source still works.
Add a Script task to the control flow inside the Foreach Loop container and connect it to the Data Flow task with a success precedence constraint.
Right-click on the newly-added Script task and select Edit from the pop-up menu. On the Script tab within the Scrip Task Editor window, add WorksheetName to the ReadWriteVariables list and FileName to the ReadOnlyVariables list.
Click on the Design Script button to open the Visual Studio for Applications (VSA) development environment.
Edit the code to look like this:
I’m personally skeptical that you’re going to run into this situation in the real world. From my experience, unless files come from a single source, there are almost always meaningful differences (although they may be small) between them. And if these hypothetical Excel workbook files come from a single source, they’re probably going to have the same name, right?
In any event, let’s work under the assumption that we have Excel workbook files with the same data but with a different worksheet name. The steps below will demonstrate how to update the sample package from my earlier post to work in this scenario. I’m not going to write all of the VB.NET code that will be required (partly because I’m writing this in the airport while waiting for my flight and don’t have the necessary components installed to make it work, and partly because this sounds like an ideal “exercise for the reader”) but I’ll cover everything else. Here’s what you need to do:
First, we need a new XLS file to serve as input. Copy one of the existing XLS files (I’m using the last post as the starting point, so if you didn’t follow those steps then, you’ll need to do it now in order to follow along) and then open the copy in Excel. Rename the first worksheet from Sheet1 to DifferentName. Save the file and close it.
Open the SSIS project created in the previous post in Visual Studio, and open the SSIS_Excel_Loop.dtsx package.
Add a new variable named WorksheetName of type string with the value “Sheet1$”
Edit the Excel data source in the data flow to use the “Table name or view name variable” Data access mode and select the WorksheetName variable from the drop-down list.
Click on the Preview button to verify that the data source still works.
Add a Script task to the control flow inside the Foreach Loop container and connect it to the Data Flow task with a success precedence constraint.
Right-click on the newly-added Script task and select Edit from the pop-up menu. On the Script tab within the Scrip Task Editor window, add WorksheetName to the ReadWriteVariables list and FileName to the ReadOnlyVariables list.
Click on the Design Script button to open the Visual Studio for Applications (VSA) development environment.
Edit the code to look like this:
Public Sub Main()
Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables("FileName").Value)
If fileName.Contains("email_book_03.xls") Then
sheetName = "DifferentName$"
Else
sheetName = "Sheet1$"
End If
Dts.Variables("WorksheetName").Value = sheetName
Dts.TaskResult = Dts.Results.Success
End Sub
Dim sheetName As String
Dim fileName As String = CStr(Dts.Variables("FileName").Value)
If fileName.Contains("email_book_03.xls") Then
sheetName = "DifferentName$"
Else
sheetName = "Sheet1$"
End If
Dts.Variables("WorksheetName").Value = sheetName
Dts.TaskResult = Dts.Results.Success
End Sub
This is where you’ll need to do a little work on your own. Although this scenario will execute and work as desired for the sample files, it’s obviously not going to be sufficient in the real world. Instead, you’ll need to use the Excel object model, perhaps through a custom .NET assembly that wraps the COM Office DLLs, perhaps through Visual Studio Tools for Office (I’m not going to take the time to build the former and can’t download and install the latter without an internet connection) and use that API to retrieve the worksheet name of interest. This will probably be the first worksheet in the workbook, but your requirements may vary.
The key portions of the VB.NET code you’ll need to write for the Script task are that you read from the FileName package variable to know what XLS file you’re working with, and then write to the WorksheetName package variable to tell the rest of the package (specifically the Excel data source component in the data flow) the name of the sheet to use. Pretty simple, right?
Well, it works on paper, but as I mentioned early on I suspect that you’ll find that there are differences beyond the sheet name that you’ll need to handle. Because of this you’ll need to ensure that your package has robust error handling so that when these differences appear at runtime the package can handle them gracefully.
Good luck!
Postscript: I also see that while I was in the air yesterday I got a few additional comments posted from "Romain" on looping over the tabs in a workbook to load all of them instead of just the first tab. I'm afraid I cannot read the French solution to which he linked, but the same technique not listed in the code above is what you're going to need to do that. You'll need to loop through each Worksheet object in the Workbook object for the current XLS file, using the Excel object model to do so. Probably the easiest way to do this is to have a For Loop Container in your control flow contained within the existing Foreach Loop Container that loops through the multiple Excel files. Within the For Loop have a script task that fetches the name of the next sheet following the last-fetched "current" sheet (or the first sheet in the book on the first pass through the loop) and also sets a Boolean flag indicating that the sheet name being fetched is the name of the last sheet in the workbook. Configure the For Loop to exit when this flag variable is set to true.
Postscript: I also see that while I was in the air yesterday I got a few additional comments posted from "Romain" on looping over the tabs in a workbook to load all of them instead of just the first tab. I'm afraid I cannot read the French solution to which he linked, but the same technique not listed in the code above is what you're going to need to do that. You'll need to loop through each Worksheet object in the Workbook object for the current XLS file, using the Excel object model to do so. Probably the easiest way to do this is to have a For Loop Container in your control flow contained within the existing Foreach Loop Container that loops through the multiple Excel files. Within the For Loop have a script task that fetches the name of the next sheet following the last-fetched "current" sheet (or the first sheet in the book on the first pass through the loop) and also sets a Boolean flag indicating that the sheet name being fetched is the name of the last sheet in the workbook. Configure the For Loop to exit when this flag variable is set to true.
Again, good luck! I'm not going to have time to write the Excel sheet-looping code myself, but when you get it working I'd love to see it. ;-)
Subscribe to:
Posts (Atom)