Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with Date in SSIS Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 6:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
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

Post #1509297
Posted Tuesday, October 29, 2013 6:45 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:50 PM
Points: 532, Visits: 448
What is the error specifically saying?
Post #1509323
Posted Tuesday, October 29, 2013 6:46 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,570, Visits: 10,448
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?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509325
Posted Tuesday, October 29, 2013 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
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
Post #1509331
Posted Tuesday, October 29, 2013 6:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
Oh, my code needed to be in quotes.
Post #1509332
Posted Tuesday, October 29, 2013 6:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 13,570, Visits: 10,448
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1509334
Posted Tuesday, October 29, 2013 7:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 2:25 AM
Points: 45, Visits: 165
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
Post #1509337
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse