Goodbye Pragmatic Works. Hello Pragmatic Works!

I wanted to share a new phase in my life. After a little over three and a half awesome years, October 10th will be my last day as a consult with Pragmatic Works. Beginning October 13th I will be going to work for… Pragmatic Works! (Confused? Hey you should be me.)

Most people don’t realize this, but Pragmatic Works is technically two companies in one. We have a consulting division, where I have worked for the last three and a half wonderful years. Beginning October 13th I will transition to working for the software division in the role of Product Evangelist.

In this role I will be spreading the word about our tools, as well as supplying additional training on our entire suite. You can begin to expect more posts from me that focus on our various tools, which include BIxPress, Task Factory, DOCxPress, and DBAxPress. I’ll still be in the community, perhaps even more so, giving people the opportunity to learn more about SQL Server in general, our tools and services in particular.

If you are going to the PASS Summit, be sure to look me up. I’m copresenting a precon titled Zero to Hero with PowerShell and SQL Server, as well as doing a regular session Make SQL Server Pop with PowerShell. I’ll also be spending a lot of time at the Pragmatic Works booth doing demos and the like. Would love to meet you, talk about your challenges around BI development, and how we could work to get many of them resolved.

So, Goodbye Pragmatic Works. Hello Pragmatic Works!

PowerScripting Podcast

I just wanted to give a thanks to the guys at the PowerScripting Podcast for having me on tonight. As soon as it is released I’ll follow up with a link.

For those who came here from hearing me on the podcast, you can find more info on SQL Saturday at: http://bit.ly/sqlsat328

If you want to find out more about my sessions at the PASS Summit, you can jump to http://bit.ly/acsummit. My co-presenters for the precon are Brad Ball @sqlballs and Jason Strate @stratesql.

The Pragmatic Works webinars can be found on the company website at http://pragmaticworks.com. Just follow the Free Training on the T’s to get access to the webinars. You can search by author name (Robert Cain will get you mine) or topic.

My other training videos can be found on Pluralsight, http://pluralsight.com/training.

I also have a youtube channel with a couple of videos, https://www.youtube.com/user/arcanecode. Check out the Column Mode Editing video for a quick editing tip on making your life easier with both PowerShell and SQL Server.

Creating a Data Warehouse Date Id in Task Factory Advanced Derived Column Transformation

The company I work for, Pragmatic Works, makes a great tool called Task Factory. It’s a set of transformations that plug into SQL Server Integration Services and provides a wealth of new controls you can use in your packages. One of these is the Advanced Derived Column Transformation. If you are familiar with the regular Derived Column transformation built into SSIS, you know that it can be painful to use if you have to create anything other than a very basic calculation. Every try typing something complex into that single row tiny little box? Egad.

The Task Factory Advanced Derived Column transform allows you to pop up a dialog and have true multi-line editing. In addition there are 180 addition functions to make your life easier. Which is actually the point of this whole post.

As a Business Intelligence developer, one of the things I have to do almost daily is convert a date data type to an integer. Most dates (at least in the US) are in Month / Day / Year format. Overseas the format is usually Day / Month / Year (which to me makes more sense). SQL Server Analysis Services loves integer based field, so a common practice is to store dates as an integer in YYYYMMDD format.

Converting a date to an integer using the derived column transform can be ugly. Here’s an example of a fairly common (although not the only) way to do it:


(DT_I4)((DT_WSTR,4)YEAR(MyDateColumn) + RIGHT("00" + (DT_WSTR,2)MONTH(MyDateColumn),2) + RIGHT("00" + (DT_WSTR,2)DAY(MyDateColumn),2))

Task Factory makes this much easier. There is a ToChar function which converts columns or values to characters. This function allows you to pass in a format to convert to. Wrap all that in a ToInteger function and away you go. Check this out:


ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))

Much, much simpler. One thing, the case of the format is very important. It must be yyyyMMdd, otherwise it won’t work. If you want to extend this more, you can actually check for a null, and if it is null return a –1 (a common Id for a missing row) or another special integer to indicate a missing value, such as 19000101.


IIf(IsNull(MyDateColumn)
   , -1
   , ToInteger(ToChar(MyDateColumn, "yyyyMMdd"))
   )

Here we first check to see if the column is null, if so we return the missing value, else we return the date converted integer. And yes, you can do multi line code inside the Advanced Derived Column Transformation.

As you can see the Advanced Derived Column Transformation makes working with dates much, much easier than the standard derived column transformation. This is such a common need that, at the risk of sounding like an ad, I decided to blog about it so I can share this with all my clients in the future.

(Just to be clear, it’s not an ad, I was not asked to do this, nor did I receive any money for it. Mostly I did this post just so I could share the syntax when I start each project or training class.)

Arcane 2011 Year in Review

Seems most folks I know are doing “Year in Review” posts for their blogs. Not wanting to miss a chance to do a blatant rip-off I thought I’d jump on the band wagon.

My MVP Award was renewed for 2011. This award is something I’m both proud and humbled to receive, especially in the SQL community. MVPs in general are a very helpful, friendly bunch, and the SQL especially so. I really like the new #SQLFamily hashtag that’s being used on Twitter. Aside from my family, I think becoming an MVP is the thing I’m most proud of in my life.

I was quite happy when my second co-authored book, SQL Server MVP Deep Dives Vol II came out. All proceeds went to charity, it felt great to help out both the children in Operation Smile and the SQL community.

