{ "name": "PL_2_Extract_And_Load_Staging", "properties": { "description": "Child Pipeline to migrate the tables without special characters", "activities": [ { "name": "Lookup Tables", "type": "Lookup", "dependsOn": [], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlServerSource", "sqlReaderQuery": { "value": "@concat('select distinct b.name SchemaName , a.name TableName from ',pipeline().parameters.DatabaseName,'.', ' SYS.tables a,',pipeline().parameters.DatabaseName,'.', 'SYS.schemas b\nwhere a.type=''U''\nand a.schema_id=b.schema_id\nand a.is_ms_shipped =''0''\nand b.name not in (''sys'')\nand a.object_id not in (Select distinct object_id From ',pipeline().parameters.DatabaseName,'.', ' SYS.all_columns c\nwhere (c.name like (''% %'')\nor c.name like (''%\t%'')\nor c.name like (''%~%'')\nor c.name like (''%&%'')\nor c.name like (''%>%'')\nor c.name like (''%<%'')\nor c.name like (''%#%'')\nor c.name like (''%*%'')\nor c.name like (''%+%'')\nor c.name like (''%,%'')\nor c.name like (''%;%'')\nor c.name like (''%(%'')\nor c.name like (''%)%'')\nor c.name like (''%{%'')\nor c.name like (''%}%'')\nor c.name like (''%=%'')\nor c.name like (''%//n%'')\nor c.name like (''%\\n%'')\nor c.name like (''%//t%'')))\norder by 1,2')", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "DS_OnPremSQLServer", "type": "DatasetReference", "parameters": { "ServerName": { "value": "@pipeline().parameters.ServerName", "type": "Expression" } } }, "firstRowOnly": false } }, { "name": "ForEach1", "type": "ForEach", "dependsOn": [ { "activity": "Lookup Tables", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "items": { "value": "@activity('Lookup Tables').output.value", "type": "Expression" }, "isSequential": true, "activities": [ { "name": "Set Schema Name", "type": "SetVariable", "dependsOn": [], "policy": { "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "variableName": "SchemaName", "value": { "value": "@item().SchemaName", "type": "Expression" } } }, { "name": "Set Table Name", "type": "SetVariable", "dependsOn": [ { "activity": "Set Schema Name", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "variableName": "TableName", "value": { "value": "@item().TableName", "type": "Expression" } } }, { "name": "CD - Convert SQL table to Parquest", "type": "Copy", "dependsOn": [ { "activity": "Set Table Name", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "3.12:00:00", "retry": 5, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlServerSource", "queryTimeout": "02:00:00", "partitionOption": "None" }, "sink": { "type": "ParquetSink", "storeSettings": { "type": "AzureBlobFSWriteSettings", "maxConcurrentConnections": 1 }, "formatSettings": { "type": "ParquetWriteSettings" } }, "enableStaging": false, "translator": { "type": "TabularTranslator", "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } }, "inputs": [ { "referenceName": "DS_OnPremSQL_Table", "type": "DatasetReference", "parameters": { "ServerName": { "value": "@pipeline().parameters.ServerName", "type": "Expression" }, "DatabaseName": { "value": "@pipeline().parameters.DatabaseName", "type": "Expression" }, "SchemaName": { "value": "@variables('SchemaName')", "type": "Expression" }, "TableName": { "value": "@variables('TableName')", "type": "Expression" } } } ], "outputs": [ { "referenceName": "DS_Parquet_Staging_Sink", "type": "DatasetReference", "parameters": { "FileSystem": { "value": "@variables('Container')", "type": "Expression" }, "Directory": { "value": "@concat(variables('Directory'),'/',pipeline().parameters.DatabaseName,'/',variables('SchemaName'))", "type": "Expression" }, "FileName": { "value": "@concat(variables('TableName'),'.','parquet')", "type": "Expression" } } } ] } ] } }, { "name": "Lookup Special Characters Tables", "type": "Lookup", "dependsOn": [], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlServerSource", "sqlReaderQuery": { "value": "@concat('select distinct ',variables('Replace_P1'), 'b.name',variables('Replace_P2'),' SchemaName_SC , ',variables('Replace_P1'), 'a.name',variables('Replace_P2'),' TableName_SC, b.name SchemaName, a.name TableName from ',pipeline().parameters.DatabaseName,'.', 'SYS.tables a,',pipeline().parameters.DatabaseName,'.', 'SYS.schemas b\nwhere a.type=''U''\nand a.schema_id=b.schema_id\nand a.is_ms_shipped =''0''\nand b.name not in (''sys'')\nand a.object_id in (Select distinct object_id From ',pipeline().parameters.DatabaseName,'.', 'SYS.all_columns c\nwhere (c.name like (''% %'')\nor c.name like (''%\t%'')\nor c.name like (''%~%'')\nor c.name like (''%&%'')\nor c.name like (''%>%'')\nor c.name like (''%<%'')\nor c.name like (''%#%'')\nor c.name like (''%*%'')\nor c.name like (''%+%'')\nor c.name like (''%,%'')\nor c.name like (''%;%'')\nor c.name like (''%(%'')\nor c.name like (''%)%'')\nor c.name like (''%{%'')\nor c.name like (''%}%'')\nor c.name like (''%=%'')\nor c.name like (''%//n%'')\nor c.name like (''%\\n%'')\t\t\t\t\t\t \nor c.name like (''%//t%'')))\norder by 1,2')", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "DS_OnPremSQLServer", "type": "DatasetReference", "parameters": { "ServerName": { "value": "@pipeline().parameters.ServerName", "type": "Expression" } } }, "firstRowOnly": false } }, { "name": "ForEach - Special Characters", "type": "ForEach", "dependsOn": [ { "activity": "Lookup Special Characters Tables", "dependencyConditions": [ "Succeeded" ] } ], "userProperties": [], "typeProperties": { "items": { "value": "@activity('Lookup Special Characters Tables').output.value\n", "type": "Expression" }, "isSequential": true, "activities": [ { "name": "CD - Convert SQL table to Parquest SC", "type": "Copy", "dependsOn": [ { "activity": "Lookup - Get SQL Statement", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "3.12:00:00", "retry": 5, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlServerSource", "sqlReaderQuery": { "value": "@activity('Lookup - Get SQL Statement').output.firstRow.Query", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "sink": { "type": "ParquetSink", "storeSettings": { "type": "AzureBlobFSWriteSettings", "maxConcurrentConnections": 1 }, "formatSettings": { "type": "ParquetWriteSettings" } }, "enableStaging": false, "translator": { "type": "TabularTranslator", "typeConversion": true, "typeConversionSettings": { "allowDataTruncation": true, "treatBooleanAsNumber": false } } }, "inputs": [ { "referenceName": "DS_OnPremSQL_Table", "type": "DatasetReference", "parameters": { "ServerName": { "value": "@pipeline().parameters.ServerName", "type": "Expression" }, "DatabaseName": { "value": "@pipeline().parameters.DatabaseName", "type": "Expression" }, "SchemaName": { "value": "@variables('SchemaName_SC')", "type": "Expression" }, "TableName": { "value": "@variables('TableName_SC')", "type": "Expression" } } } ], "outputs": [ { "referenceName": "DS_Parquet_Staging_Sink", "type": "DatasetReference", "parameters": { "FileSystem": { "value": "@variables('Container')", "type": "Expression" }, "Directory": { "value": "@concat(variables('Directory'),'/',pipeline().parameters.DatabaseName,'/',variables('SchemaName_SC'))", "type": "Expression" }, "FileName": { "value": "@concat(item().TableName_SC,'.','parquet')", "type": "Expression" } } } ] }, { "name": "Lookup - Get SQL Statement", "description": "", "type": "Lookup", "dependsOn": [ { "activity": "Set Table Name SC", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "timeout": "0.12:00:00", "retry": 0, "retryIntervalInSeconds": 30, "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "source": { "type": "SqlServerSource", "sqlReaderQuery": { "value": "@concat('DECLARE @s VARCHAR(MAX)\nDECLARE @schemaname VARCHAR(500) = ''',item().SchemaName_SC,'''\nDECLARE @tablename VARCHAR(500) = ''', item().TableName_SC ,''' \nDECLARE @schema VARCHAR(500) = ''',item().SchemaName,'''\nDECLARE @table VARCHAR(500) = ''', item().TableName ,''' \nSELECT @s = ISNULL(@s + '', '','''') + QUOTENAME(c.name) + '' as '' + QUOTENAME(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.name,''\\\\'',''_''),''+'',''Plus''),''~'',''|''),''-'',''_''),''–'',''_''),'','',''_''),''('',''_''),'')'',''_''),''/'',''|''),'':'',''_''),''<'',''LessThan''),''>'',''GreaterThan''),''?'',''|''),''*'',''|''),''.'',''_''),''['',''_''),'']'',''_''),''{'',''_''),''}'',''_''),''\\'',''|''),'' '',''_''),''%'',''Percent''),''#'',''Number''),''='',''Equal''),''&'',''And''))\nFROM ',pipeline().parameters.DatabaseName,'.', 'sys.all_columns c join ',pipeline().parameters.DatabaseName,'.', 'SYS.all_objects t ON c.object_id = t.object_id\njoin ',pipeline().parameters.DatabaseName,'.', 'sys.schemas s ON t.schema_id = s.schema_id\nwhere s.name = @schema AND t.name = @table \nSELECT ''select''+ @s +'' '' +''from ''+'' '' + QUOTENAME( @schema )+''.''+ QUOTENAME(@table) as Query')", "type": "Expression" }, "queryTimeout": "02:00:00", "partitionOption": "None" }, "dataset": { "referenceName": "DS_OnPremSQLServer", "type": "DatasetReference", "parameters": { "ServerName": { "value": "@pipeline().parameters.ServerName", "type": "Expression" } } }, "firstRowOnly": true } }, { "name": "Set Schema Name SC", "type": "SetVariable", "dependsOn": [], "policy": { "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "variableName": "SchemaName_SC", "value": { "value": "@item().SchemaName_SC", "type": "Expression" } } }, { "name": "Set Table Name SC", "type": "SetVariable", "dependsOn": [ { "activity": "Set Schema Name SC", "dependencyConditions": [ "Succeeded" ] } ], "policy": { "secureOutput": false, "secureInput": false }, "userProperties": [], "typeProperties": { "variableName": "TableName_SC", "value": { "value": "@item().TableName_SC", "type": "Expression" } } } ] } } ], "parameters": { "ServerName": { "type": "string", "defaultValue": "OnPremServerName.eu.rabonet.com" }, "DatabaseName": { "type": "string", "defaultValue": "ABC" } }, "variables": { "SchemaName": { "type": "String" }, "TableName": { "type": "String" }, "Container": { "type": "String", "defaultValue": "ContainerName" }, "Directory": { "type": "String", "defaultValue": "inbound/gold/mds/xyz" }, "FileName": { "type": "String" }, "SchemaName_SC": { "type": "String" }, "TableName_SC": { "type": "String" }, "Replace_P1": { "type": "String", "defaultValue": "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(" }, "Replace_P2": { "type": "String", "defaultValue": ",'+','Plus'),'~','|'),'-','_'),'–','_'),',','_'),'(','_'),')','_'),'/','|'),':','_'),'<','LessThan'),'>','GreaterThan'),'?','|'),'*','|'),'.','_'),'{','_'),'}','_'),'\\','|'),' ','_'),'%','Percent'),'#','Number'),'=','Equal'),'&','And'),'\t','_')" } }, "folder": { "name": "OnPrem Migration" }, "annotations": [], "lastPublishTime": "2024-10-28T09:53:30Z" }, "type": "Microsoft.DataFactory/factories/pipelines" }