Archive
Posts Tagged ‘List collection’
SSIS – Read C# ‘List’ collection in ‘Execute SQL Task’
In one of our integration requirements, we had to read the ‘List<>’ collection values, which was set in ‘Script Task’ control and pass to ‘Execute SQL Task’ control.
In this article I am going to list out the step by step process to set and get the ‘List<>’.
Assume that you have a collection List<string> which holds ‘Product Names’
var listProducts = new List<string>() {“Product1″, ” Product2″, ” Product3″};
Lets see how to loop through and read the ‘Product names’ and pass to ‘Execute SQL Task’.
Required SSIS components:
- A SSIS ‘Variable’ (i.e., varProducts) of type ‘Object’ to set the ‘List<>’ object in ‘Script Task’ control
- A SSIS ‘Variable’ (i.e., varProductName) of type ‘String’ to set the ‘Product Name’ for each List<> value.
- ‘Foreach Loop container’ to parse and read the ‘List<>’
Steps:
- Choose ‘varProducts’ as ‘ReadWriteVariables’
- In ‘ScriptTask’ set ‘listProduct’ object to ‘varProducts’ variable.
- var listProducts = new List<string>() {“Product1″, ” Product2″, ” Product3″};
- Dts.Variables[“User:: varProducts”].Value = listProducts ;
- In ‘Foreach Loop container’,
- Choose ‘Enumerator’ as ‘Foreach From Variable Enumerator’
- Enumerator Variable as ‘User:varProducts’
- As the ‘List’ contain ‘Product Name’ collection, read the ‘Product Name’ to ‘varProductName’ variable. ‘Index’ 0 denotes, first value in collection (i.e., Product Name’.
- As the final step, In ‘Execute SQL Task’, map the ‘varProductName’ variable to a ‘Parameter’.
- In above screen, we are setting ‘@prodName’ parameter with ‘Product Name’ and ‘@prodName’ further used in ‘SQL Statement’ of ‘Execute SQL Task.
🙂
Categories: SQL
Tags: List collection, SSIS
Stats
- 1,559,519 hits
Tweets
- New blog post for Power Apps Beginners : Canvas Apps | Options to store configuration data rajeevpentyala.com/2023/02/06/beg… 1 hour ago
- RT @ManuelaPichler_: I suffered a concussion last week, and I'm so glad to see this video is actually REAL and not just something my brain… 1 week ago
- RT @TaikiYoshidaEN: Awesome to see we have a shiny new public facing website for Power CAT! 😻😻😻 microsoft.github.io/powercat/ #PowerApps #PowerA… 1 week ago
- RT @ravichada: 🎥 The demo video is ready now. Do check out the Dataverse Security Roles risk assessment tool in action. Leave a comment or… 1 week ago
- RT @ManuelaPichler_: CoE kit but make it more fluent-y #comingsoon https://t.co/kcSzPqwZhM 1 week ago
Join 400 other subscribers
Top Posts
- Power Apps component framework (PCF) - Beginner guide
- Power Automate Flows | 'Callback Registration Expander' System Jobs stuck at 'Waiting For Resources'
- Associate/Disassociate plugin messages in CRM
- [Code Snippet] Custom Workflow Activity with Input and Output Params
- [Step by Step] Postman tool with Microsoft Dataverse Web API
- Auto generate new GUID for ‘uniqueidentifier’ column in SQL Table
- God Mode - Level Up - Dynamics 365 Chrome Extension
- Canvas App -Working with Bing Maps connector
- Power Apps - 'Environment Variables' and their usage in Canvas Apps
- [Step by Step] Power Apps | Show pop ups in Canvas App