Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Date in SSIS


Help with Date in SSIS

Author
Message
frdrckmitchell7
frdrckmitchell7
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 271
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
Tee Time
Tee Time
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 465
What is the error specifically saying?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16447 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
frdrckmitchell7
frdrckmitchell7
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 271
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
frdrckmitchell7
frdrckmitchell7
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 271
Oh, my code needed to be in quotes.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16447 Visits: 13202
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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
frdrckmitchell7
frdrckmitchell7
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 271
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search