Post reply

How to store date value'30/04/2012' to YYYYMM in sql table by derived column of SSIS?

  • September 5, 2012 at 4:38 pm

    #266338

    Hi ,

    I am going to store the date value data'30\04\2012' coming from csv into sql table column like'201204'.

    Please help me how to set the expression of SSIS derived column ?

    Regards,
    Kumar

  • September 6, 2012 at 2:39 am

    #1534305

    Any help??????????????

    Regards,
    Kumar

  • September 6, 2012 at 3:03 am

    #1534318

    Use the SUBSTRING() function.

    Have a try and post back with how you get on.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 6, 2012 at 3:14 am

    #1534321

    I need data like 201204 and storing into int datatype.Substring will give \ also???????????

    Regards,
    Kumar

  • September 6, 2012 at 3:17 am

    #1534323

    i have unable to set this expression in derived column.Pls help me..

    select convert(varchar(4),month(getdate()),105) + convert(varchar(4),year(getdate()))

    Regards,
    Kumar

  • September 6, 2012 at 3:32 am

    #1534331

    Derived columns in SSIS do not use T-SQL syntax - they use SSIS Expression syntax, which is a bit like C#. So no selects, converts etc. You just need to know the column name and start positions/lengths.

    Have a look at some more examples on-line & you'll see what I mean.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 6, 2012 at 3:36 am

    #1534333

    I'm a bit unclear of your requirements but I assume you want to extract the Month and Year from a given date and create a string from the components in the format YYYYMM.

    If so try this:

    SELECT CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS NVARCHAR(2)),2)

    Good luck!

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • September 6, 2012 at 3:41 am

    #1534336

    Robin Sasson (9/6/2012)

    I'm a bit unclear of your requirements but I assume you want to extract the Month and Year from a given date and create a string from the components in the format YYYYMM.

    If so try this:

    SELECT CAST(YEAR(GETDATE()) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(GETDATE()) AS NVARCHAR(2)),2)

    Good luck!

    No - this is not how SSIS derived columns work. The formula for a derived column can be as simple as

    SUBSTRING( «character_expression», «start», «length» )

    where character expression is typically a column name (in square brackets.)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 6, 2012 at 3:43 am

    #1534339

    Thanks Phil, I saw your reply just after I posted mine...

    Doing SSIS research as we type...

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • September 6, 2012 at 3:44 am

    #1534341

    Why not consider storing as a DATE data type and then convert to the said format when required. Don't understand why you would want to store as int? What's the reason?

  • September 7, 2012 at 9:26 am

    #1535134

    My date column is called periodbegin. The derived column is like this:

    YEAR(PeriodBegin) * 100 + MONTH(PeriodBegin)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply