Naming Files in DTS export 1

[Eyespi20]

I know this has been addressed before and I've read the archives, and implemented the suggestions, but it just isn't working, so I feel that I'm missing a piece somewhere. Since I'm a real neophyte when it comes to DTS jobs, I need some additional hand-holding through this, please.

I've created the job. I've created the dynamic property and connected it to my text file connection. Used the following code to create the file name with date appended to it.

select '\\ttc-appserver1\DataEntryReviewReport\DataEntryReview_' + 
convert(varchar(4), year(getdate())) + 
case when len(month(getdate()))=1 then '0' end +
convert(varchar(2), month(getdate()))+
case when len(day(getdate())) = 1 then '0' end +
convert(varchar(2), day(getdate())) + 
.csv'

When I click the refresh button, I get an error:

Error description: Invalid column prefix ": No table name specified unclosed quotation mark before the character string".

So, the name isn't getting the date appended to it and the file goes into the ftp site with just the name and will be overwritten the next time the job is run which isn't exactly what I need done.

Thanks in advance for any and all suggestions.

Margaret
è¿é

 

[Eyespi20]

Ok, I finally have it putting the file out on the server, but the Dynamic Properties task where it names it is not working. Since the name of the file HAS to be in the publish part of the job, how do you get it to use the one that's created by the Dynamic Property?

Thanks

Margaret

 

[gradley]

I'm not sure what you mean by the "Publish" part of the job. If you refer to the step by step process from mrdenny's original post, it will dynamically use this name at run time.

 

[Eyespi20]

I set the dynamic as the first step of the job, then have a couple of sql tasks, and the export to the file. The dynamic property is not naming the file -- it goes to the folder with the name of DEReview.txt. How can I get around this?

Thanks.

Margaret

 

[gradley]

Crazy question? Did you set the dynamic property back to the file name with mm/dd/yy extension or is it still "\\ttc-appserver1\DataEntryReviewReport\DEReview.csv" as you noted above. If the entire DTS package is not running (without error), it sounds like you either have the wrong name in the Dynamic step or you are no longer overriding the Data Source for the output file.

 

[Eyespi20]

Not a crazy question at all. I don't know. How would I check? I followed Mr Denny's instructions, but as I started out, I believe there is a (probably) small something I missed somewhere.

Thanks!

Margaret

 

[gradley]

Go back into the Dynamic properties task:

1. Double click on the line you created to override this file name.
2. Make sure that the "Data Source" of the OLE DB properties of this output file is highlighted in the left window pane.
3. Double-click on the "Value" in the right window pane
4. Click the [Refresh] button.
5. Scroll over to the right in the "preview" window and look at what value is displayed.

If it doesn't have the YYYYMMDD suffix then you haven't changed it back to your original. If it does include the suffix, then it somehow is not pointing to the output file connection and you may want to delete and recreate this Dynamic property.

Good luck!

 

[Eyespi20]

Ok, I deleted and recreated the job, now I'm getting an error like I was before...

<i>Error Source Microsoft JET Database Engine

Error Description: Cannot update. Database or object is read-only.</i>

Since there IS no object with this name, that isn't possible and the database is not read-only.

Thanks.

Margaret

 

[gradley]

Margaret,

I'm not as well versed on SQL 7.0 as SQL 2000 but can't (shouldn't) you be using an OLE-DB connection for SQL Server? Why are you using a Microsoft JET connection. I thought that was only for connections to an Access database? Is you output file defined as a destination (Text file)? It should be if you are creating .csv files.

 

[Eyespi20]

When you look at the properties of the text file output, it DOES say OLE-DB connection. Yes, my output file is a text file (comma delimited).

Thanks

Margaret