SSIS hanging - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

SSIS hanging

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-25 : 07:34:03

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

52326 Posts

Posted - 2013-10-25 : 08:41:22

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-25 : 09:13:06

ok I got it.

Thanks

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-25 : 09:15:39

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-25 : 09:22:31

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-25 : 09:25:44

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-28 : 01:51:39

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

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 07:05:42

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-28 : 08:47:08

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-28 : 08:59:48

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

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-10-28 : 09:46:26

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources