Objections to Power Shell for Data Integration

In our previous post, we discussed why we recommend that you use PowerShell for data integration.  If you’re a data professional, you may have some objections.  (Indeed, if you’re not a technical person, you should probably skip this post).

First, PowerShell isn’t great for data transformation.

For example, if you’ve used SSIS or a similar tool, you may be used to mapping data in your integration tool. That, indeed, is how the ETL – Extract/Transform/Load integration model works.  You take data from source 1 (extract), you transform it in your tool, and you push it into source 2 (load).

And indeed, we don’t recommend this paradigm.  Instead, we prefer ELT– Extract/Load/Transform.

Which means all PowerShell is doing is pulling the data and pushing into another database. Once the data is loaded, we then use SQL to transform it for its final use.

The point – we don’t do transformations in intermediate tools.  We do it in the database source or in the database target.

Speed may be your next objection.

Purpose built tools can be faster than PowerShell. 

But as our customer, you’re a solid mid-market company our customer with revenue anywhere from 50 to 500 million dollars.  And customers like you don’t have “big data”. It’s rare that any of your integrations process more than 100,000 records at a time. 

A decent server can process this quantity of data within minutes.  And indeed, in the world of Rest APIs, the speed constraint is most often the API, not PowerShell. And again, as a mid-market company, you’re not looking for instantaneous, 7×24 performance.   Ease of support and training are much more important.

if you do have tens of millions of records to load at a time or you need instantaneous updates, we’ll agree that PowerShell is not the answer. And we’ll admit that we’re not the right partner for you.

Why PowerShell for Data Integration

As there are many specific tools for data integration (Boomi, SSIS, informatica), you we think you should choose PowerShell.  Whether your data source is an Excel spreadsheet, a text file, a Rest APIs, or a software specific reporting tool, PowerShell should be the glue that binds them all together.  There are two reasons we think you should choose this direction.

First, if you run Windows, you have PowerShell.  Yes, we’ll almost certainly have to install some new modules.  But you have it.  And there’s no easier investment than no investment.

Second, you can find people to support and maintain what we build together. Which means you won’t become dependent on us.  We certainly hope that you become a long-term client.  But we don’t believe in consultant dependency syndrome.   You should work with us because you want to, not because you’re afraid of firing us.

Third, there are lots and lots of resources available to learn about PowerShell.  With their MVP program, Microsoft has done a wonderful job encouraging expert content creation.   Blogs, videos, and conferences provide multiple ways to learn about PowerShell. And of course, we’re happy to train.

That said, we know that PowerShell is not THE answer to every data integration question. We’ll discuss some objections in our next post.

Moving Data From Excel to SQL Server with PowerShell (Relatively Painlessly)

Last fall, my attendance at the SQL Summit was entirely justified by a presentation by Aaron Nelson, a SQL Server MVP on Excel, PowerShell and SQL Server.

Unfortunately, I regularly suffer from having Excel as a data source. In an ideal world, this wouldn’t happen.

Logically, no one would buy a SaaS solution without first figuring out how the data in that system would be accessed and combined with all the other data your organization needs.

Unfortunately, things aren’t always logical.

Read more…

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult