Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Converting (AS400 Y2K date) string to date format and pushing to CRM programatically as a date field via SSIS C# script RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I am trying to get records from an AS400 system into Dynamics CRM programatically. To achieve this i have pushed the AS400 records into a SQL table and am able to push those records to CRM by referencing the CRM 4 web service endpoints in a SSIS 2008 C# script.

    The problem is one of the fields is in Y2K date string format. In order to get it into a date field (D.O.B) in CRM i believe i will need to convert it to a date format then reference resulting value in a variable.

    I do not know how to do this.

    This question/answer (http://stackoverflow.com/a/4880021/1326443) may help with part of the question but i do not know how to use this into my script to get a value (haven't done any scripting for a number of years and new to C#)

    Script snippet:

        public class ScriptMain : UserComponent
        {
        private CrmService service = null;


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


            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = "DevOrg";

            service = new CrmService();
            service.Url = "http://crm/mscrmservices/2007/crmservice.asmx";
            service.CrmAuthenticationTokenValue = token;
            service.Credentials = System.Net.CredentialCache.DefaultCredentials;
        }

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

        }

        public override void LeadInput_ProcessInputRow(LeadInputBuffer Row)
        {
            lead cont = new lead();

        if (!Row.TITL20_IsNull)
            
        {
                cont.salutation = Row.TITL20;
            }


        if (!Row.DOBI20_IsNull)
                
        {
                    cont.new_birthdate = Row.DOBI20;
                }

        ....

        ....

                    service.Create(cont);
                }

            }
        }


    ***************

    { cont.new_birthdate = Row.DOBI20; } throws:

    "cannot implicitly convert type 'string' to .....CrmSdk.CRMDateTime"


    Cheers

    Tuesday, October 2, 2012 10:53 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Got it.

    Try to use following code:

            private DateTime CYYMMDDtoDateTime(string source1)
        {
            string source = (source1.length == 6 ? "0" : "") + source1;
	


            int year = 0;
            int month = 0;
            int day = 0;

            year = (source.Substring(0, 1) == "0" ? 1900 : 2000) + int.Parse(source.Substring(1, 2));
            month = int.Parse(source.Substring(3,2));
            day = int.Parse(source.Substring(5, 2));

            return new DateTime(year, month, day);
        }

    Freelance Developer for Dynamics CRM 4.0/2011

    Wednesday, October 3, 2012 2:32 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    In this case you should add only following check:

    if (Row.DOBI20 != "0")

