question

PaulFinol avatar image
0 Votes"
PaulFinol asked PaulFinol published

Data Factory data flow columns

I need to extract CSV files from SFT in external location to SQL server
I have found several limitations for this task with ADF
For example SFTP connector is only available with copy activity not data flow
But copy activity only support single character column separator not multi-character
But mi CSV are separated with |
on the other hand data flow doesn't connect to SQL server yet
I decided to pull SFTP files to blog first and later transform from blob using the delimiter | to identify columns and place result file in the blob
my idea is to use copy activity to move from blob to SQL server once the columns are defined

81969-image.png


the extraction to blob is OK and the column separation on the dataflow source is also OK
however when i run the pipeline there is an error on the sink from data flow

82008-image.png




Is that a real limitation or am i doing something wrong? i definided the shema correctly on the dataset from the dataflow activity

azure-data-factoryadfs
image.png (24.7 KiB)
image.png (28.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Are you able to share the data flow script here so that we can see the code behind your data flow? The script is available from the top-right of the data flow designer view from the "script" button.

0 Votes 0 ·

1 Answer

PaulFinol avatar image
0 Votes"
PaulFinol answered PaulFinol published

i built a second choice using copy with pipe delimiter which is working OK but i the final result is not the what i am really looking for

82018-image.png


{
"name": "pipeline1",
"properties": {
"activities": [
{
"name": "Copy_SFTP_to_BLob",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "DelimitedTextSource",
"storeSettings": {
"type": "SftpReadSettings",
"recursive": false,
"wildcardFolderPath": "upload",
"wildcardFileName": "RESUMEN*",
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "DelimitedTextReadSettings"
}
},
"sink": {
"type": "DelimitedTextSink",
"storeSettings": {
"type": "AzureBlobStorageWriteSettings"
},
"formatSettings": {
"type": "DelimitedTextWriteSettings",
"quoteAllText": true,
"fileExtension": ".txt"
}
},
"enableStaging": false,
"dataIntegrationUnits": 2,
"translator": {
"type": "TabularTranslator",
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false,
"dateTimeFormat": "\"YYYY-MM-DD hh:mm:ss\""
}
}
},
"inputs": [
{
"referenceName": "Dataset_from_SFTP",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Dataset_SFTPtoBlob",
"type": "DatasetReference"
}
]
},
{
"name": "Transform_CSV",
"type": "ExecuteDataFlow",
"dependsOn": [
{
"activity": "Copy_SFTP_to_BLob",
"dependencyConditions": [
"Failed"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"dataflow": {
"referenceName": "Dataflow_sink_to_Blob",
"type": "DataFlowReference"
},
"compute": {
"coreCount": 8,
"computeType": "General"
},
"traceLevel": "None"
}
},
{
"name": "Copy_Blob_to_SQL",
"type": "Copy",
"dependsOn": [
{
"activity": "Transform_CSV",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "DelimitedTextSource",
"storeSettings": {
"type": "AzureBlobStorageReadSettings",
"recursive": false,
"wildcardFileName": "Tansformed_RESUMEN*",
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "DelimitedTextReadSettings"
}
},
"sink": {
"type": "SqlServerSink"
},
"enableStaging": false,
"dataIntegrationUnits": 2,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"name": "Codigo_"
},
"sink": {
"name": "Codigo",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ames"
},
"sink": {
"name": "Ames",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Peri"
},
"sink": {
"name": "Peri",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Periodo"
},
"sink": {
"name": "Periodo",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Origen"
},
"sink": {
"name": "Origen",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Neto"
},
"sink": {
"name": "Neto",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktha"
},
"sink": {
"name": "Ktha",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Kthn"
},
"sink": {
"name": "Kthn",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "KthI"
},
"sink": {
"name": "KthI",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktim"
},
"sink": {
"name": "Ktim",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktdl"
},
"sink": {
"name": "Ktdl",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "KtdlO"
},
"sink": {
"name": "KtdlO",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktde"
},
"sink": {
"name": "Ktde",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktds"
},
"sink": {
"name": "Ktds",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktfm"
},
"sink": {
"name": "Ktfm",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Ktex"
},
"sink": {
"name": "Ktex",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "DiasTr"
},
"sink": {
"name": "DiasTr",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "DiasLi"
},
"sink": {
"name": "DiasLi",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "TramoCar"
},
"sink": {
"name": "TramoCar",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Coprev"
},
"sink": {
"name": "Coprev",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Cod_Reg"
},
"sink": {
"name": "Cod_Reg",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Kcay"
},
"sink": {
"name": "Kcay",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Khij"
},
"sink": {
"name": "Khij",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Kotc"
},
"sink": {
"name": "Kotc",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afecto1"
},
"sink": {
"name": "Afecto1",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afecto2"
},
"sink": {
"name": "Afecto2",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afecto3"
},
"sink": {
"name": "Afecto3",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afecto4"
},
"sink": {
"name": "Afecto4",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afevari"
},
"sink": {
"name": "Afevari",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Empresa"
},
"sink": {
"name": "Empresa",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Diaslim"
},
"sink": {
"name": "Diaslim",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Subsil"
},
"sink": {
"name": "Subsil",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Fafp"
},
"sink": {
"name": "Fafp",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Fecha_dep"
},
"sink": {
"name": "Fecha_dep",
"type": "DateTime",
"physicalType": "smalldatetime"
}
},
{
"source": {
"name": "BancoDep"
},
"sink": {
"name": "BancoDep",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "NroRec"
},
"sink": {
"name": "NroRec",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Fecha_pag"
},
"sink": {
"name": "Fecha_pag",
"type": "DateTime",
"physicalType": "smalldatetime"
}
},
{
"source": {
"name": "Jdd"
},
"sink": {
"name": "Jdd",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Dias3pd"
},
"sink": {
"name": "Dias3pd",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Fecha_ult"
},
"sink": {
"name": "Fecha_ult",
"type": "DateTime",
"physicalType": "smalldatetime"
}
},
{
"source": {
"name": "SubsilN"
},
"sink": {
"name": "SubsilN",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Afevar2"
},
"sink": {
"name": "Afevar2",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Licmd"
},
"sink": {
"name": "Licmd",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "BancoPag"
},
"sink": {
"name": "BancoPag",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "efecDep"
},
"sink": {
"name": "efecDep",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Fecha_eje"
},
"sink": {
"name": "Fecha_eje",
"type": "DateTime",
"physicalType": "smalldatetime"
}
},
{
"source": {
"name": "Mondep"
},
"sink": {
"name": "Mondep",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "User_eje"
},
"sink": {
"name": "User_eje",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Kfal"
},
"sink": {
"name": "Kfal",
"type": "Int32",
"physicalType": "int"
}
},
{
"source": {
"name": "Kper"
},
"sink": {
"name": "Kper",
"type": "Int32",
"physicalType": "int"
}
}
],
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false,
"dateTimeFormat": "\"YYYY-MM-DD hh:mm:ss\""
}
}
},
"inputs": [
{
"referenceName": "Dataset_Blob_to_SQL",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Dataset_to_SQL_RESUMEN",
"type": "DatasetReference"
}
]
},
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [
{
"activity": "Copy_SFTP_to_BLob",
"dependencyConditions": [
"Succeeded"
]
}
],
"policy": {
"timeout": "7.00:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "DelimitedTextSource",
"storeSettings": {
"type": "AzureBlobStorageReadSettings",
"recursive": false,
"wildcardFileName": "RESUMEN*",
"enablePartitionDiscovery": false
},
"formatSettings": {
"type": "DelimitedTextReadSettings"
}
},
"sink": {
"type": "SqlServerSink"
},
"enableStaging": false,
"dataIntegrationUnits": 2,
"translator": {
"type": "TabularTranslator",
"mappings": [
{
"source": {
"name": "Codigo_",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Codigo",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ames",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ames",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Peri",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Peri",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Periodo",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Periodo",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Origen",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Origen",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Neto",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Neto",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktha",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktha",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Kthn",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Kthn",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "KthI",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "KthI",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktim",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktim",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktdl",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktdl",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "KtdlO",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "KtdlO",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktde",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktde",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktds",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktds",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktfm",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktfm",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Ktex",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Ktex",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "DiasTr",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "DiasTr",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "DiasLi",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "DiasLi",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "TramoCar",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "TramoCar",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Coprev",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Coprev",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Cod_Reg",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Cod_Reg",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Kcay",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Kcay",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Khij",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Khij",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Kotc",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Kotc",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afecto1",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afecto1",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afecto2",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afecto2",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afecto3",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afecto3",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afecto4",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afecto4",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afevari",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afevari",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Empresa",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Empresa",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Diaslim",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Diaslim",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Subsil",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Subsil",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Fafp",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Fafp",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Fecha_dep",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Fecha_dep",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "BancoDep",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "BancoDep",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "NroRec",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "NroRec",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Fecha_pag",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Fecha_pag",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Jdd",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Jdd",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Dias3pd",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Dias3pd",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Fecha_ult",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Fecha_ult",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "SubsilN",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "SubsilN",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Afevar2",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Afevar2",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Licmd",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Licmd",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "BancoPag",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "BancoPag",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "efecDep",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "efecDep",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Fecha_eje",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Fecha_eje",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Mondep",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Mondep",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "User_eje",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "User_eje",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "Kfal",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Kfal",
"type": "String",
"physicalType": "nvarchar"
}
},
{
"source": {
"name": "_Kper",
"type": "String",
"physicalType": "String"
},
"sink": {
"name": "Kper",
"type": "String",
"physicalType": "nvarchar"
}
}
],
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false
}
}
},
"inputs": [
{
"referenceName": "Dataset_Blob_to_Blob",
"type": "DatasetReference"
}
],
"outputs": [
{
"referenceName": "Dataset_to_SQL_RESUMEN",
"type": "DatasetReference"
}
]
}
],
"annotations": [],
"lastPublishTime": "2021-03-26T19:11:22Z"
},
"type": "Microsoft.DataFactory/factories/pipelines"
}



image.png (16.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.