Post reply

Help with Date in SSIS

  • October 29, 2013 at 6:01 am

    #300590

    Hi all the clever people

    I have set up the following three variables that I want to use in a for loop container:

    Counter Scope= For Loop Container DataType = DateTime

    EndDate Scope= For Loop Container DataType = DateTime

    StartDate Scope= For Loop Container DataType = DateTime

    InitExpression @[User::Counter] = @[User::StartDate]

    EvalExpression @[User::Counter] < @[User::EndDate]

    AssignExpression @[User::Counter] = dateadd("d",1,@[User::Counter])

    I have the following code that I want to use in Startdate:

    (TrxDateNumeric is defined as a smalldatetime variable in our database - it cannot be changed for various reasons)

    Select TrxDateNumeric

    from dbo.BI1_View_Dim_CalendarDefinition WITH (NOLOCK)

    inner join dbo.BI1_View_Dim_SystemParameters WITH (NOLOCK)

    on Run_Type = ''SALES''

    where TrxDate = RUN_DateHistoryFrom

    TrxDateNumeric is set to the first of the month for the current month, which is what I want to store in StartDate, but I get an error is SSIS (the code as is here works in the db and is what I want).

    Is there anyone that can assist me with this? I am a bit new to ssis.

    Kind regards

    Fred

  • October 29, 2013 at 6:45 am

    #1662329

    What is the error specifically saying?

  • October 29, 2013 at 6:46 am

    #1662331

    What is the error? How do you try to put TrxDateNumeric into StartDate?

    ps: any reason why you are using NO_LOCK in your query?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 29, 2013 at 6:55 am

    #1662337

    Hi Koen

    thanks for coming back. I have figured out this one thanks. NOLOCK, btw, is something standard that we use here. I have to use it because my boss wants it in the code.

    Thanks for your help anyway.

    Kind regards

  • October 29, 2013 at 6:56 am

    #1662338

    Oh, my code needed to be in quotes.

  • October 29, 2013 at 6:57 am

    #1662339

    frdrckmitchell7 (10/29/2013)

    NOLOCK, btw, is something standard that we use here. I have to use it because my boss wants it in the code.

    I hope your boss is fully aware of all the negative consequences of using NO_LOCK.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 29, 2013 at 7:00 am

    #1662342

    I'll ask him the day he fires me, lol.

    Seriously, I'm still to new at the company to question his 'motives'. Will leave the questions for a later time:-D

Viewing 7 posts - 1 through 6 (of 6 total)

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