Mr Shaa Mr Shaa - 3 years ago 237
C# Question

How to insert Ticket Number(lookup field) to MS Dynamics CRM Pricing Approval Products(Custom entity) using ssis's script component

I need to create or update Ticket Number lookup field in Pricing Approval Products. This ticket number(lookup field) is linked to Ticket(text field) in another entity named as Pricing Approval Tickets.

This is my code in script component.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{

Entity ApprovalProductEnt = new Entity("new_ticketproduct");
ColumnSet columns = new ColumnSet(true);
columns = new ColumnSet(new String[] { "new_ticket",
"new_productgroup",
"new_producttype",
"new_productitem" });

Guid TicketNumberId = new Guid();
TicketNumberId = getTicketNumber(Row.OppportunityID, ref organizationservice);

//update
if (TicketNumberId != Guid.Empty)
{
ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
}
else
//create
if (TicketNumberId != Guid.Empty)
{
ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
}
}

public Guid getTicketNumber(string ticketnumber, ref IOrganizationService service)
{
Guid TicketNumberGuid = Guid.Empty;
QueryExpression TicketNumberQuery = new QueryExpression { EntityName = "new_pricingapprovalticket", ColumnSet = new ColumnSet(true) };
TicketNumberQuery.Criteria.AddCondition("new_ticketnumber", ConditionOperator.Equal, ticketnumber);
EntityCollection TicketNumberQueryRetrieve = service.RetrieveMultiple(TicketNumberQuery);

for (var i = 0; i <TicketNumberQueryRetrieve.Entities.Count; i++)
{
TicketNumberGuid = TicketNumberQueryRetrieve.Entities[0].GetAttributeValue<Guid>("new_ticket");
}

return TicketNumberGuid;
}


When I start the SSIS package, Its run without error. But somehow there is no data inserted.

enter image description here

I have no problem to create a normal text field. But when it come to lookup field, internet solutions suggest to use EntityReference as per my code, but I have NO idea what is wrong. So I am a bit lost here.

Answer Source

Lookup is a relationship column (Foreign key) of related entity. You should check if there is a master record available to map the key or create the record of that type to map.

ColumnSet is not needed in first method while creating entity record. But you should map the columns from source file/table Rows. ColumnSet is useful like in second method while retrieving dataset.

For loop is unnecessary in 2nd method.

Just put the missing pieces in your snippet for you to understand, this can be improvised.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Entity ApprovalProductEnt = new Entity("new_ticketproduct");

    ApprovalProductEnt["new_productgroup"] = Row.ProductGroup;
    ApprovalProductEnt["new_producttype"] = Row.ProductType;
    ApprovalProductEnt["new_productitem"] = Row.ProductItem;

    Guid TicketNumberId = new Guid();
    TicketNumberId = getTicketNumber(Row.OppportunityID, ref organizationservice);

    //update
    if (TicketNumberId != Guid.Empty)
    {
        ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
    }
    else
    //create
    {
        Entity TicketEnt = new Entity("new_pricingapprovalticket");
        TicketEnt["new_name"] = Row.OppportunityID;
        TicketNumberId = organizationservice.Create(TicketEnt);

        ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
    }

   organizationservice.Create(ApprovalProductEnt);
}


public Guid getTicketNumber(string ticketnumber, ref IOrganizationService service)
{
    Guid TicketNumberGuid = Guid.Empty;
    QueryExpression TicketNumberQuery = new QueryExpression { EntityName = "new_pricingapprovalticket", ColumnSet = new ColumnSet(true) };
    TicketNumberQuery.Criteria.AddCondition("new_ticketnumber", ConditionOperator.Equal, ticketnumber);
    EntityCollection TicketNumberQueryRetrieve = service.RetrieveMultiple(TicketNumberQuery);

if (TicketNumberQueryRetrieve.Entities.Count > 0)
    TicketNumberGuid = TicketNumberQueryRetrieve.Entities[0].GetAttributeValue<Guid>("new_ticket");

return TicketNumberGuid;
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download