topic Re: Store Activity Data into a Table in Pipelines

Store Activity Data into a Table

jpelham — Wed, 07 May 2025 12:35:35 GMT

I am wanting to store the data collected from the Get Metadata activity into a lakehouse table, along with the Copy Data activity output for each table loaded in the ForEach activity. I have tried using variables of type array, and have now been testing out system variables. I haven't had any luck with figuring it out so far. Does anyone have any suggestions?

Re: Store Activity Data into a Table

Anonymous — Wed, 07 May 2025 13:26:43 GMT

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;

Re: Store Activity Data into a Table

spencer_sa — Thu, 08 May 2025 20:59:59 GMT

If you explicitly want the Get Metadata activity data in a table, then it is possible to have a notebook with a string input parameter, feed the activity output into the parameter of the Notebook Activity, and then parse the json in python and write the subsequent output to a delta table using pyspark.

Re: Store Activity Data into a Table

jpelham — Fri, 09 May 2025 15:23:49 GMT

Thanks! I will give that a try!

Re: Store Activity Data into a Table

jpelham — Fri, 09 May 2025 15:32:57 GMT

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.

Re: Store Activity Data into a Table

spencer_sa — Fri, 09 May 2025 19:08:52 GMT

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 lakehouse

metadata_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.

Re: Store Activity Data into a Table

v-sathmakuri — Tue, 13 May 2025 13:23:55 GMT

Hi jpelham ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you spencer_sa and Anonymous for the response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

Re: Store Activity Data into a Table

v-sathmakuri — Fri, 16 May 2025 11:47:53 GMT

Hi jpelham ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

Re: Store Activity Data into a Table

v-sathmakuri — Tue, 20 May 2025 17:42:04 GMT

Hi jpelham ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!