locked
V2 How to make the tablename Variable RRS feed

  • Question

  • Hello,

    Due to missing date-from and date-to in the imports i"ve create a (v1) system that uploads all data to dynamic created tables. The slicetime is in the table name. Works like a charm.

    My SqlDWTable also had a dynamic name in the Datatable. But this syntax is not working in v2.

    This is my table definition (altered to a v2 parameter insteadof partitionedby).

    "typeProperties": {
    "tableName": "$$Text.Format('[stg_otb_bict2233].[DAY_JUSTIF_{0:yyyyMMddHH}]', @dataset().parameters.TriggerRunTime)"
    },

    But this resulst into a error ...

    {
        "copyDuration": 1,
        "errors": [
            {
                "Code": 3003,
                "Message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Parse error at line: 1, column: 1: Incorrect syntax near '$'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 1, column: 1: Incorrect syntax near '$'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=103010,State=1,Message=Parse error at line: 1, column: 1: Incorrect syntax near '$'.,},],'",
                "EventType": 0,
                "Category": 5,
                "Data": {
                    "FailedDbOperation": "Parse error at line: 1, column: 1: Incorrect syntax near '$'.",
                    "ServerName": "f276fe485143.tr28.westeurope1-a.worker.database.windows.net,11194",
                    "SqlErrorNumber": "103010",
                    "SqlErrors": "[{\"Source\":\".Net SqlClient Data Provider\",\"Number\":103010,\"State\":1,\"Class\":16,\"Server\":\"f276fe485143.tr28.westeurope1-a.worker.database.windows.net,11194\",\"Message\":\"Parse error at line: 1, column: 1: Incorrect syntax near '$'.\",\"Procedure\":\"\",\"LineNumber\":1}]",
                    "SqlClientConnectionId": "b64dca36-7948-40d8-8573-f8bc747aadcc"
                },
                "MsgId": null,
                "ExceptionType": null,
                "Source": null,
                "StackTrace": null,
                "InnerEventInfos": []
            }
        ],
        "effectiveIntegrationRuntime": "DefaultIntegrationRuntime (West Europe)",
        "usedCloudDataMovementUnits": 4,
        "usedParallelCopies": 1,
        "billedDuration": 6,
        "effectiveIntegrationRuntimes": [
            {
                "name": "DefaultIntegrationRuntime",
                "type": "Managed",
                "location": "West Europe",
                "billedDuration": 0.06666666666666667,
                "nodes": null
            }
        ]
    }

    Mayby it would work with a syntax "tableName": "[stg_otb_bict2233].[DAY_JUSTIF_@{dataset().parameters.TriggerRunTime}" but I need the time formatting ...

    Any idea's

    Tuesday, December 12, 2017 12:22 PM

Answers

  • I did find the problem.

    "folderPath": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))"

    Shoul be used like

    "folderPath": {
    "value": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))",
    "type": "Expression"
    }

    Tuesday, December 19, 2017 9:55 PM

