SSIS Scripting Component: Get child records for creating Object

Question

Got it working - Posted My solution below but will like to know if there is better way

Hello All

I am trying to create Domain Event for a newly created (after migration) domain object in my database.

for Objects without any internal child objects it worked fine by using Script Component. The problem is in how to get the child rows to add information to event object.

Ex. Customer-> Customer Locations.

I am creating Event in Script Component- as tranformation- (have reference to my Domain event module) and then creating sending serialized information about event as a column value. The input rows currently provide data for the parent object.

Please advise.

Regards,

The Mar

Edit 1

I would like to add that current I am doing processsing in

public override void Input0_ProcessInputRow(Input0Buffer Row)

I am looking for something like create a a data reader in this function

loop through data rows -> create child objecta nd add it to parent colelction

Still on google and PreExecute and ProcessInput Seems something to look at .

Answer 1

This is my solution. I am a total newbie in SSIS , so this may not be the best solution.

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connectionManager;
    SqlCommand cmd = null;
    SqlConnection conn = null;
    SqlDataReader reader = null;


    public override void AcquireConnections(object Transaction)
    {

        try
        {
            connectionManager = this.Connections.ScriptConnectionManager;
            conn = connectionManager.AcquireConnection(Transaction) as SqlConnection;

            // Hard to debug failure-  better off logging info to file
            //using (StreamWriter outfile =
            //    new StreamWriter(@"f:\Migration.txt"))
            //{
            //    outfile.Write(conn.ToString());
            //    outfile.Write(conn.State.ToString());
            //}
        }
        catch (Exception ex)
        {
            //using (StreamWriter outfile =
            //    new StreamWriter(@"f:\Migration.txt"))
            //{
            //    outfile.Write(" EEEEEEEEEEEEEEEEEEEE"+ ex.ToString());
            //}
        }




    }


    public override void PreExecute()
    {
        base.PreExecute();

        cmd = new SqlCommand("SELECT [CustomerLocation fields] FROM customerlocationView where custid=@CustId", conn);
        cmd.Parameters.Add("CustId", SqlDbType.UniqueIdentifier);

    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Collection<CustomerLocation> locations = new Collection<CustomerLocation>();
        cmd.Parameters["CustId"].Value = Row.id;

        // Any error always  saw that reader reamians open on connection
        if (reader != null)
        {
            if (!reader.IsClosed)
            {
                reader.Close();
            }
        }

        reader = cmd.ExecuteReader();

        if (reader != null)
        {
            while (reader.Read())
            {
                // Get Child Details
                var customerLocation = new CustomerLocation(....,...,...,);
                customerLocation.CustId = Row.id;
                locations.Add(customerLocation);
            }



        }






        var newCustomerCreated = new NewCustomerCreated(Row.id,,...,...,locations);

        var serializedEvent = JsonConvert.SerializeObject(newCustomerCreated, Formatting.Indented,
                                                                    new JsonSerializerSettings { TypeNameHandling = TypeNameHandling.Objects, ReferenceLoopHandling = ReferenceLoopHandling.Ignore });

        Row.SerializedEvent = serializedEvent;
        Row.EventId = newCustomerCreated.EventId;
        ...
        ...
        ...
        ....
        ..
        .
        Row.Version = 1;



       // using (StreamWriter outfile =
        //       new StreamWriter(@"f:\Migration.txt", true))
       // {
       //     if (reader != null)
         //   {
         //       outfile.WriteLine(reader.HasRows);
            //outfile.WriteLine(serializedEvent);
          //  }
           // else
          //  {
          //      outfile.Write("reader is Null");
          //  }
        //}
        reader.Close();
    }



    public override void ReleaseConnections()
    {
        base.ReleaseConnections();
        connectionManager.ReleaseConnection(conn);
    }
}

One thing to note is that a different approach to create connection is to get the connection string from connectionManager and use it to create OLEDB connection.