SFTP File Import To SQL Azure DB

• pmcpherson

  SSC-Addicted

  Points: 477

  More actions

  May 22, 2017 at 4:09 pm

  #342939

  Hi and thanks for helping.
  I have a database in the Azure Cloud, it is a SQL database as a service (PaaS).
  I have a client that stores files that I have to collect daily, on their premises, via SFTP.
  In an on-premise solution, I would store encrypted connection information in a table, query it to create batch files in secured NTFS folders, execute the batch files to get the .csv files local, then delete the batch files, and update log tables.
  Then I would use T SQL commands to bulk insert the contents of the files to their respective tables.
  It is not the best solution, but it gets the job done.

  How do I change this flow to work with SQL Azure (PaaS)?
  Since it is PaaS, I don't have the SQL Agent or Jobs.
  I found Azure Automation which I can use to get info from our Azure FTP server, but not one with a different Active Directory service (unless I am reading the documentation wrong).

  Surely somebody else has done this already!
  Please, help.
  Also, I am new to Azure period, and will likely need click though examples of how to setup any middle pieces.