Forum Discussion
Store Activity Data into a Table
The following works;
1) Feed the output of the Get MetaData activity into a parameter of a Notebook Activity@string(activity('Get Metadata1').output.childItems)
2) Have a Notebook similiar to the following - attach it to a lakehousemetadata_json = json.loads(metadata)
df = spark.createDataFrame(metadata_json)
df.write.mode('overwrite').saveAsTable('get_metadata')3) When the pipeline is run, this is what gets output;
If this helps, please consider Accepting as a Solution to help others find it more easily.
Ive stored web activity output (which is basically the same) in a warehouse before using the script activity.
It is kinda hacky though, so a proper way would definitely be better, a stored procedure should work as well i think and be a bit cleaner already but i havent tried it
Heres example code for the script activity:
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @JsonData NVARCHAR(MAX) = '@{variables('
data')}';
-- Replace the pipeline expression with the actual value at runtime
SET @DynamicSQL = '
IF ISJSON(''' + REPLACE(@JsonData, '''', '''''') + ''') = 1
BEGIN
INSERT INTO ApiDataTable (RowId, RandomNumber, RandomString, RandomDate)
SELECT
randomId,
randomNumber,
randomString,
randomDate
FROM OPENJSON(''' + REPLACE(@JsonData, '''', '''''') + ''')
WITH (
randomId VARCHAR(100) ''$.randomId'',
randomNumber VARCHAR(100) ''$.randomNumber'',
randomString VARCHAR(100) ''$.randomString'',
randomDate VARCHAR(100) ''$.randomDate''
);
SELECT CONCAT(''Successfully inserted '', @@ROWCOUNT, '' rows into ApiDataTable'') AS Result;
END
ELSE
BEGIN
SELECT ''Error: Invalid JSON data. Please check the format.'' AS Result;
END';
EXEC sp_executesql @DynamicSQL;Thanks for the suggestion! I tried doing a stored procedure and it wasn't quite working how I had hoped and it was getting kind of messy to work with. I'll give this a try if the other solution doesn't work.
