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.
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.
In the Integration section, click Create Now under Data Import.
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.
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.
Then we will configure an FTP connection
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.
Right after that we will select the target table. In our case the target table is actor table.
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.
When we click finish it will bring up the following screen.
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.
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.
We can execute the same select statement which we had executed earlier and see if the table contains the valid data.
Once we commit that our task has worked successfully, we can create a daily schedule.
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)
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.
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.
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.
So essentially you can use “trusted connection”