All replies

  • The syntax in ADF V2 for parameters and concatenating variables with date type variables is the following
    "typeProperties": {
    "tableName": "@concat('[stg_otb_bict2233].[DAY_JUSTIF_', formatDateTime(dataset().ScheduledRunTime, 'yyyyMMddHH'))"
    },






    • Edited by saulcruzr Tuesday, December 12, 2017 2:34 PM
    Tuesday, December 12, 2017 2:26 PM
  • Somewhere i'm making a mistake, still getting this error ...

    Activity CopyFromCSVToSTG failed: Failure happened on 'Source' side. ErrorCode=UserErrorSourceBlobNotExist,

    'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The required Blob is missing.

    ContainerName: https://xxx.blob.core.windows.net/bict2233, ContainerExist: True, BlobPrefix: S1073_PBI_DAY_JUSTIF_VW.csv, BlobCount: 0.,Source=Microsoft.DataTransfer.ClientLibrary,'

    For helping out I will post the call and the json definitions.

    $runId = Invoke-AzureRmDataFactoryV2Pipeline -PipelineName $plName -ResourceGroupName $rgName -dataFactoryName $dfName -ParameterFile "C:\ADF\bict2233_A\lstgTriggerParams.json"

    lstgTriggerParams.json

    {
      "TriggerRunTime": "2017-12-12T18:30:00"
    }

    lstTrigger.json

    {
    	"properties": {
    		"name": "lstg-Trigger",
    		"type": "ScheduleTrigger",
    		"typeProperties": {
    			"recurrence": {
    				"frequency": "Day",
    				"interval": 1,
    				"startTime": "2017-12-10",
    				"endTime": "2099-12-31",
    				"timeZone": "UTC",
    				"schedule": {
    					"minutes": [5],
    					"hours": [16]
    				}
    			}
    		},
    		"pipelines": [{
    			"pipelineReference": {
    				"type": "PipelineReference",
    				"referenceName": "lstgDayJustif"
    			},
    			"parameters": {
    				"TriggerRunTime": {
    					"type": "DateTime",
    					"value": "@trigger().scheduledTime"
    				}
    			}
    		}]
    	}
    }

    The Pipeline, lstgDayJustif.json

    {
    	"name": "lstg-DAY_JUSTIF",
    	"properties": {
    		"activities": [{
    			"name": "CopyFromCSVToSTG",
    			"type": "Copy",
    			"inputs": [{
    				"referenceName": "csvDayJustif",
    				"type": "DatasetReference",
    				"parameters": {
                                "TriggerRunTime": "@pipeline().parameters.TriggerRunTime"
                            }
    			}],
    			"outputs": [{
    				"referenceName": "stgDayJustif",
    				"type": "DatasetReference",
    				"parameters": {
                                "TriggerRunTime": "@pipeline().parameters.TriggerRunTime"
                            }
    			}],
    			"typeProperties": {
    				"source": {
    					"type": "BlobSource"
    				},
    				"sink": {
    					"type": "SqlDWSink",
    					"preCopyScript": "@concat('BEGIN TRY CREATE TABLE [stg_otb_bict2233].[DAY_JUSTIF_', formatDateTime(pipeline().parameters.TriggerRunTime, 'yyyyMMddHH'), '] ([REF_JUS_ID] [BIGINT] NULL,[REF_JUSTIFICATION_NO] [BIGINT] NULL) WITH (DISTRIBUTION = HASH([IMPACTED_TRAIN_DEPARTURE_DT]), CLUSTERED COLUMNSTORE INDEX) END TRY BEGIN CATCH TRUNCATE TABLE [stg_otb_bict2233].[DAY_JUSTIF_', formatDateTime(pipeline().parameters.TriggerRunTime,'yyyyMMddHH'), '] END CATCH')",
    					"allowPolyBase": true
    				},
    				"translator": {
    					"type": "TabularTranslator",
    					"columnMappings": "REF_JUS_ID:REF_JUS_ID,REF_JUSTIFICATION_NO:REF_JUSTIFICATION_NO"
    				},
    				
    			},
    		}],
    		"parameters": {
    			"TriggerRunTime": {
    				"type": "DateTime"
    			}
    		}
    	}
    }

    And finally the source-dataset csvDayJustif.json

    {
    	"name": "csvDayJustif",
    	"properties": {
    		"structure": [{
    			"name": "REF_JUS_ID",
    			"type": "Int64"
    		},
    		{
    			"name": "REF_JUSTIFICATION_NO",
    			"type": "Int64"
    		}],
    		"published": false,
    		"type": "AzureBlob",
    		"linkedServiceName": {
    			"referenceName": "xxx",
    			"type": "LinkedServiceReference"
    		},
    		"typeProperties": {
    			"fileName": "S1073_PBI_DAY_JUSTIF_VW.csv",
    			"folderPath": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))",
    			"format": {
    				"type": "TextFormat",
    				"columnDelimiter": "¤",
    				"quoteChar": "\"",
    				"firstRowAsHeader": true
    			}
    		},
    		"parameters": {
    			"TriggerRunTime": {
    				"type": "DateTime"
    			}
    		}
    	}
    }

    Wednesday, December 13, 2017 7:35 AM
  • it means that either the path or the file name are wrong, I would guess that the following would result in something like bict2233/data-in/day/2017121512

    for debugging purposes, can you try first hard-coding it and then changing it back to the variable ? 


    "folderPath": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))"

    you can try also using other system variables instead of your custom TriggerRunTime, just to make sure you're doing it right https://docs.microsoft.com/en-us/azure/data-factory/control-flow-system-variables


    • Edited by saulcruzr Friday, December 15, 2017 5:52 PM
    Friday, December 15, 2017 5:50 PM
  • I did find the problem.

    "folderPath": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))"

    Shoul be used like

    "folderPath": {
    "value": "@concat('bict2233/data-in/day/', formatDateTime(dataset().TriggerRunTime,'yyyyMMddHH'))",
    "type": "Expression"
    }

    Tuesday, December 19, 2017 9:55 PM