I am trying to use Get Metadata activity in Azure Data Factory in order to get blob filenames and copy them to Azure SQL database table. I added the stored procedure activity after Get Metadata activity. Here is my new activity stored procedure, I added the parameter as suggested however, I changed the name to JsonData as my stored procedure requires this parameter.
This is my stored procedure.
/****** Object: StoredProcedure [dbo].[InsertDataJSON] Script Date: 10/14/2020 11:01:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*USE datafactorypoc1*/
ALTER PROCEDURE [dbo].[InsertDataJSON] (
@JsonData NVARCHAR (MAX)
)
AS
BEGIN
DECLARE @err int
INSERT INTO extractFileNameTest1 (ItemName, ItemType, EffIntegrationRunTieme, ExecutionDuration, DurationInQueue)
SELECT ItemName, ItemType, EffIntegrationRunTieme, ExecutionDuration, DurationInQueue
FROM OPENJSON (@JsonData, N'$') WITH (
ItemName VARCHAR(255) N'$.ItemName',
ItemType VARCHAR(255) N'$.ItemType',
EffIntegrationRunTieme VARCHAR(255) N'$.EffIntegrationRunTieme',
ExecutionDuration INT N'$.ExecutionDuration',
DurationInQueue INT N'$.DurationInQueue'
)
SELECT @err = @@ERROR
RETURN (@err)
END
I get this error at the stored procedure:
{
"errorCode": "2402",
"message": "Execution fail against sql server. Sql error number: 13609. Error Message: JSON text is not properly formatted. Unexpected character 'S' is found at position 0.",
"failureType": "UserError",
"target": "Stored procedure1",
"details": []
}
But when I check the input, it seems like it already successfully reading the json string itemName.
But, when I check output, it's not there.
Could you please help me check what I did wrong here? Is it my stored procedure? Thank you very much in advance.
Update 15/10/2020 I created a new pipeline and move the Stored Procedure inside ForEach activity to connect to Get_Filename_2 instead of the first one ad followed:
Here I changed the value of parameter to Get_Filename_2 and output as itemName instead of childitems (because I got an error with using childitems because childitems is from Get_Filename_1 and not 2).
After executing the pipeline (which was failed), the input of the last stored procedure is:
{
"storedProcedureName": "[dbo].[InsertDataJSON]",
"storedProcedureParameters": {
"JsonData": {
"value": "FRRNSC84FIN1_A2276801_20200103-152534.json",
"type": "String"
}
}
}
And the output is:
{
"effectiveIntegrationRuntime": "DefaultIntegrationRuntime (West Europe)",
"executionDuration": 0,
"durationInQueue": {
"integrationRuntimeQueue": 0
},
"billingReference": {
"activityType": "ExternalActivity",
"billableDuration": [
{
"meterType": "AzureIR",
"duration": 0.016666666666666666,
"unit": "Hours"
}
]
}
}
For this pipeline, it's failed with following error message.
{
"errorCode": "2402",
"message": "Execution fail against sql server. Sql error number: 13609. Error Message: JSON text is not properly formatted. Unexpected character 'F' is found at position 0.\r\nFRRNSC84FIN1_A2276801_20200103-152534.json",
"failureType": "UserError",
"target": "Stored procedure1",
"details": []
}
On my old pipeline where I have stored procedure outside of ForEach loop. The pipeline did not fail:
Here is the input of the last stored procedure activity:
{
"storedProcedureName": "[dbo].[InsertDataJSON]",
"storedProcedureParameters": {
"JsonData": {
"value": "[{\"name\":\"FRRNSC84FIN1_A2274001_20200103-143748_back_camera_0.jpeg\",\"type\":\"File\"},{\"name\":\"FRRNSC84FIN1_A2274001_20200103-143748_right_camera_0.jpeg\",\"type\":\"File\"},{\"name\":\"FRRNSC84FIN1_A2274801_20200103-144811_right_camera_0.jpeg\",\"type\":\"File\"},{\"name\":\"FRRNSC84FIN1_A2275201_20200103-145229_right_camera_0.jpeg\",\"type\":\"File\"},{\"name\":\"FRRNSC84FIN1_A2276801_20200103-152534.json\",\"type\":\"File\"}]"
}
}
}
And here is the output:
{
"effectiveIntegrationRuntime": "DefaultIntegrationRuntime (West Europe)",
"executionDuration": 0,
"durationInQueue": {
"integrationRuntimeQueue": 0
},
"billingReference": {
"activityType": "ExternalActivity",
"billableDuration": [
{
"meterType": "AzureIR",
"duration": 0.016666666666666666,
"unit": "Hours"
}
]
}
}
This pipeline ran successfully however the result in the SQL is not what I really expected, also many NULL values before the first json string but it insert all itemName in the same location. This is because it's outside of ForEach loop I think. But why it insert so many NULL?
Here is my stored procedure that I modified:
/****** Object: StoredProcedure [dbo].[InsertDataJSON] Script Date: 15/10/2020 10:31:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*USE datafactorypoc1*/
ALTER PROCEDURE [dbo].[InsertDataJSON] (
@JsonData NVARCHAR (MAX)
)
AS
BEGIN
PRINT @JsonData
/*INSERT INTO Logs values (DEFAULT, @JsonData)*/
INSERT INTO extractFileNameTest1 values (@JsonData, DEFAULT)
SELECT * FROM
OPENJSON (@JsonData)
WITH (
ItemName VARCHAR(255) N'$.ItemName',
ItemType VARCHAR(255) N'$.ItemType'
)
END
I tried to delete all data in my test SQL table to see the output of the 2nd fail pipeline. I notice that it did parse correctly the json string filename inside my table and all of them (I only have 5 files in blob storage). But other data are NULL.
To conclude, the result in SQL table of the new pipeline is much better, each file name on the different cell of the table but I got pipeline run failed. Could you please help me check what I did wrong here? Is it the stored procedure or the expression in my last Stored procedure activity inside ForEach loop?
Thank you very much in advance.
Get_File_Name1.output.childtiems
) and using "Get_File_Name2" to get the fileitem name
. Then the stored procedure should connect to the "Get_File_Name2" not after foreach. – Leon Yuenull
at my side too. Still need time to figure out it. – Leon Yue