I’ve continued my public speaking, being allowed to present at places such as the New York City Code Camp, SQL Rally in Orlando, and one of my favorites CodeStock.

I produced quite a bit of new training content for Pluralsight, to positive reviews. I’m glad this relationship is continuing, teaching is the best way to learn new things and I’ve learned a great deal, and hopefully helped a few others.

The most exciting news of my year though was going to work for Pragmatic Works! You could have knocked me over with a feather when, at the MVP Summit last year, Brian Knight (I should say THE Brian Knight) looks at me and says “So, you looking for a job?” When someone of Brian’s caliber asks if you want a job, you don’t say no.

Coming to work at Pragmatic Works was a great career move for me. We currently have five (yes, FIVE) MVPs working here. That’s a huge percentage considering the size of our company. And there’s quite a few of our guys that are working hard in the community, and I see an MVP award in their future. 

In addition to the regular consulting you might expect I’ve also gotten to do a lot of training. I love doing training, its so gratifying to share knowledge, guide students, see that look of “ah-ha” on their faces when it all clicks for them. I think that has been my favorite part of the job so far.

As a company Pragmatic Works is doing very well, entering into several new, exciting partnerships. Plus, they keep increasing our benefits! In a time when other companies are cutting costs, they have given us three new benefits!

With this level of technical expertise, it keeps me challenged. It also inspires me toward even more professional achievements.

On the home front, I now have a teenager living in my home. My sweet little baby girl turned 13 this year! Holy cow, how did that happen? I mean, last time I checked I was only 17 myself. And her sister is not far behind.

They had a great year, we home school and they got involved in a science program at Auburn University where they learn scientific techniques. They did some forensic investigations, and are now learning how to do proper experiments, clearly defining the various steps. They get all this brain power from their teacher / mother, my wife is the real brains in the family.

They also took up new extra curricular activities. My youngest, Anna, learned to ice skate and even did her first skating show just before Christmas. My older daughter, Raven, now plays the guitar and has given her first recital. My home office is under her bedroom, and I often get to enjoy the sounds of her practicing over my head.

And of course, last but not least, I have to give a special shout out to my lovely wife of 15 years, Ammie. She has been very supportive of my career, even though with the new work related travel it has meant more work for her. I know I’m very lucky to have a wife this supportive. Thanks sweetheart!

With that, another year bites the dust. 2012 is shaping up to be an exciting year. Assuming of course the world doesn’t end. But if it did, I suppose that would be exciting too, so either way it’s a win, excitement wise.

The Great Pragmatic Works Task Factory Round Up

Over the last month or so, I’ve been blogging about my companies (Pragmatic Works) cool suite of SSIS (SQL Server Integration Services) add in tools called Task Factory. I did this mostly to learn about the various components, and decided to video the whole thing in order to a) help myself remember and b) share the knowledge with everyone.

I’m about to move onto some of our other tools, and as I explore and learn them will be adding videos as I go. I wanted to wrap up the Task Factory series with a summary of all the tools, and links to the blog post and the direct link to the video. Please note I’ve listed these alphabetically, to make them easy to find, instead of the order in which they were published. 

Task Factory Address Parse video | blog

Task Factory Advanced E-Mail and SMS Task video | blog

Task Factory Case Transform video | blog

Task Factory Compression video | blog

Task Factory Data Cleansing video | blog

Task Factory Data Validation video | blog

Task Factory Delete Batch Transform video | blog

Task Factory Dimension Merge Slowly Changing Dimension video | blog

Task Factory Email Source video | blog

Task Factory File Properties video | blog

Task Factory Null Handler blog

Task Factory Replace Unwanted Characters video | blog

Task Factory RegEx video | blog

Task Factory SalesForce.com Source and Destination blog

Task Factory SharePoint Destination video | blog

Task Factory SharePoint Source video | blog

Task Factory Surrogate Key transform video | blog

Task Factory Terminator Destination blog

Task Factory Trim Plus video | blog

Task Factory Update Batch transform video | blog

Task Factory Upsert Destination video | blog

Task Factory XML Destination video | blog

Task Factory–SalesForce.com Source and Destination

Over the course of the last month I have created video tutorials on the components that ship with Pragmatic Works Task Factory suite of SSIS components. There are two though, that unfortunately I’m not able to cover, but I did want to make sure everyone was aware of. Those are the SalesForce.com Source and Destination components.

These are quite similar to the SharePoint Source and Destination components I covered in previous posts. Here’s a screen shot of the Source properties window:

 

SNAGHTML6a6b6c3

 

As with most of the tools in Task Factory, you simply pick the connection manager, pick the object to connect to, supply and parameters, and pick what you need. Just that simple and straight forward.

The Destination component is just as easy, as you can see:

SNAGHTML6aafcf6

Again, simply create a connection, pick the target object, map the columns and away you go.

Since I don’t currently have a way to communicate with a SalesForce.com environment, as of this writing we’ll have to settle for a few screen shots. I believe though they will convey the simplicity of the product. Most importantly though you are now aware of these handy components, something you’ll find invaluable if you deal with SalesForce.com.

Task Factory–SharePoint Destination

In a previous video we looked at the SharePoint Source component, one of the many components in Pragmatic Works Task Factory suite of SSIS tools. We saw how easy it was to extract data out of a SharePoint list.

What though, do we do if we need to push data into a SharePoint list? It turns out it’s every bit as easy, using Task Factory’s SharePoint Destination component.

Follow

Get every new post delivered to your Inbox.

Join 104 other followers