May 11, 2023 at 2:56 pm
I am writing an SSIS package that interacts with azure blob storage. To make life easier I'm using a 3rd party add on called ZappySys. The Zappysys task is set up to gather details of the files stored in an azure blob storage container and put those details in to an ADO.Net object variable (basically a table). I now want to get the data out of that variable and in to a SQL table. I'm wondering if there's a direct way to write data from variable to table? I think I could probably set up a foreach loop and for each row in the table I write that data to variable and then write those variables to the table, but I was hoping there was a more direct way that didn't require a loop. I basically have the data in a table already and now I want to write that data to a SQL table. Any ideas on how I can accomplish this?
May 11, 2023 at 7:47 pm
I am not sure about SSIS but, assuming your object is a DataTable, try SqlBulkCopy:
https://www.howtosolutions.net/2016/07/dotnet-save-datatable-into-database-table/
May 11, 2023 at 8:56 pm
If you put that data into a recordset object in SSIS - you can then use that object in a foreach loop to insert into the table. You still have to loop over each row in the object and insert the data into a table.
The other option is to use the recordset object as a source in a data flow. To do that, you would use a script component as the source and map each field in the recordset to an output field from the script component and then send that to a OLEDB destination.
https://www.timmitchell.net/post/2015/04/20/using-the-ssis-object-variable-as-a-data-flow-source/
You may be able to use the ADO.NET object directly in the script component instead of using a recordset object.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2023 at 9:12 pm
Thanks for these ideas! I have it working in a foreach loop, but I'd like to try the second option that you mentioned. I would think that it would be faster to not have to do the loop.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply