There are lots of ways to get data into databases - do we really need another? I believe the answer is "yes" if the alternative can do three important things:
xSkrape addresses each of these points:
xSkrape for SQL Server is similar to other SQL products such as Microsoft's geospatial libraries that add functionality through CLR functions. Our library is rather tiny - mainly because its heavy-lifting is off-loaded to an application layer. This has the benefit of not requiring you to bloat your databases with dozens of assemblies.
There are two ways to use xSkrape for SQL Server: use our copy of the application layer (xSkrape-hosted), or install your own copy of the application layer (self-hosted). Both hosting approaches have different billing methods, but both billing methods share the concept of using xSkrape credits. These credits are associated with your free xSkrape.com on-line account.
Why use credits? This offers you more flexibility: start by using xSkrape-hosting and switch over to self-hosting at your leisure without losing anything - there are many variations of what you can do with credits. In fact, we award 250 free credits every month, making xSkrape-hosting effectively free for low-to-moderate usage. If you elect to self-host, every SQL machine that uses the xSkrape libraries is charged 50 credits for the first month of use, and 300 credits every month after that. This means self-hosting can be free, too, for the first month of familiarization. Credit packs can be purchased on-line, as needed.
You can find your 36-character client keys on the My Account - Queries and Keys page:
There are pros and cons for each hosting approach. For self-hosting:
Some organizations might prefer not to use CLR libraries based on policy. Does this need to disqualify xSkrape? You could alternatively identify one or more dedicated "ETL databases" where an exception is made for xSkrape. You can also use xSkrape .NET components as found on NuGet. (xSkrape-hosted and self-hosted.) Watch for more .NET tools that can be used from SSIS that leverage xSkrape technology.
Furthermore, changes coming in SQL 2017 will have CLR assemblies facing a higher security hurdle, as noted here. The important take-away is: "Microsoft recommends that all assemblies be signed by a certificate or asymmetric key with a corresponding login that has been granted UNSAFE ASSEMBLY permission in the master database." We do sign our two deployed assemblies with certificates, meaning you can follow this best practice under SQL 2017. (The need for "UNSAFE" for xSkrape comes from the fact it does access external resources, by design.)
Communication with xskrape.com in the xSkrape-hosted scenario uses SSL. By default, self-hosting will only use SSL if port 443 is selected and SSL is already set up on 443 - but you can manually configure any installed host site / port to support SSL. After https has been added as a valid binding, simply change http://hostname:port to https://hostname:port.
You've got a few options when it comes to securing your client keys:
As far as the key itself and having to remember it each time you want to generate an object: we offer a "vault" to store it in, protected by a password of your choice. To use this feature, select "Manage Your xSkrape.com Account", supply the client key you wish to encrypt, and a password to encrypt it with:
We store the key on your machine in encrypted form, so your password is used in the code generator in place of needing to supply your client key. Note: when you generate code with the option to include your client key in the generated object, it will be exposed there regardless of whether it was vaulted or not. (We recommend using the option to not embed your key in generated objects!)
After you've run the "enable" tool on a database, you can test it right away:
Next, use our examples! The intention is if you click on these links:
... you should arrive at parameters that can generate a fully-working object. Click "Generate" to create the script for a SQL wrapper:
... then click "Execute" to commit the object to the database. After that, click "Test" to try it out. If we could retrieve tabular data in the test, we'll show it in a grid to preview the result.
A typical follow-up step would be to parameterize our generated object. We might for example generate the following object using the URL "https://www.quandl.com/api/v1/datasets/WIKI/MSFT.csv?sort_order=desc&collapse=daily&trim_start=2017-10-01":
This is fine if we wanted the object to always get data for the MSFT stock symbol, but we might adjust the object to accept any symbol:
Now we could use it in some larger query like this:
Q: Can I have more than one application host with self-hosting?
A: Technically, yes. You'll need to embed the host URL in your objects (currently), so whether you do that dynamically or statically is up to you. For billing, consider this example topology:
Are you billed based on the count of machines along the bottom? Actually, no: it'd be based on the lines that connect to your application hosts. (It just happens with one host, the count matches the number of connecting SQL machines.)
Q: How can I get in touch with questions, suggestions, etc.?
A: Drop us a line on our contact page.
Q: Are there any limitations on usage?
A: Our license agreement terms outline any conditions we have.
Q: Where can I find more examples?