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:
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:
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.
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.
Microsoft SharePoint has taken the enterprise by storm. A staggering number of corporations today are using SharePoint as their collaborative and information backbone. A tremendous amount of information is being stored within SharePoint lists.
As such, it’s becoming more and more important to be able to quickly and easily extract the data in those lists and bring it into other platforms for analysis, reporting and storage. SQL Server Integration Services seems like the ideal tool for moving and transforming this type of data around, but accessing data in SharePoint lists is not a trivial task.
Unless of course you have Task Factory. Task Factory is a suite of SSIS components from Pragmatic Works. In this video, we’ll look at the SharePoint Source component.
Have you ever been in a situation where you were stuck for an answer? I mean really stuck? I sure as heck have been. Being in a spot where I really needed an experts opinion, needed to be able to get some one on one help with a problem. Unfortunately, there never seemed to be anyone else around, someone who could take the dedicated time to help with my issue.
When I came to work for Pragmatic Works, I found out about this cool program we have called Virtual Mentoring. The concept is pretty simple. You buy a block of hours from one of our sales guys. Don’t ask me how much it costs, I try to stay out of the money side of things. You can contact one of them if you are interested (http://pragmaticworks.com/services/Consulting/VM/Default.aspx).
So once you get the hours you call up and get some time scheduled with one of our consultants. Our support folks pair up the type of problem with the right person. You answer this short questionnaire that helps describe the issue, and how long you think you’ll need.
On the appointed time you and the mentor join a virtual meeting space, such as GoTo Meeting or Windows Live Meeting (it’s setup ahead of time) and away you go. You can share desktops, and have a good conversation about the issue.
I’ve done several of these acting as a mentor, and it’s really cool. Most times it’s one on one, but one time I did speak with an entire team.
For some reason our mentoring program doesn’t seem particularly well known, so I thought I’d take a moment to pass along the info. I sure wish I’d known about this years ago!
For any ETL developer, updating dimensional data is the heart of what you do. Using out of the box SSIS components, however, is an unattractive proposition. You either had to use the built in SCD wizard, or use the "roll your own" approach. As any veteran BI developer knows, the SCD wizard isn’t the best in the world, primarily due to it’s reliance on the OLEDB Command task. "Roll your own", in other words handling all the logic yourself, works, but is time consuming to develop and often confusing to maintain.
A far, far better solution for handling Kimball SCD is to use the Task Factory SSIS Dimension Merge Slowly Changing Dimension transform. While it’s name is rather long winded, it’s definitely worth the breath.
In this video we’ll take a look at the Dimension Merge Slowly Changing Dimension in action.
Task Factory is a suite of SSIS components available from Pragmatic Works. In this video we’ll look at the Update Batch Transform.
Updating data can be a real pain. You either have to setup a special staging table in your database, then update from it, or use the slow OLEDB command. In this video we’ll look at a better solution, Task Factory’s SSIS Update Batch transfrom.
Task Factory is a suite of SSIS components available from Pragmatic Works. In this video we’ll look at the Data Validation Transform.
In past videos we looked at using the various Data cleansing transforms to clean up data coming into our SSIS package. What if you didn’t want to clean that data? Instead, you may just want to validate the data, then take some action based on that validity. To accomplish that, we can use the Task Factory SSIS Data Validation transform from Pragmatic Works.
In past videos we’ve looked at the various Data Cleansing Transforms available in Task Factory. We looked at the case transform, and saw how it could handle correcting capitalization errors. We saw how the Trim Plus transform could not only be used to trim leading and trailing spaces from a column but trim specified characters or words too. Regular Expressions were also examined in the RegEx Transform video.
There is one Data Cleansing component in the Task Factory suite that we haven’t covered yet, and it’s the grand daddy of them all. It’s appropriately named the Task Factory SSIS Data Cleansing Transform, combining the power of all the other data cleansing transforms into a single component.
If you are a Regular Expression guru, you’ve probably wished you could use Regular Expressions within your SSIS Packages. With the Task Factory SSIS RegEx Replace Transform, part of the Task Factory suite of SSIS components from Pragmatic Works, you can do just that. Let’s see how…
Of all the components in Task Factory, by far the coolest is the E-Mail Source. Using this nifty component you can actually pull your data into SSIS from an E-Mail! In this video you can see the Pragmatic Works tool in all it’s glory. Then you can see that the Task Factory SSIS E-Mail Source, like bowties, is cool.
When working with data, I often need to delete rows, but only rows for a specific set of keys I’m working with. The Task Factory SSIS Delete Batch Transform, from Pragmatic Works makes this easy. In this video, we’ll make like a barbarian king and hack and slash our way through data with ease.
When extracting data, you don’t always a natural key handy. Sometimes the nature of the data negates any built in key, or perhaps there’s just not one present. For these situations the Task Factory SSIS Surrogate Key Transform, part of the Task Factory suite from Pragmatic Works, will meet your needs.
At it’s core it’s very simple, just add it to your data flow, provide a column name, seed value and increment value, and away you go. It’s almost so simple you don’t need a video, but our series wouldn’t be complete without including this little gem.
In previous videos, we’ve looked at some of the other data cleansing operations Pragmatic Works’ Task Factory offers, such as address parsing and case correction. Another common data cleansing task is trimming spaces or characters from the start and end of text columns. In this video, we’ll do some data cleansing with Task Factory’s Trim Plus transform.
XML has become the defacto standard for information exchange across the web. The Task Factory SSIS XML Destination, one of the many components found in Pragmatic Works’ Task Factory suite, makes it incredibly easy to create XML files from your SQL Server Integration Services packages. In this video, you will see just how simple it is to generate easy to read XML files.
Inserting and updating records is, without a doubt, the most common operation performed in any SQL Server Integration Services package. What a headache though, having to determine if the record exists, whether to insert it, stage it, etc.
All that goes away with the Task Factory SSIS Upsert Destination component, part of the Task Factory suite from Pragmatic Works. With one simple control you can handle both inserts and updates with ease. This video shows you how incredibly easy it is.