How to you insert an SSIS variable into data flow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

How to you insert an SSIS variable into data flow

Author

Topic

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-01 : 10:45:55

Hi -I'm trying to add a datetime variable PIT into a query in SSIS package but getting the following error "Must declare the scalar variable "@PIT",. Note I have not done this before so I know I missing something but cannot put my finger on it. Here is my script.

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())))))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:49:49

is @PIT a variable added inside SSIS? if yes, you should create another variable to create the query concatenating this variable. If variable name is @[User::Query] then set EvaluateAsExpression property as true and set Expression as

"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]
," + @[User::PIT] + " AS PIT
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())))))"

and then in task where you use the above query use sql command from variable option (i guess you're using above inside OLEDB source task)
and then map it to @[User::Query] variable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 10:52:12

A much simpler way would be to ignore that column altogether in SELECT inside data flow task and then add a derived clumn where you add new column PIT and set it to value of @[User::PIT] and give option add as a new column so that it gets added to metadata flow for further use in data flow

The advantage in above method is that you dont have to create additional variable and also doesnt have to generate query dynamically.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-01 : 11:48:35

Visakh -

Yes the @PIT variable is inside SSIS.

I'm not sure I understand what and where I need to do the following

quote:
A much simpler way would be to ignore that column altogether in SELECT inside data flow task and then add a derived clumn where you add new column PIT and set it to value of @[User::PIT] and give option add as a new column so that it gets added to metadata flow for further use in data flow

Sorry I'm very new to SSIS and I'm a visual person. Can you show me?

Thanks,
David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-01 : 12:00:34

quote:
Originally posted by ostinoh

Visakh -

Yes the @PIT variable is inside SSIS.

I'm not sure I understand what and where I need to do the following

quote:
A much simpler way would be to ignore that column altogether in SELECT inside data flow task and then add a derived clumn where you add new column PIT and set it to value of @[User::PIT] and give option add as a new column so that it gets added to metadata flow for further use in data flow

Sorry I'm very new to SSIS and I'm a visual person. Can you show me?

Thanks,
David

i dont have time now to put together a working screenshot.
First go through the data flow task and drag and drop a derived column task from toolbox and then you can see where to set the values/ properties

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-01 : 13:16:12

No worries I found where to add the Derived Column and added what you stated to. The column does get created but the data shows up as Null.

Thoughts to why that is?

Thanks,
David

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-01 : 13:32:40

Visakh -

You can forget my last post about the Null. I got that fixed and can now see the date. Yea!! But how do I get the time to show up as well? Now it comes up as 2011-11-30 00:00:00

I need the time to allow for removal and keeping of data on certain days.

Thanks,
David

ostinoh
Yak Posting Veteran

66 Posts

Posted - 2011-12-01 : 13:52:44

Once again nevermind about my post I guess there is something with the Variable in SSIS that I had to select the variable in Variables window and then click the Properties window, and type the time in Value property.

When I did that it all worked.

Visakh - A big Thank you for your help and pointing me in the right direction.

David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-02 : 01:05:02

quote:
Originally posted by ostinoh

Once again nevermind about my post I guess there is something with the Variable in SSIS that I had to select the variable in Variables window and then click the Properties window, and type the time in Value property.

When I did that it all worked.

Visakh - A big Thank you for your help and pointing me in the right direction.

David

No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources