SSIS Scripting Component: Get child records for creating Object
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 an开发者_Python百科y 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 .
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.
精彩评论