Using Files Stored in Azure File Services with SQL Server Integration Services Part 2

By:   |   Comments   |   Related: > Azure Data Factory


When migrating your Integration Services project to the Azure cloud environment, it’s not only a matter of moving the projects and packages to Azure, you also have to take into account external dependencies, such as the source data. Is it going to stay on-premises? Or is it going to move to the cloud as well. For files, such as flat files or Excel files, you can use Azure File Services to host these inside the Azure cloud. In part 1 of the tip, we demonstrated how you can setup and configure Azure File Services (also named Azure File Storage or simply Azure Files). In this part, we’ll show a practical example of how we can modify an existing SSIS package so it uses Azure File Services.


If you haven’t already, please refer to part 1 of this tip. There the setup and configuration of Azure File Services is explained, as well as how you can setup your credentials on your machine and in the SSISDB database. A correct configuration is necessary if you want to follow along with this tip.

Replacing Local File Share with Azure File Services

Test Setup

In this tip, we’re going to read a very simple CSV file containing a list of the top 250 movies (the list is a couple of years old if you see some weird rankings):

flat file with movies

We’re going to read this data and store in a table inside an Azure SQL Database (or any other SQL Server database). The DDL for the destination table is as follows:

CREATE TABLE [dbo].[Top250Movies](
   [Movies] [nvarchar](250) NULL

Our sample SSIS package is straight forward as well (it is created using Visual Studio 2019 using the dark theme). First, we truncate the destination table with an Execute SQL Task:

truncate table

After the Execute SQL Task, we have a data flow. Inside, there’s a flat file source and an OLE DB Destination. The flat file source is configured with a flat file connection manager pointing to the file on our local file share:

flat file connection manager local

The destination simply maps the one column of the flat file to the column in the table:


When we run the package, the data is read from the local file share and transferred over the network to the destination table inside the Azure SQL DB.

success local

Replacing Local File Share with Azure File Services

In part 1, we created an Azure Storage account and a file share which we called mssqltips-fileshare. Inside this share, we now store the CSV file:

file share explorer

The only change we need to make to our SSIS package is the connection string of the flat file connection manager! It’s that easy. Be aware though, that credentials to access the file share need to be set up on your machine. More information on that in part 1. The connection string takes on the following format:


In our sample, this becomes:

flat file connection azure

Moving to Azure File Services should be pretty painless if you have expressions in place for your connection managers. In that case, you just need to change the project/package parameter or an environment variable in one single location to update multiple connection managers. You can find more information on this subject in the tip: Setup Environment Variables in SQL Server Integration Services.

Getting your Files in Azure File Services

Reading files stored in Azure File Services is one thing, but getting them there is another. There are a couple of options for writing data to Azure File Services:

  • Simply use the Flat File Destination in an SSIS package. The configuration is the same as shown in this tip.
  • You can use Azure Logic Apps to copy files to the file share. This follows the same pattern described in the tip Transfer Files from SharePoint To Blob Storage with Azure Logic Apps. In Logic Apps, there’s an Azure File Storage connector:
logic app connector
  • There are a couple of actions you can choose from. Currently they’re all in preview. You can for example choose the Create file action.

create file logic app
  • Finally, you can choose the folder where you want to store your file:
configure logic app folder
  • You can use Azure Data Factory to store data inside Azure File Services using the Copy Data activity. You can find an example of this activity in the tip Create Azure Data Factory Pipeline. There’s a connector available out-of-the-box:
AFS connector in ADF
  • To create the connection, you need to specify the URL to the file share – the same connection string used in the SSIS package, but you also need to specify a username and password. These are the same credentials used on your machine to connect to the file share and in the SSISDB. You can find an example of setting up a Linked Service in ADF in the tip Create Azure Data Lake Linked Service Using Azure Data Factory.

configure linked service in ADF
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms