0

I want to build a dynamic script component as source and destionation for multible tables. I just started by sample at Microsoft Page. It is working for specific table but now need to convert it to dynamic for all tables but need your helps.

First I thought to generate 'MyAddressOutputBuffer.Column' part dynamicly by below sql query.

   SqlCommand cmdMeta = new SqlCommand(@"
   select ColumnMapping = ' MyAddressOutputBuffer.Column'+cast(RANK() OVER(ORDER BY c.name)-1 as varchar(5))+' =  sqlReader.GetValue(' + cast(RANK() OVER(ORDER BY c.name) - 1 as varchar(5)) + ').ToString();' 
   from sys.tables t
   inner join sys.columns c on t.object_id = c.object_id
   where t.name = 'Address'", sqlConn);
   sqlReaderMeta = cmdMeta.ExecuteReader();

Above code give expected result but not sure how replace with hardcoded mapping with below part : 

public override void CreateNewOutputRows()  
{  

    while (sqlReader.Read())  
    {  
        {  
            MyAddressOutputBuffer.AddRow();  
            MyAddressOutputBuffer.AddressID = sqlReader.GetValue(0).ToString();  // I use getVAlue for all data types.
            MyAddressOutputBuffer.City = sqlReader.GetValue(1).ToString();  
        }  
    }  

}

Is there any way to say C# to run dynamic query which return from above sql instead of hardcoded?

Can Aslan
  • 53
  • 2
  • 11
  • I learned need to use `Eval()` but still doesn't have much idea about how use it. – Can Aslan Apr 27 '17 at 07:50
  • In principle you could use reflection and do something like this: `int i = 0; foreach (var p in x.GetType().GetProperties()) { p.SetValue(x, sqlReader.GetValue(i++)); } ` But this creates a lot of problems . What exactly are you trying to accomplish? – Fernando Sibaja Apr 28 '17 at 04:04
  • I want to read from sql and insert into another database. – Can Aslan Apr 28 '17 at 05:29
  • I solved Source part of Script component and now working an destination part. but couldn't map dynamically. ı updated below part like this but instead of getting value of column, it just show 'Row.Column' as string.`int columnIndex = 0; public string ColumnCount(int x) { string rowColumn = null; if (x < maxColumnCount) { rowColumn = "Row.Column" + x; } else{ rowColumn = "Row.Column" + 0; } return rowColumn; } public override void Input0_ProcessInputRow(Input0Buffer Row) { { sqlCmd.Parameters["@Column0"].Value = ColumnCount(0); .. sqlCmd.ExecuteNonQuery(); }}` – Can Aslan Apr 28 '17 at 05:38
  • I suggest using other approach. Why do the source and destination need to be dynamic? Also, if the 2 tables are in the same server, why not simply using a sql statement?. – Fernando Sibaja Apr 28 '17 at 13:36
  • i want to use this approach for all tables because of that i want dynamic. I also did destination part dyamic by this code `sqlCmd.Parameters[ColumnCount(0)].Value = Row.Column0;` ColumnCount(0) = ColumnCount is a method which send # of columns. I send 0,1,2,... numbers by this method till maximum column # that i have defined for script component as input. and it map according to how many columns i have. Now i have last thinkg, it is inserting Empty instead of NULL. After solved it, my etl will be ready to use for 1 time tasks. I believe it will save much time for me. – Can Aslan May 08 '17 at 13:38

0 Answers0