SQL SERVER – Cloud Based Data Integration Made Easy – A Real World Scenario

SQL
5 Comments

If you are a DBA, once in a while, you will have a situation where you end up with some tasks which will be boring and annoying. Trust me in my life, I often come across similar scenarios quite often. Here is one such tasks I came across a few days ago. Let us learn about cloud based data integration.

A Task, I would like to Automate

Just the other day I was assigned a task where I had to take a CSV file from my network and insert into SQL Server which was hosted in remote locations. Well, you may think it as a pretty easy task and I agree with you this is very easy task. The challenge is not about its difficulty, but the challenge was rather about the entire process and my interest. The part which annoyed me the most was that I have to do this every day at 4 PM.

This means, every day I must be at my desk at 4 PM and take a file from the network and upload to remote SQL Server. What about weekends? What about when I have to step away from my desk at 4 PM? What about the situation, when I am busy doing something much more important than this task? Well, as I said, more than task, I have been just one-place with the routine which was associated with it. In simple words, this was an ETL task which needed to be automated, but I can’t depend on my machine always. I have to find a solution which was cloud based and runs efficiently.

Skyvia at Rescue

I was sitting miffed in office and suddenly I remembered that last year I blogged about the tool Skyvia. Here is the blog post Integrate Your Data with Skyvia – Cloud ETL Solution. I quickly referred to my own blog, post and realized I should give Skyvia a try.

What is Skyvia?

Skyvia is a powerful solution for cloud data integration (ETL) and backup. It is a complete online solution and does not require any local software installed except for a web browser. In Skyvia we can create integration packages that define the operations and then we can run them or schedule for automatic execution. An integrated package is a set of data ETL operations that can be stored for future use, executed, modified, or deleted. Skyvia provides several kinds of packages for different integration scenarios. They are Import, Export, Replication, and Synchronization packages.

How did I do it?

Well, here are few of the screenshots of the task which I was assigned.

First, I checked if the table where I have to export data exists or not. As the table was already created, I quickly checked if it contained data or not. The table contained no data.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 1

Next we will open the Skyvia web interface. It is pretty simple and it will list three options on the left bar. We will click Integration there.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 2

Solarwinds

In the Integration section, click Create Now under Data Import.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 3

In the data integration screen we will be presented with various options. We will load the CSV file from an FTP server, so we select source as a CSV from FTP and target as SQL Server.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 4

As we will be connecting SQL Server for the very first time we will be creating new connection and that is pretty straight forward procedure.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 5

Then we will configure an FTP connection

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 6

Next we will configure CSV options. Here will be providing various options, but in our case all the default options were good enough for us to move next.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 7

Right after that we will select the target table. In our case the target table is actor table.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 8

The next screen will present mapping and we will one more time review various mapping options. We will make sure that all the source and target columns maps correctly.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 9

When we click finish it will bring up the following screen.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 10

Click on Save and now we are back on the following screen. Over here we can execute our task and see if it works or not. Click on the RUN button on the right side of the screen.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 11

In my case the task ran successfully and it shows that it has inserted 200 rows successfully. The time taken to complete this entire task was 35 seconds and it depends on my network connection to the destination server.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 12

We can execute the same select statement which we had executed earlier and see if the table contains the valid data.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 13

Once we commit that our task has worked successfully, we can create a daily schedule.

SQL SERVER - Cloud Based Data Integration Made Easy - A Real World Scenario 14

That’s it! I am done.

Now every day at specific time the task will automatically execute and will log history.

Next Action Items

Team Devart has created Skyvia a feature rich service. One has to experiment with various different options to fully see the vast capability of this amazing product. Here are few things you can consider doing it. Here is the link where you can sign up for Skyvia for totally FREE. Next I will be trying out Skyvia with Salesforce. Skyvia is an all-in-one cloud solution for various Salesforce data integration scenarios. In addition to the standard Salesforce data loader functionality – data import and data export – it offers powerful data replication and synchronization tools and backup functionality with preview and export of backed up data, data search, viewing data changes between backups, and one-click restore.

Sign up for Skyvia for totally FREE.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Installation Error – INSTALLSHAREDDIR parameter is not valid because this directory is compressed or is in a compressed directory
Next Post
SQL SERVER – Notes and Observations on ReadOnly Databases in SQL Server

Related Posts

5 Comments. Leave new

  • Nice to know about SkyVia, however I just wanted to let you know that this requirement can be done through SSIS package and scheduling the same in SQL Job.

    Reply
    • Hi Praveen,

      I agree, however Skyvia is much more advanced and it can connect MySQL, PostgreSQL, Dropbox and many other database. Currently my requirement is to move data to SQL Server but I know very soon, we will be doing the same task with other database and Skyvia is amazing in doing such complicated cross platforms.

      Thanks for reading the blog post Praveen – do let me know your feedback once you try Skyvia.

      Reply
  • What is User ID and password supposed to be if I’m on a local connection using Windows authentication? I’m not using any kind of password.

    Reply
    • You would login to windows using your account. That is the account used. Since you are already logged into the OS, the password would be validated by domain controller.

      Reply

Leave a Reply

Menu