SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SSIS hanging
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

184 Posts

Posted - 10/25/2013 :  07:34:03  Show Profile  Reply with Quote
Hi, When I assign an expression to a datetime variable SSIS just hangs when I run the package or open the solution.

All I am trying to do is:
@[User::userDate] = DATEADD("DAY",10,GETDATE())

A job needs to run daily for next 10 dates. Any other way and any reason why SSIS hangs?

Thanks

--------------------
Rock n Roll with SQL

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/25/2013 :  08:41:22  Show Profile  Reply with Quote
where are you setting this value? In expression builder? what happens when you click on Evaluate Expression?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/25/2013 :  09:13:06  Show Profile  Reply with Quote
ok I got it.

Thanks

--------------------
Rock n Roll with SQL
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/25/2013 :  09:15:39  Show Profile  Reply with Quote
I was assigning in the expression builder, should have just been DATEADD("DAY",10,GETDATE()).

Now another issue, this variable is a datetime field and needs to increment by a day which I am doing in the script. But it seems for every loop it is assigning the value back to the original value DATEADD("DAY",10,GETDATE()) and not incrementing by a day. If I hard-code the initial value then it works fine. Any thoughts?

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/25/2013 :  09:22:31  Show Profile  Reply with Quote
can I ask why you need a variable for this. Where are you trying to populate these values to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/25/2013 :  09:25:44  Show Profile  Reply with Quote
Here is the complete thing:

In the script:

DateTime StartDate, EndDate;
StartDate = Convert.ToDateTime(Dts.Variables["User::startDate"].Value);
StartDate = StartDate.AddDays(1); --this shows correct, date+1
Dts.Variables["User::startDate"].Value = StartDate; -- if I do a popup of Dts.Variables["User::startDate"].Value it still shows old date

Variable startDate is defined with a default value as:

(DT_DATE)((DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , GETDATE()), 2))

--------------------
Rock n Roll with SQL
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/28/2013 :  01:51:39  Show Profile  Reply with Quote
got it, we first need to set EvaluateAsExpression = False if we have to modify an expression at runtime.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/28/2013 :  07:05:42  Show Profile  Reply with Quote
quote:
Originally posted by rocknpop

got it, we first need to set EvaluateAsExpression = False if we have to modify an expression at runtime.

--------------------
Rock n Roll with SQL


yes thats obvious
otherwise it will always change value based on expression set.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/28/2013 :  08:47:08  Show Profile  Reply with Quote
I did not know that expressions worked this way. I thought we could first initialize an expression and then just modify it normally (without setting any property to false).

The variables are used to populate data for say 10 days, day by day and wanted to set start date from the day it ran. so endDate would be start +1. On each iteration start would increment by 1 and so would endDate.

Thanks for your inputs.

--------------------
Rock n Roll with SQL
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/28/2013 :  08:59:48  Show Profile  Reply with Quote
quote:
Originally posted by rocknpop

I did not know that expressions worked this way. I thought we could first initialize an expression and then just modify it normally (without setting any property to false).

The variables are used to populate data for say 10 days, day by day and wanted to set start date from the day it ran. so endDate would be start +1. On each iteration start would increment by 1 and so would endDate.

Thanks for your inputs.

--------------------
Rock n Roll with SQL


Why you need separate variables for that? Why not loop over calendar table which will have dates stored in them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 10/28/2013 :  09:46:26  Show Profile  Reply with Quote
Yes maybe that is one way of achieving this. Will there be any performance improvements if I use a table instead of variables?

--------------------
Rock n Roll with SQL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000