title: How to use stored procedure as input dataset in ADF (How to assign database it uses)
body: <p>I want to run a stored procedure against a linkedservice (azure sql database) and output the result of that stored procedure to a dataset (azure sql database).</p>
  
  <p>Is this possible?</p>
  
  <p>I currently have ended up with this:</p>
  
  <p><strong>Pipeline: It should use a stored procedure that is found on a database defined as a linkedservice and copy that over to the output dataset (an azure sql database)</strong></p>
  
  <pre><code>{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Pipeline.json",
    "name": "CopyGetViewsByDateRange",
    "properties": {
      "description": "&lt;Enter the Pipeline description here&gt;",
      "activities": [
        {
          "name": "CopyActivityTemplate",
          "type": "Copy",
          "inputs": [
            {
              "name": "InputDataset"
            }
          ],
          "outputs": [
            {
              "name": "OutputDataset"
            }
          ],
          "typeProperties": {
            "source": {
              "type": "SqlSource",
              "sqlReaderStoredProcedureName": "Analytics_GetViewsByDateRange2",
              "storedProcedureParameters": {
                "clientid": { "value": "12345", "type": "Int" },
                "startdateid": { "value": "20170421", "type": "Int" },
                "enddateid": { "value": "20170514", "type": "Int" }
              }
            },
            "sink": {
              "type": "SqlSink"
            }
          },
          "policy": {
            "concurrency": 1,
            "executionPriorityOrder": "OldestFirst",
            "retry": 3,
            "timeout": "01:00:00"
          },
          "scheduler": {
            "frequency": "Minute",
            "interval": "15"
          }
        }
      ],
      "start": "2017-05-15T00:00:00Z",
      "end": "2017-05-17T00:00:00Z"
    }
  }
  </code></pre>
  
  <p><strong>Input dataset (Note the comments):</strong></p>
  
  <pre><code>{
    "$schema": "http://datafactories.schema.management.azure.com/schemas/2015-09-01/Microsoft.DataFactory.Table.json",
    "name": "InputDataset",
    "properties": {
      "type": "AzureSqlTable", // This surely needs to be a stored procedure type
      "linkedServiceName": "AnalyticsAMECDevDB",
      "structure": [
        {
          "name": "Client_Id",
          "type": "Int64"
        },
        {
          "name": "DimDate_Id",
          "type": "Int64"
        },
        {
          "name": "TotalContentViews",
          "type": "Int64"
        } // The structure represents what the stored procedure is outputting
      ],
      "typeProperties": {
        "tableName": "Analytics.FactPageViews" // This is obviously not right
      },
      "availability": {
        "frequency": "Minute",
        "interval": "15"
      },
      "external": true
    }
  }
  </code></pre>
  
  <p>My stored procedure looks like this:</p>
  
  <pre><code>SELECT 
          @clientid as Client_Id,
          [DimDateId] as DimDate_Id,
          count(1) as TotalContentViews
      FROM 
          [Analytics].[FactPageViews] as pageviews  
      inner join Analytics.DimPages as pages
          on pageviews.dimpageid = pages.id
      where 
          DimDateId between @startdateid and @enddateid
      group by 
          dimdateid
      order by 
          dimdateid
  </code></pre>
  
  <p><strong>EDIT (got something to work atleast)</strong></p>
  
  <p>I am currently managing it by defining a query and running the command there:</p>
  
  <pre><code>"activities": [
    {
      "type": "Copy",
      "typeProperties": {
        "source": {
          "type": "SqlSource",
          "sqlReaderQuery": "$$Text.Format('EXEC [dbo].[GetViewsByDateRange] 2, 20170421, 20170514', WindowStart, WindowEnd)"
        },
        "sink": {
          "type": "SqlSink",
          "writeBatchSize": 0,
          "writeBatchTimeout": "00:00:00"
        },
        "translator": {
          "type": "TabularTranslator",
          "columnMappings": "Client_Id:Client_Id,DimDate_Id:DimDate_Id,TotalContentViews:TotalContentViews"
        }
      },
      "inputs": [
        {
          "name": "InputDataset-0af"
        }
      ],
      "outputs": [
        {
          "name": "OutputDataset-0af"
        }
      ],
  </code></pre>