December 1, 2011 at 7:48 am
Hi -I'm trying to add a datetime variable PIT into a query in SSIS package but getting the following attached error. Note I have not done this before so I know I missing something but cannot put my finger on it. Here is my script and I attached a screenshot of the error.
Can anyone help me out?
Thanks,
David
SELECT [Client]
,[Engagement]
,[EngagementStatus]
,[Project]
,[Project ID]
,[Customer ID]
,[Brand]
,[Year]
,[Project Type]
,[Project Identifier]
,[Revenue Type]
,[ProjectStatus]
,[Task]
,[Resource]
,[BusinessUnit]
,[BillingRole]
,[BillableTask]
,[TaskCategory]
,[Week]
,[plannedHours]
,[BillingRate]
,[Revenue]
,[CostRate]
,[Cost]
,[Vertical]
,[EngagementManager]
,[Partner]
,[AssociatePartner]
,[EngagementTimeApprover]
,[ProjectTimeApprover]
,[EmployeeType]
,[StaffingType]
,[Office]
,[Level]
,[PrimaryFunction]
,[UserDefinedResourceID]
,[FiscalNo]
,[we_date]
,[Allocation Method]
,[Contract Type]
,[Contract Amount]
,[Percent Complete]
,[Finance Contact]
,[ADP ID]
,@PIT AS PIT <-- Here is the variable I'm trying to add.
FROM [Cp].[dbo].[hgvBacklogLabor_Changepoint]
WHERE Week between dateadd(dd,0,datediff(dd,0,(dateadd(dd,(datepart(dw, getdate())*-1 ),getdate())))) and dateadd(ww, 62, dateadd(dd,0,datediff(dd,0,(dateadd(dd,(datepart(dw, getdate())*-1 ),getdate())))))
December 1, 2011 at 1:39 pm
Add a derived column task following the source task.
CEWII
December 1, 2011 at 1:41 pm
Two options. As Elliott already mentioned, you can use a derived column task to include the variable as the column you're looking for before getting to the destination. This is my personally preferred method for dealing with package variables if it's an option.
The other choice is change it to ? as PIT and then include the variable as a paramter to the query in the interface.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 1, 2011 at 1:43 pm
Hi
Thank for the response. I did add Derived Column but know all it does is add the column plus the date and time that is listed in my variable. I want it enter the curent date and time when the package runs keeping the all dates. I attached what my SSIS looks like.
Thoughts??
Thanks,
David
December 1, 2011 at 1:50 pm
Either method described would do exactly the same thing. How is PIT determined? I am unsure exactly what you want. Please clarify and we will try again.
CEWII
December 1, 2011 at 2:04 pm
Lets see if I can explain it right.
What I'm doing is pulling in data from another database using a SSIS package. In the source editor I'm using SourceConnectionOLEDB - SQL Command and have my script to pull in the data from my source. I need a Point In Time stamp of when it was pulled into the new table.
Later I will write a script that will delete everything but lets say Friday's 12:00 PM data pull. Keeping that 12:00 data for archival purposes.
I created a variable in SSIS like this
Name - PIT
Scope - BackLogLabor_Pull_Pacjage
Data Type - DateTime
Vaule - 12/1/2011 3:33 PM
Then I created the Derived Column like this
Derived Column Name - PIT Derived Column 1
Derived Column - <add as new column>
Expression - @[User::PIT]
Data Type - date [DT_DATE]
The PIT column is create but the data in there is 2011-12-01 15:33:00 no matter what time I run it.
I hope that helps Please let me know if you need more information.
Thank you again for the help with this.
David
December 1, 2011 at 2:08 pm
You want to change your variable to use an Expression, and then use the GETDATE() function to get the current date. As it stands you're hardcoding a variable and always using that variable.
December 1, 2011 at 2:27 pm
I don't want to sound like an idiot but I'll go ahead and do that.
How do I
change your variable to use an Expression
Is that changed under the Data Type in the variable?
Thanks,
David
December 1, 2011 at 2:33 pm
If you select your variable and then go to the properties window, you'll see it. Check this screenshot for more details
December 1, 2011 at 2:43 pm
Yeah!! that was it....
Thank you for pointing me in the right direction 😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply