I have populated a list in C# script and assigned its value to SSIS object variable.
Then I used that object variable to execute some SQL query by looping through For each do enumerator.
I tried doing this by Foreach ado enumerator but getting error
X variable doesn't contain a valid data object.
Can anybody provide any inputs.
Youre using a list. Not a recordset and therefore you need to enumerate over a variable.
If you want to use ADO Recordset, you need to fill a datatable instead.
This shows you how to write to object with a variable list
This shows you how to write to object with recordset (using multiple values)
Like this:
1 .C# Script code - Write to Object with list using variable enumerator
public void Main()
{
// TODO: Add your code here
List<string> NewList = new List<string>();
NewList.Add("Ost");
NewList.Add("Hest");
Dts.Variables["User::NameList"].Value = NewList;
Dts.TaskResult = (int)ScriptResults.Success;
}
1. Variable settings in ssis
1. Foreach loop container settings
Use Foreach Variable Enumerator and use your object variable
Map your outcome to a variable(s)
1. Execute SQL Task test case
Write your SQL with variables
Map your variable to Parameter mapping
1. Result
2. C# Script code - Write to object with datatable using ADO enumerator
public void Main()
{
// TODO: Add your code here
DataTable dt = new DataTable();
dt.Columns.Add("FilmName",typeof(string));
dt.Columns.Add("ActorName",typeof(string));
dt.Rows.Add("Starwars", "Harrison ford");
dt.Rows.Add("Pulp fiction", "Samuel Jackson");
Dts.Variables["User::NameList"].Value = dt;
Dts.TaskResult = (int)ScriptResults.Success;
}
2. Variable settings in ssis
2. Foreach loop container settings
Use Foreach ADO Enumerator and your object as variable
Map your outcome to variable(s)
2. Execute sql task test case
Write your SQL with variables
Map your variable(s) to Parameter mapping
2. Result
Thanks @plaidDK Second approch solved my problem
2.C# Script code - Write to object with datatable using ADO enumerator
Instead of list I have populated data table:
public DataTable ToDataTable<T>(List<T> items)
{
DataTable dataTable = new DataTable(typeof(T).Name);
//Get all the properties by using reflection
PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (PropertyInfo prop in Props)
{
//Setting column names as Property names
dataTable.Columns.Add(prop.Name);
}
foreach (T item in items)
{
var values = new object[Props.Length];
for (int i = 0; i < Props.Length; i++)
{
values[i] = Props[i].GetValue(item, null);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
//Variable passed as below Variables.vFailedTransactionNo = dt;
ANd then ado enumerator done rest of the job.
Thanks for help!