0

We plan to migrate the DB server from SQL VM to a managed instance. And previously the input files (csv) for the SSIS packages were kept in some drive location (like C: E:) since it was VM. But now we are moving to the managed instance and it is serverless. So we are planning to place the input files in Azure file storage but in SSIS there are no default connection managers to connect the Azure file storage. So kindly advise that how to fetch the input files from Azure file storage through SSIS packages.

Ram
  • 527
  • 1
  • 4
  • 7

1 Answers1

1

To use Azure Files, and you simply set the credentials to use:

catalog.set_execution_credential @domain = N'Azure', @user = N'<storage-account-name>', @password = N'<storage-account-key>'

And replace the drive letter with a UNC path, and use the same connectors you would for the local file system.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks a lot, David Browne , one small clarification, is catalog.set_execution_credential procedure not available in SQL VM? Because in SQL managed instance i can able to access this procedure but not in SQL VM. – Ram Oct 03 '22 at 17:26
  • Correct. On a SQL Server VM the package execution impersonates the user running the package, typically the SQL Agent Service Account or a SQL Agent Proxy. – David Browne - Microsoft Oct 03 '22 at 17:48
  • ok thanks, so could you please confirm that from SQL VM, how we can access/connect the Azure file storage? – Ram Oct 03 '22 at 19:01
  • You can use cmdkey to register a credential for the user https://learn.microsoft.com/en-us/azure/storage/files/storage-how-to-use-files-windows, or use AD DS or AAD auth https://learn.microsoft.com/en-us/azure/storage/files/storage-files-identity-auth-active-directory-enable – David Browne - Microsoft Oct 03 '22 at 19:11