Date not evaluating in an expression

I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

Doriss wrote:

I have been looking all over for some info about other people having this problem, but haven't found anything.

I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!

Not quite sure of the whole picture here but when you say its being evaluated in two places I start to worry. So, two questions:

Where is the expression (is it on a variable or elsewhere)?

What are you attempting to do in your script task?

-Jamie

|||

The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.

|||

Doriss wrote:

The script portion might just be my inexperience with ssis, but I read online somewhere that using script objects to set your variables was good practice and I was originally having problems getting my variables to be read at all, and this solved the problem. So basically they are just empty script objects where I am using the expressions for those objects to set variables that will be used throughout the process. I used two of them because at the time I couldn't figure out how to use them to set just a plain variable without using one of the properties (ex: readonlyvariable). So I use the readonlyvariable and the readwrite variable in each object to set my expression. I thought for a moment just the other day that I could actually take this out and set the variables globally, but as I mentioned, that is when I had problems with the app crashing when I tried to set the expression to be evaluated at a global level. I actually tried changing my program today to also retrieve the date from the database by running a query that would return it to a variable and it still did not work. It seems as if the variables get set at some point in time and then they are not re-evaluated.

Woah. That's alot of information.

I wouldn't agree that using script tasks to set a variable is considered better practice than using an expressoin on the variable. In fact I would argue to the contrary:

Variables evaluated by an expression are more reusable|||

Wanted to post that I found the resolution to this. I did end up getting rid of the script object stuff. I think I suffered from too much information on the web that sent me in the wrong direction. I decided to use global variables, and got rid of my script objects. But what really made it work was removing the reference to the variable name. So this:

@.[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

Now becomes:

(DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)

When I did it the other way it was locking my variables (which was why it was crashing though it actually wasn't - it was just taking a really long time to tell me what the problem was). I also changed some of my other variables that were referencing this variable, which I just read about.

For the record, I think the documentation on variables is a bit lacking. It seems some important things to know from my experience is to make everything global (work with it in the variables window and set it's properties). Make sure to set the evaluateexpression to True if thats what you want (in the properties window). Do not reference another variable in your expression and do not set the variable equal as mentioned above (if you are doing this in the properties expression window). Other things to know that it took me forever to figure out is that you should save the package as a server package (you have to use the copy as) - this is the easiest way to port over your package. You need to give the user account that this is running under user mapping to msdb (SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole) as well as create a proxy account. Best info for that is here:http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

Phew - it only took me forever to actually get this to work!

Date Logic for a DTS package

Hello,
I need to facilitate updating a data warehouse table with a DTS package that
updates an accounting table for premium amounts. I will do a one time run o
f
all the accounting records and after that would like to 'grab' just the
previous 2 months worth of data (on a nightly run, so that it is up to the
day) and add it to the existing data. Obviously, there will be overlap in
dates, so what would be a good way to handle this with my logic?
Thank you!Hi Patrice
It is not clear what exactly you are trying to achieve.
If you use a query as the source of your data, then you can limit the data
that is extracted by a criteria (assuming that you have datatime value that
will give you the last two months). If your destination is accessable throug
h
a linked server you could exclude those rows that do not exist in the
destination table (using the primary key), this will mean that the time
restriction is unneccessary. If you can't use a linked server, then you can
load the data into a staging table, and then selectively insert new records
(using the existance of the PK) from there.
John
"Patrice" wrote:

> Hello,
> I need to facilitate updating a data warehouse table with a DTS package th
at
> updates an accounting table for premium amounts. I will do a one time run
of
> all the accounting records and after that would like to 'grab' just the
> previous 2 months worth of data (on a nightly run, so that it is up to the
> day) and add it to the existing data. Obviously, there will be overlap in
> dates, so what would be a good way to handle this with my logic?
> Thank you!

Date Help

Here is the problem......
1-I am new to SQL, this is a big problem
2- I have a table that was extracted from a WFM (workforce managment) package that for some reason they cannot use the normal date format. They use was is called a start_moment, this is the name of the field. I have figured out the calculation in access but I am now trying to get it into an asp page and need to format to a sql function.

the Start_moment data contain the date and the time.
for example - 55196880 in the start_moment field is actually 12/09/2004 at 23:00.

this stupid wfm software company states the this moment is the number of minutes from December 30, 1899, 12:00 AM GMT.

I need the date in one field and the time in another
any suggestions?I'd start with:SELECT DateAdd(minute, 55196880, '1899-12-30')
-PatP

Date function in DTS package

How can I make this work against an Access table through an ODBC dsn in my DTS package? I need to subtract 1 day from the current date.

Tried this, which is incomplete of course, (need to subtract the 1 day):

WHERE (TTDateTimeIn >= DATEDIFF(dd, 1, { fn CURDATE() }))

Got this error:
[Microsoft][ODBC Microsoft Access Driver] Too few Parameters. Expected 1.I believe the function you should be using is DATEADD, not DATEDIFF. And you will need to enclose that dd in single quotes.

Terri|||That's correct, I finally figured it out. What added to my troubles was needing to pull yesterdays data after midnight, but I got it.

(TTDateTimeIn >= DATEADD('y', - 1, { fn CURDATE() }))

It was painful building this DTS package using a DSN connection to get to locked MS Access tables.

Thanks.

Date Convertion

I am currently running a DTS package to extract data from a DB2 database.
The code reads Select * from ABC where entrydate='12/15/2005'
This works fine but I need to automate the process by selecting the date
automatically. As soon as the entrydate = formula the extract do not work.
I have tried different versions of date formula
The format of the date field on the SQL table is smalldatetime and on DB2
it is date
Can some one helpVuka
Use 'yyyymmdd' format with SQL Server
Lookup CONVERT system function in the BOL
"Vuka" <Vuka@.discussions.microsoft.com> wrote in message
news:CF33E355-6FFA-4C1F-95A8-C9EABE0CDD29@.microsoft.com...
>I am currently running a DTS package to extract data from a DB2 database.
> The code reads Select * from ABC where entrydate='12/15/2005'
> This works fine but I need to automate the process by selecting the date
> automatically. As soon as the entrydate = formula the extract do not
> work.
> I have tried different versions of date formula
> The format of the date field on the SQL table is smalldatetime and on DB2
> it is date
> Can some one help

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
Montreal
JP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison
|||Thanks,
but not all the record have this value. When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure
.
Regards,
Jean-Paul
|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like â'0001-01-01â'. Before I import directly my AS400 tables into Access without problems because Access read those date as â'1901-01-01â'. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value â'0001-01-01â' to â'1901-01-01â' and then alter the data type back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like â'0001-01-01â'. I could not do modification to the AS400 source. Is there a way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with
a package to a text file, there are dates like “0001-01-01”. Before I i
mport directly my AS400 tables into Access without problems because Access r
ead those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Pa
ckage it crash unless I first change the fields in my SQL table to varChar(1
5), do the importation, change the value “0001-01-01” to “1901-01-01
and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields.
I try to link the AS400 source to my SQL Server, I still could not read tab
les that have dates like “0001-01-01”. I could not do modification to th
e AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||Thanks,
but not all the record have this value. When I import with package, I could
go around it. But I need to be live, so, from my SQL Server, I link into the
AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it
said “conversion failure
.
Regards,
Jean-Paul|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison