locked
Does ADF support copying data from on-premise sql server to sql azure RRS feed

  • Question

  • As titles. Other similar questions are relevant to SSIS.

    Thanks,

    Arthur

    Tuesday, November 11, 2014 10:13 AM

Answers

  • Hi Arthur, I see that your slices are in 'pending execution' state.  There can be several reasons for this, but one may be that you need to specify the 'waitOnExternal' property in the availability section. The 'waitOnExternal' property is used to signal ADF that the reference data is available and ready for the pipeline to move forward.  Here is an example:

    {

        "name": "CustomerTable",

        "properties":

        {

            "location":

            {

                "type": "AzureBlobLocation",

                "folderPath": "MyContainer/MySubFolder/",

                "linkedServiceName": "MyLinkedService",

                "format":

                {

                    "type": "TextFormat",

                    "columnDelimiter": ",",

                    "rowDelimiter": ";"

                }

            },

            "availability":

            {

                "frequency": "Hour",

                "interval": 1,

                "waitOnExternal":

                {

                    "dataDelay": "00:10:00",

                    "retryInterval": "00:01:00",

                    "retryTimeout": "00:10:00",

                    "maximumRetry": 3

                }

            }

        }

    }

    Other items to troubleshoot:

    1. try another scenario such as copy to blob to blob to see if there is still an issue

    2. set your slice range within your active period, not at the endpoints of the active period.

    Thanks,

    Sonia

    Tuesday, November 25, 2014 5:05 PM

All replies

  • Hi Arthur - thanks for checking out ADF!  Yes, we do support copying from on-premises SQL Server to Azure storage.  First, you install a gateway client on your on-premise sql server, and then register it as a linked service with ADF.  You also register your Azure SQL (or Azure table or blob) as a linked service. Next you define the datasets and a pipeline with the on-premise server as the input source, and the Azure SQL db as the output sink, and a copy activity for the processing.  You can find more details and examples in our documentation articles in the Develop section:

    "Enable your pipelines to work with on-premises data" and "Copy data with Data Factory"
    Hope that helps!

    Sonia

    Wednesday, November 12, 2014 8:36 PM
  • Actually, I did what exactly you suggested. However, no runs for my pipeline. I made a simple screenshot, though not enough information. I can provide more information if you want.

    Thanks,

    Arthur

    Thursday, November 13, 2014 8:59 AM
  • Hi Arthur, note that the UI displays local time, while the cmdlets work with UTC time.  A few questions for you:  can you give me the command you used to start your pipeline? What time zone are you in?  When you click the Diagram part from the main Data Factory part, what do you see?  If you click into the Produced table, what slice status do you see - pending execution or failed?

    Thursday, November 13, 2014 9:36 PM
  • Hi Sonia,

    Thanks for your reply. 

    The command I used to start my pipeline is 

    Set-AzureDataFactoryPipelineActivePeriod -ResourceGroupName <myResourceGroupName> -DataFactoryName <myDataFactoryName> -PipelineName <myPipelineName> -StartDateTime "2014-11-13 00:00:00" -EndDateTime "2014-11-13 01:00:00"

    I was setting those two datetimes to be a passed date time so my pipeline could start immediately, am I right?

    Below are statuses of consumed and produced tables.

    Consumed:

    Produced:

    As you can see, I tried three times, none of them started to run truly. 

    Also, from this link, it seems that my scenario has not been supported yet.

    Thanks,

    Arthur

    Friday, November 14, 2014 1:59 AM
  • Are you able to kickoff a simpler activity, such as copy on-premise to Blob?  There are some scenarios with our preview service where on-premise direct to Azure SQL db are still being worked out. 
    Monday, November 17, 2014 5:38 PM
  • Hi Jingfei,

    Copying data from SQL Server to Azure SQL Database is not officially supported at this stage - it is a common ask we got from the customers with high priority, and team are working on full validation before announce feature lit up. Please expect this capability coming soon.

    To set the expectation properly, we will keep the "Supported inputs and outputs" table here up-to-date; the pairs listed should always be working fine. You could give them a try to get familiar with ADF.

    Seeing your produced table screenshot, seems there is orchestration issue happened. Could you try a supported case then see if it can work as expected? Refer to Get started with Data Factory or Enable your pipelines to work with on-premises data if needed.

    Thanks,

    Linda

    Tuesday, November 18, 2014 2:03 AM
  • Hi Arthur, I see that your slices are in 'pending execution' state.  There can be several reasons for this, but one may be that you need to specify the 'waitOnExternal' property in the availability section. The 'waitOnExternal' property is used to signal ADF that the reference data is available and ready for the pipeline to move forward.  Here is an example:

    {

        "name": "CustomerTable",

        "properties":

        {

            "location":

            {

                "type": "AzureBlobLocation",

                "folderPath": "MyContainer/MySubFolder/",

                "linkedServiceName": "MyLinkedService",

                "format":

                {

                    "type": "TextFormat",

                    "columnDelimiter": ",",

                    "rowDelimiter": ";"

                }

            },

            "availability":

            {

                "frequency": "Hour",

                "interval": 1,

                "waitOnExternal":

                {

                    "dataDelay": "00:10:00",

                    "retryInterval": "00:01:00",

                    "retryTimeout": "00:10:00",

                    "maximumRetry": 3

                }

            }

        }

    }

    Other items to troubleshoot:

    1. try another scenario such as copy to blob to blob to see if there is still an issue

    2. set your slice range within your active period, not at the endpoints of the active period.

    Thanks,

    Sonia

    Tuesday, November 25, 2014 5:05 PM
  • When can we expect this capability coming out? I tried a poc copying data from on prem to azure database and it actually worked fine for one slice.

    I tried copying data from on prem to sql azure directly by specifying 'sink' in pipeline as sqlsink. It worked fine for one slice.

    {
        "name": "ADFTutorialPipelineOnPrem",
        "properties":
        {
            "description" : "This pipeline has one Copy activity that copies data from an on-prem SQL to Azure Database",
             "activities":
            [
                {
                    "name": "CopyFromSQLtoAzure",
                    "description": "Copy data from on-prem SQL server to Azure Database",     
                    "type": "CopyActivity",
                    "inputs": [ {"name": "EmpOnPremSQLTable"} ],
                    "outputs": [ {"name": "EmpSQLAzureTable"} ],
                    "transformation":
                    {
                        "source":
                        {                               
                            "type": "SqlSource",
                            "sqlReaderQuery": "select * from Users"
                        },
                        "sink":
                        {
                            "type": "SqlSink"
                        }   
                    },
                    "Policy":
                    {
                        "concurrency": 1,
                        "executionPriorityOrder": "NewestFirst",
                        "style": "StartOfInterval",
                        "retry": 0,
                        "timeout": "01:00:00"
                    }       

                 }
            ]
        }
    }

    Wednesday, December 17, 2014 12:20 PM
  • Hi Megha, recently we just officially support direct copying data between on-prem SQL Server and Azure SQL Database. So yes your scenario could work fine now, and the way you configured it is exactly correct. See the latest "Supported inputs and outputs" table in this article, as well as the properties supported under each source/sink type. Thanks.

    Friday, December 19, 2014 7:44 AM
  • Hi sonia,

    Is there more info on the datdelay available. e.g. I want to delay 1 day and 4 hours. If I enter 28:00:00 it becomes 28.00:00:00. After waiting 1 day the process is still not running. How can I put in a dealy with more then 24 hours?

    thanks,

    Julius


    jk

    Thursday, July 2, 2015 11:50 PM