Dynamic Properties question

[Eyespi20]

I have a DTS job for which I need to make it run in the morning and in the afternoon, create an excel spreadsheet and mail it to a customer.

I have my queries in place and now am creating my Dynamic Property for naming the file -- this is my code

select '\\Europe\Public\AR Department\Completions_' + cast(datepart(mm, getdate()) AS varchar(2)) 
+ cast(datepart(dd, getdate()) as Varchar(2)) + 
cast(datepart(yyyy, getdate()) as Varchar(4))  + case when datepart(hh, getdate()) < 12 then '_AM'  when datepart(hh, getdate()) > 12 then '_PM' end +'.xls'

However, when I click the refresh button it gives me an unexpected error.

When I run this in Query analyzer, it runs perfectly.

Any insight?

Thanks!

Margaret

 

[SajidAttar]

Try using convert function

Please note that when [red] datepart(hh, getdate()) = 12 file name returns NULL [/red]

select '\\Europe\Public\AR Department\Completions_' + replace( convert(varchar, getdate(),101),'/','') + 
case when datepart(hh, getdate()) < 12 then '_AM'  else '_PM' end +'.xls'

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[Eyespi20]

Thank you for pointing that out, however, even changed as your example shows, I still get an "unexpected error occurred. An error result was returned without an error message."

Again, when run in Query Analyzer, this returns the correct value.

When run with the job, it reports the step running properly, but the file name is not changed.

Thank you for your time and assistance.

Margaret

Margaret

 

[SajidAttar]

When I tried, I was able to get the proper result with Dynamic Property Task Object. (Using both the Queries Yours and Mine)

Please cross check it. and possible please inform how you are using it?





Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[Eyespi20]

I have the DPT set up to be the first step of the DTS job. There are 2 or 3 steps whereby the DTS gathers information, inserts some information that it has run into my events table and lastly, produces the output file of the excel spreadsheet which I wish to have named with the date and AM or PM as when I get to the point of scheduling it, I will have it run twice a day. The DPT step is set up on the data source of the Excel spreadsheet as I've read on this forum to do.

I still want to incorporate a step where the excel spreadsheet is e-mailed to someone, but that's the last step and the least of my worries.

Thank you again for your help.

Margaret