none
Azure SQL DB to interface with SSIS and SSAS RRS feed

  • Question

  • From: Shane Danihser @danes488 via Twitter

    I have a azure sql database and i'm having difficulties. I am trying to get SSIS and SSAS to interface with it but i'm coming up dry on articles that appropriately explain how i can get these connections to happen. Can you assist? The Azure SQL instance I have is not SQL installed on a windows server on Azure. This is the Azure SQL sans OS, so I have no OS to attach this file to and SSIS and SQL server don't run locally on anything. the instance in the cloud is -just- sql and nothing else, and that's what i'm trying to get SSIS and SSAS to connect to.

    AzureSupport provided the following documentation: aka.ms/d1218689aka.ms/d12186891aka.ms/d12186892 

    Thanks,

    @AzureSupport

    Friday, June 24, 2016 3:50 PM

All replies

  • When you setup your connection in SSIS or SSAS you need to use Sql server credentials, and supply the servername as servername.database.windows.net, and supply the user name as username@servername and supply the password you setup when you created the SQL Azure DB or some other valid login you have created.

    Finally, you have to setup a firewall rule to allow the client to connect from whatever IP they are using.  https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings/

    Friday, July 1, 2016 8:12 PM
  • Hi Shane,

    What do you mean by "interface with" Azure SQL database?  Do you mean connecting to Azure SQL DB as a source and/or destination of data from SSIS?  if so, Azure SQL database can be treated just like on premise SQL server and you can use ADO.net source/destination, oleDB source/destination and Odbc source/destination.  To connect to Azure SQL from SSIS you do not need SSIS azure feature pack.

    SQL azure does not have SSISDB in it so SSIS catalog cannot be stored in Azure SQL.  If you would like to use SSIS catalog in Azure, you should create a Azure SQL VM and use SSIS catalog from SQL Azure VM.

    Please let us know if you have additional questions.

    Monday, July 11, 2016 10:59 PM
  • Hello Janine,

    The issue that I have is that I do not have, nor have I been able to find a place to download SSIS or SSAS, so that I can connect to the Azure SQL DB as if it were an on premise SQL server.

    I don't believe I need the SSISDB stored in Azure SQL, I just need to find a way to download/use SSIS on a windows 10 workstation that can connect to the Azure SQL DB.

    Wednesday, July 13, 2016 3:26 PM
  • To obtain SSIS, please download and install SQL server, select Integration Services as an optional component.  SSIS is only available in SQL server standard and enterprise editions.  SSIS packages can only run on those editions.

    To design SSIS packages which can run on SQL server, please download SSIS SSDT designer here to start:

    https://msdn.microsoft.com/en-us/library/mt204009.aspx?f=255&MSPPError=-2147217396


    Wednesday, July 13, 2016 6:28 PM
  • Hey Janine,

    Thanks for the response. The problem I have with this, and the original reason for my writing, is that SQL server on Azure is only the database. And I need to pay for a full version of SQL to have continued access to features such as SSIS and SSAS, correct?

    From my understanding though, SQL Azure was everything we needed. So from what I'm gathering, that's not the case.

    Wednesday, July 13, 2016 7:09 PM
  • Hi, yes you're correct. SQL Azure does not contain SSIS and does not contain everything within SQL server.

    In order to get SSIS, you can either get SQL server license or obtain an Azure virtual machine with SQL server already installed. 

    Tuesday, July 19, 2016 7:56 AM