How do I import a number of files without using File Mask

  • 118Views
  • Last Post 20 July 2021
0
votes
Jay Ong posted this 21 June 2021

Hi Skyvia Support,

 

I have a number of import packages that import csv files into a SQL database each day using the File Mask method. However, I have a new scenario I would like advice on how to handle.


I have a new SCADA server which automatically creates CSV files each day. These files sit on the SCADA server until the files are downloaded manually ~once per month. I can create a PowerShell script to merge all the CSV files into one big CSV and import that single file, but I would prefer to import each file individually to make it easier to manage especially if there is an issue importing a specific day and an error appears. 

 

How do I setup the Integration Service to import and iterate to the next file once the first import has been successful, then import the next file once the second file has imported successfully and so on. Does a feature exist? Can you send me some instructions on how to set this up? Thanks.

 

Kind regards
Jay

Order By: Standard | Newest | Votes
0
votes
Nataliia Nikulina posted this 23 June 2021

Hello Jay,

Thank you for contacting the Skyvia Support team.

 

To implement your scenario, you can create files with the same columns and then reload and manually replace the file quickly in the task using Reload in the package:

Another option is to set up a package with a schedule to launch each hour for example and substitute a file name with the use of a file mask.

Note that it’s not recommended to use seconds or fractions of seconds, and even minutes in the mask  

 

You can follow a guide below to know how to use a mask in the file:
https://docs.skyvia.com/data-integration/import/importing-CSV-files-via-file-masks.html


Let us know if you need any further assistance    Kind regards, Nataliia

0
votes
Jay Ong posted this 24 June 2021

Hi Nataliia,

Manually replacing each file is not a suitable solution for me as I need an automated solution once all the csv files are ready and uploaded to the file storage folder.

I understand it is possible for me to use the file mask method each hour, but it is not an ideal situation for me. I don't want to be limited to waiting an hour to import each file. You said it is not recommended to use File mask with minutes. I would be happy to write a script that would add 1 min to the file mask of each file. There will only be between a max of 31 files for this schedule as there can only be a max of 31 days in a month. Surely, the import package is not only limited to run once per hour?? How can I achive what I want, without the risk of my schedule importer not running. Have any of your other customers run into the same situation? What did they do?

Regards

Jay

0
votes
Jay Ong posted this 30 June 2021

Hi Nataliia,

 

I have not heard from you for a few days. Can you please advise?

 

Regards

Jay

1
votes
Nataliia Nikulina posted this 01 July 2021

Hello Jay,

 

Thank you for the update.

We have received your request and will contact you with an update soon.

 

Kind regards,

Nataliia

 

 

  • Supported by
  • Jay Ong
0
votes
Nataliia Nikulina posted this 02 July 2021

Hello Jay,    

 

Thank you for the update.    

 

Unfortunately adding minutes to a file mask is not a good idea for your scenario.    

There are various reasons for that.    

Firstly, packages can be in the queued status for more than a minute.    

Also, in case of Update or Upsert action operation occurs row by row, package execution can take even more time to run.


We recommend that you use hours in the file mask as we advised previously.

Let us know if we may help you more.    

 

Kind regards,

Nataliia

0
votes
Jay Ong posted this 05 July 2021

This is not the best solution and seems like a limitation. Is there any scenario where minutes can be used then? Can you ask your development team to look into a possible solution for the future?

 

i thnk I have no choice but to merge all the csvs into one or several big csvs and import them manually each month. This will be a manual solution. =( I can stagger the file each hour, but its just very manual which is not ideal.

 

I may need to migrate to use Azure Data Factory. =(

0
votes
Jay Ong posted this 07 July 2021

Hi Nataliia,

 

Can you please respond. Is the integration service using file mask limited then to 1hr imports? I was under the impression the file mask could support minutes when i originally signed up to Skyvia.

 

Regards
Jay

0
votes
Nataliia Nikulina posted this 08 July 2021

Hello Jay,

 

Thank you for the update.

 

As previously mentioned, we don’t recommend using seconds or minutes in the mask since it is not guaranteed that the package will run exactly at the specified time.

 

When Skyvia is under peak load, package run may be delayed for a few seconds or even minutes.

And even if the difference between the package run time and date/time in the file name is minor, the package run will fail.

We will definitely investigate the possibility to implement this feature in the future and will contact you as soon as this functionality is implemented.    

Anyway, you can try to test adding minutes to a file mask for your specific case to see if this works for you as it should.    

 

Kind regards,

Nataliia

0
votes
Jay Ong posted this 19 July 2021

Hi Nataliia,


Can I use Control Flow Beta to achive what I want? Do you have any instructions on how to set this up if I can. I am happy to provide some detials to you or your engineers if someone can help me with the setup. I am certain, others will find this info useful.


Regards
Jay

0
votes
Nataliia Nikulina posted this 20 July 2021

Hello Jay,    

Thank you for the update.    

CSV files are currently not supported in Control Flow so it will not be possible to implement a requested scenario using Control Flow.    

Let us know if we may help you more.  

Kind regards,

Nataliia  

Customer Support Engineer 

Close