cont.new_birthdate = new CrmDateTime()
{
    value = CYYMMDDtoDateTime(Row.DOBI20).ToString("s");
};


    Freelance Developer for Dynamics CRM 4.0/2011

    Thursday, October 4, 2012 10:55 AM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    Try to use following:

    cont.new_birthdate = new CrmDateTime()

    {

    value = Row.DOBI20

    };

    Freelance Developer for Dynamics CRM 4.0/2011

    Tuesday, October 2, 2012 11:08 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks Andrii,

    I reckon that's the ticket. To expand on my question where i referred to (http://stackoverflow.com/a/4880021/1326443) for a bit more scope on my issue. I have a (AS400 style) Y2k string. To paraphrase:

    "I am using SSIS (SQL 2008) to bring data over from an AS400. The date values are stored in the 400 as a 7 digit numeric. Here is the format: "CYYMMDD" C is a "century digit" where 0 = 1900 and 1 = 2000. I have been looking into derived columns and script components. ...Also, I am losing leading zeros."

    The suggested answer was:

    "

    1. ((DT_WSTR,8)(<<AS400>> + 19000000)) 
    2. (DT_DBDATE)(SUBSTRING(DCDateString,1,4) + "-" + SUBSTRING(DCDateString,5,2) + "-" + SUBSTRING(DCDateString,7,2))

    "

    I do have the data in a SQL Server table now so the AS400 system is out of the picture. But how do i get this into my code to pass the value:

        cont.new_birthdate = new CrmDateTime()

        {

        value = SomeFunction(Row.DOBI20)???

        };

    Any ideas?

    Tuesday, October 2, 2012 12:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    I believe you can write this coversion with own hands. Should be something like:

            private DateTime CYYMMDDtoDateTime(string source)
        {
            int year = 0;
            int month = 0;
            int day = 0;

            year = (source.Substring(0, 1) == "0" ? 1900 : 2000) + int.Parse(source.Substring(1, 2));
            month = int.Parse(source.Substring(3,2));
            day = int.Parse(source.Substring(5, 2));

            return new DateTime(year, month, day);
        }

    And to instantiate CrmDateTime properly try to use following code:

        cont.new_birthdate = new CrmDateTime()
    {
    value = CYYMMDDtoDateTime(Row.DOBI20).ToString("s");
    };


    Freelance Developer for Dynamics CRM 4.0/2011

    Tuesday, October 2, 2012 1:06 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    If i said to you "Index and length must refer to a location within the string. Parameter name:length" was fired by the Script Component: Runtime Error dialogue box.....



    Wednesday, October 3, 2012 12:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If i said to you "Index and length must refer to a location within the string. Parameter name:length" was fired by the Script Component: Runtime Error dialogue box.....




    Can you please provide sample of DOBI20 field that gives an error during conversion?

    Freelance Developer for Dynamics CRM 4.0/2011

    Wednesday, October 3, 2012 1:03 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Three types of value are

    0
    890121
    1000517

    The format is CYYMMDD where C is a century digit where: 

    0= 1900 & 1=2000

    On top of this the first 0 is stripped leaving 6 digits eg) 890121 as opposed to the 7 digit 1000517

    Thanks Andrii


    Edit: For what it's worth, to test i added a 0 at the start of my test fields where the leading 0 had been stripped to make a 7 character string and the function worked. So i am guessing we would need to add some conditional statement to cover fields with 6 OR 7 characters (OR 1 character?)...? maybe there is a better way.
    • Edited by MechaLaris Wednesday, October 3, 2012 2:30 PM
    Wednesday, October 3, 2012 2:17 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Got it.

    Try to use following code:

            private DateTime CYYMMDDtoDateTime(string source1)
        {
            string source = (source1.length == 6 ? "0" : "") + source1;
	


            int year = 0;
            int month = 0;
            int day = 0;

            year = (source.Substring(0, 1) == "0" ? 1900 : 2000) + int.Parse(source.Substring(1, 2));
            month = int.Parse(source.Substring(3,2));
            day = int.Parse(source.Substring(5, 2));

            return new DateTime(year, month, day);
        }

    Freelance Developer for Dynamics CRM 4.0/2011

    Wednesday, October 3, 2012 2:32 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Nearly there...It's just those pesky records with a string value of '0' (as above). I guess this will need to be replaced in the function with a value that CrmDateTime recognises as 'nothing' (NULL) or replaced with an empty string. Or otherwise!

    I have a vbscripting background and just wondered if there was an "on error resume next" type option to get round the C# script bombing out. I've just had a look and can see there is a try..catch option. Is it possible to try the function and if there is a problem return (catch) a value that CrmDateTime recognises as nothing or return an empty string? Just a thought...

    • Edited by MechaLaris Thursday, October 4, 2012 10:57 AM
    Thursday, October 4, 2012 10:45 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    In this case you should add only following check:

    if (Row.DOBI20 != "0")

cont.new_birthdate = new CrmDateTime()
{
    value = CYYMMDDtoDateTime(Row.DOBI20).ToString("s");
};


    Freelance Developer for Dynamics CRM 4.0/2011

    Thursday, October 4, 2012 10:55 AM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote
    Great stuff! Really appreciate your help Andrii.
    Thursday, October 4, 2012 2:11 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hey Andrii,

    I have a related issue. I have switched from my test data source: Excel where the DOBI20 field was a string  - to production source: SQL Server table where I just found the DOBI20 field is decimal. What modification would you suggest to the script to accommodate this?

    Value = CYYMMDDtoDateTime(Row.DOBI20).ToString("s") throws: "cannot convert from decimal to string"

    Would i need to modify the function also?

    Many Thanks

    Thursday, October 4, 2012 4:00 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Try to replace line

    Value = CYYMMDDtoDateTime(Row.DOBI20).ToString("s")

    with line

    Value = CYYMMDDtoDateTime(Row.DOBI20.ToString()).ToString("s")

    Freelance Developer for Dynamics CRM 4.0/2011

    Thursday, October 4, 2012 4:05 PM
    Moderator