Date in file name for delivered report

Does anyone know how to use the date in the file name of a delivered report?
Is it even do-able? ex: Oct_1_2004.csv. thanksIt is possible with data driven subscriptions
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris" <idroppeddabomb@.hotmail.com> wrote in message
news:%23VRjR69pEHA.3300@.TK2MSFTNGP12.phx.gbl...
> Does anyone know how to use the date in the file name of a delivered
> report?
> Is it even do-able? ex: Oct_1_2004.csv. thanks
>|||Well at least I know now that it can be done. I've searched the help pages
thoroughly and can't find any information on how this can be accomplished.
Can you please provide me with some insight on how this can be done? The
only thing I can think of is to use : "=Now()" as the file name in Report
Manager but that just names the file "=Now().xls". Some guidance would be
appreciated as I have purchased TWO reporting services books (one of them by
Brian Larson) and there's nothing in either of them on how to do this. Thank
you.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:eW3UOgdqEHA.596@.TK2MSFTNGP11.phx.gbl...
> It is possible with data driven subscriptions
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Chris" <idroppeddabomb@.hotmail.com> wrote in message
> news:%23VRjR69pEHA.3300@.TK2MSFTNGP12.phx.gbl...
> > Does anyone know how to use the date in the file name of a delivered
> > report?
> > Is it even do-able? ex: Oct_1_2004.csv. thanks
> >
> >
>

Date format export/import

Help!

I have a table that has datetime format field, I exported the table to
a csv while I dropped it and tried some other data, but now sql
doesn't recognise the date format for importing, heck I don't!

The dates look something like:

40:58.1

Whick means nothing to me, or any of us here for that matter...

Any ideas?

JohnHi

Without knowing how you exported the file it is hard to say how you came
to have the dates you have. It seems that you have something like
minutes, seconds and the first digit of the milliseconds.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

date conversion - derived column

I'm reading a .csv file that has a column such as 17June2007:00:00:00, how can I convert this column in my Derived Column routine so its inserted into my DateTime column in my database?

so far I've tried

DT_DateTimeStamp

DT_Date

DT_DBDate

DT_DbTime

and its kicking back errors on this column. What do I have to do to convert 17June2007:00:00:00 into a readable datetime for the database column?

I think you'll need to parse the date. Find the first colon, and work backwards. Something like:

(DB_TIMESTAMP) SUBSTRING([Your_Date], FindString([Your_Date], ":", 1), 4) ... and so on.

|||I've tried that and it still fails.

|||

Are you getting an error message?

|||yes

|||

Hi

I think you need to do it in 2 steps :

1. Use "Direvied Column" task convert you string "17June2007:00:00:00" to a propert Datetime string

"17 June 2007 00:00:00"

2. Use "Data Conversion" task to generate a new column of Date [DT_Date] datatype

|||

I've just finish trying that actually and it still fails. The field is defined as a string (DT_STR), I then create a new derived column and set that data type to DT_DATE and it fails, I even tried DT_TIMESTAMP, DT_DBDATE, and DT_DBTIME and all fail.

|||

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

|||

Now Im getting this:

[Data Conversion [1698]] Error: Data conversion failed while converting column "tDate" (733) to column "Copy of tDate" (1745). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I have the date field in the derived columns as a string, I then add a 'data conversion' task and take my date field and convert that to a DT_DATE and I get the above message

|||

NB2006 wrote:

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

The heck you say.... You can too change a data type in a derived column transformation. You most certainly do not need both.|||

Phil Brammer wrote:

NB2006 wrote:

You can not change the Datatype in "Derived Column" task , only in "Data Conversion" task.

You have to use both.

The heck you say.... You can too change a data type in a derived column transformation. You most certainly do not need both.

I'm not SSIS expert but I thought thats what the derived column was doing, changing the dataType for that column. I've looked at both and the data conversion task appears to be doing the same thing as a derived column is doing with the data type aspect of it. Either way I'm still failing on this date thing and its driving me nuts.

|||

Hi Phil

You are right , "Derived column" on its own worked just as well.

Derived Column - <New Column Name>

Expression - SUBSTRING(Date,1,2) + " " + SUBSTRING(Date,3,4) + " " + SUBSTRING(Date,7,4) ..... or whtever you are using

DateType - Date (DT_Date]

Length - NA

|||How are you doing substrings when the length of the month name can vary? That is, how are you parsing the date field to apply to all months?

I'd try to spend more efforts on the source side to get this date field cleaned up.|||

Phil Brammer wrote:

How are you doing substrings when the length of the month name can vary? That is, how are you parsing the date field to apply to all months?

I'd try to spend more efforts on the source side to get this date field cleaned up.

I can't tweak the source at all. I have to work with it as it is. Its currently working in a DTS package, its when I migrated that DTS package to SSIS that the date format is an issue. The date in the source field has never changed and always has been formatted like "01JUN2007:00:00:00" and that format is loading fine in a DTS package and that package has no specail formatting being done and its loading into the table on SQL 2000 as 6/1/2007, so what do i need to get that format to work on my SQL 2005 database via a SSIS package?

|||

IGotyourdotnet wrote:

I can't tweak the source at all. I have to work with it as it is. Its currently working in a DTS package, its when I migrated that DTS package to SSIS that the date format is an issue. The date in the source field has never changed and always has been formatted like "01JUN2007:00:00:00" and that format is loading fine in a DTS package and that package has no specail formatting being done and its loading into the table on SQL 2000 as 6/1/2007, so what do i need to get that format to work on my SQL 2005 database via a SSIS package?

So what is the format of the file? "01June2007" or "01JUN2007"? That is, is the month always three characters?

Date (SQL 2000)

Hi,
I get this csv file to import and the date in the column of the CSV file
comes as 3/1/2007 0:00
When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
the month and the 3 is the date. Is there a way to fix this?
Thanks in advanceHello,
Open the CSV file and select the entire date column, right click, Format,
Date option and choose English -- United States and click ok.
This will change the date format to Unites states and after this you coulld
load into SQL Server table
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:A46A190A-A386-46F9-AA6A-8498001E21EF@.microsoft.com...
> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance|||Try using a datetime string transform on the column. After creating the
transform, set InputFormat property to : "M/d/yyyy H:mm"
--
Russel Loski, MCSD.Net
"stoney" wrote:

> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance

Date (SQL 2000)

Hi,
I get this csv file to import and the date in the column of the CSV file
comes as 3/1/2007 0:00
When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
the month and the 3 is the date. Is there a way to fix this?
Thanks in advance
Hello,
Open the CSV file and select the entire date column, right click, Format,
Date option and choose English -- United States and click ok.
This will change the date format to Unites states and after this you coulld
load into SQL Server table
Thanks
Hari
"stoney" <stoney@.discussions.microsoft.com> wrote in message
news:A46A190A-A386-46F9-AA6A-8498001E21EF@.microsoft.com...
> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance
|||Try using a datetime string transform on the column. After creating the
transform, set InputFormat property to : "M/d/yyyy H:mm"
Russel Loski, MCSD.Net
"stoney" wrote:

> Hi,
> I get this csv file to import and the date in the column of the CSV file
> comes as 3/1/2007 0:00
> When I import it to SQL with DTS it thinks 3 is the month but in fact 1 is
> the month and the 3 is the date. Is there a way to fix this?
> Thanks in advance