Enumerating RecordSet in DataFlow Script Component as a Data Source

Question

This is an SSIS related issue.

I have a variable for which an object type is set. One Dataflow imports some filtered rows into a recordset, and this recordset is stored in an object variable.

In a completely separate data stream, I need to use this recordset as a source. So I created a script component and said that it would be a data source.

I set it to have three output columns that I need. My problem is, how can I get every row in a recordset to create a new row in a script component?

I passed in a record set variable as a readonly variable, when I try to make a variable available for each row, I cannot do this because the variable does not define the get enumerator method.

Therefore, I cannot print every row in these columns and cannot use my script component as a data source.

Has anyone else encountered a similar situation? Am I doing something stupid or did you do it in another alternative way?

As a note, I use C # in script and Visual studio 2008

Answer 1

So, I looked around a bit and found a solution to VB for my problem, translated it into C #, and now it compiles and behaves as expected. The code I used was as follows:

DataTable datatable = new DataTable(); System.Data.OleDb.OleDbDataAdapter oAdapter = new System.Data.OleDb.OleDbDataAdapter(); oAdapter.Fill(datatable,ReadOnlyVariables["User::XXXXX"]); foreach (DataRow row in datatable.Rows) { Output0Buffer.AddRow(); Output0Buffer.CoverAmount = Convert.ToInt32(row["XXXX"].ToString()); } 

for everyone who faces a similar problem!

Thank you all for your help.

Answer 2

I'm doing something similar, based on an older version of Andy Leonard's Infrastructure Download . Our child packages fill out a record set indicating the number of new, changed, unchanged, etc. Rows In the parent package, we check this object to make sure it has been populated before we use it. I need to skip the meeting, so I apologize for this code, since it does not completely solve your specific need, but I hope it provides a reliable push in the right direction until I return and come up with your case. I have pseudo code where you want to do something.

  public void Main() { bool debug = Convert.ToBoolean(Dts.Variables["Debug"].Value); string taskName = string.Empty; string packageName = string.Empty; string sourceName = string.Empty; bool fireAgain = false; taskName = Convert.ToString(Dts.Variables["TaskName"].Value); packageName = Convert.ToString(Dts.Variables["PackageName"].Value); // Fix this by defining and passing in params sourceName = Convert.ToString(Dts.Variables["TaskName"].Value); System.Data.OleDb.OleDbDataAdapter adapater = null; System.Data.DataTable table = null; System.Data.DataColumn column = null; System.Data.DataRow row = null; string message = string.Empty; object rowCounts = null; rowCounts = Dts.Variables["RowCounts"].Value; table = new DataTable(); try { // Get us out of this crazy thing - should only be an issue // first pass through if (rowCounts == null) { Dts.TaskResult = (int)ScriptResults.Success; return; } } catch (Exception ex) { throw new Exception("Failed here"); } adapater = new System.Data.OleDb.OleDbDataAdapter(); try { // This works if we pass in a dataset //adapater.Fill(table, Dts.Variables["RowCounts"].Value); adapater.Fill(table, rowCounts); // TODO: Enumerate through adapter // Call Output0Buffer.AddRow(); // and Output0Buffer.MyColumn.Value = adapter[i].value // possibly casting to strong type } catch (Exception ex) { try { // This works if we use a datatable System.Data.DataSet ds = null; //ds = (DataSet)Dts.Variables["RowCounts"].Value; ds = (DataSet)rowCounts; table = ds.Tables[0]; // TODO: Enumerate through datatable as we do with adapter } catch (Exception innerException) { // continue to swallow exceptions } Dts.Variables["ValidCounts"].Value = false; // trap "Object is not an ADODB.RecordSet or an ADODB.Record // parse ex.Message if (ex.Message.Contains("System.ArgumentException: ")) { System.Text.StringBuilder exceptionMessage = null; exceptionMessage = new System.Text.StringBuilder(); exceptionMessage.Append(ex.Message); exceptionMessage.Replace("\nParameter name: adodb", string.Empty); exceptionMessage.Replace("System.ArgumentException: ", string.Empty); if (exceptionMessage.ToString() != "Object is not an ADODB.RecordSet") { Dts.Events.FireInformation(0, string.Format("{0}.{1}", packageName, taskName), exceptionMessage.ToString(), string.Empty, 0, ref fireAgain); } } } Dts.Variables["ValidCounts"].Value = false; if (table.Rows.Count > 0) { Dts.Variables["ValidCounts"].Value = true; } if (debug) { message = string.Format("SourceName: {0}\nValidCounts: {1}", sourceName, false); //System.Windows.Forms.MessageBox msgBox = null; //msgBox = new MessageBox(); System.Windows.Forms.MessageBox.Show(message, string.Format("{0}.{1}", packageName, taskName)); //MessageBox(message, string.Format("{0}.{1}", packageName, taskName)); } Dts.TaskResult = (int)ScriptResults.Success; } 

Answer 3

I am working on the same issue as OP. In my case, I assumed that this is an object, and I spent a lot of time converting it to datatable. I found that the object was already datatable, so from SSIS Script Task Component I was able to write this:

 DataTable dt = (DataTable)ReadOnlyVariables["User::FTP_DataPath_File_Metadata"].Value; foreach (DataRow row in dt.Rows) { CustomOutputBuffer.AddRow(); CustomOutputBuffer.FileName = row.ItemArray[0].ToString(); CustomOutputBuffer.FileLastModified = Convert.ToDateTime(row.ItemArray[1]); CustomOutputBuffer.FileSize = Convert.ToInt32(row.ItemArray[2]); } 

This successfully converted my "object variable" into a data stream, using the Script Component as the source.

This example was run to host the FTP Task Task Protected FTP component, which saves the "Get a list of metadata files" results into an object variable.