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
è¿é

 

[JayKusch]

You cannot "SELECT" a file in a DTS package. SELECT is a SQL Server reserve word.

What exactly are you trying to accompish???

Thanks

J. Kusch

 

[Eyespi20]

The code is copied from an earlier post by MrDenny whos code is exactly copied (except for the server/path information) as written by him. The instructions preceeding the code are:

1. You will want to use a dynamic properties task (this is where my code is residing)
2. Add a Dynamic Properties Task to your DTS package.
3. Open it.
4. Click Add.
5. Open Connections.
6. Open the connection to your text file.
7. Ope OLE DB Properties
8. Select Data Source
9. Click Value on the right, then Set at the bottom.
10.Change the Source to Query
11.Change the Connection to a SQL Server.
12.In the query area put in something like this.

Afterwhich is his code section containting the code.

13.Click OK
14.Click OK
Make the Dynamic Properties Task the first thing to happen in the package.

Thanks for your help.

Margaret
è¿é

 

[gradley]

Try adding a single quote before the
.csv'

 

[Eyespi20]

Well, that solved that problem, but now when I execute the job, I get an error that says "Cannot update. Database or object is read only" Which it is not.

Any ideas? (told you I needed a hand-holding!)

Thanks!

Margaret
è¿é

 

[gradley]

Are you dealing with an outdated Access Database?

Here are a couple of microsoft links that hopefully will address this issue.

http://support.microsoft.com/default.aspx?scid=kb;en-us;825797

http://support.microsoft.com/default.aspx?scid=kb;en-us;295200

 

[Eyespi20]

No, this is not an Access database.It's a SQL database on a SQL7.0 server and I'm exporting the information to a comma delimited file.

Thanks, though.

Margaret
è¿é

 

[gradley]

Does SQL Server have rights to the folder that you are writing this file to? Try writing it locally first to see if that works. It sounds like you may have a folder permissions issue.

 

[Eyespi20]

It worked when the file name was not the dynamically created file name, and that's the only thing different.


Margaret
[color]è[/color]¿é

 

[gradley]

When you made the dynamic property entry, did you click the refresh button to preview the value that would be created?

 

[Eyespi20]

Yes -- \\ttc-appserver1\DataEntryReviewReport\DataEntryReview_20050407.csv

which is a correct path to the folder.

Could it be too long of a file name?

Margaret
è¿é

 

[gradley]

I don't think so.

I just performed the same logic here pointing to an existing server that I have and it worked fine.

Try running the Dynamic Properties Task on it own and then try the export task on it's own.

When you say it worked when the file was not dynamically created, did you set it as '\\ttc-appserver1\DataEntryReviewReport\DataEntryReview.csv' in the dynamic task or did you just execute the export portion?

 

[Eyespi20]

When I first created the export, I did not have the Dynamic Property on the task, just the query and export to a statically named file "\\ttc-appserver1\DataEntryReviewReport\DataEntryReview.csv" -- this worked perfectly, but my client wants to have it with a date appended to the file name in case he doesn't get to the file daily. This way his data will not be overwritten, but a new file created for each day's work.

How do you run a task on it's own? I've only been able to run the entire job.

Thanks.

Margaret
è¿é

 

[gradley]

Right click on a task, or in the case of an export task, right click on the Transform connector and select execute step.

You can do the same with the Dynamic Properties Task by clicking on the task.

 

[Eyespi20]

I figured out the run it by step part.

What name should I have in the destination?

I've tried "//ttc-appserver1/DataEntryReviewReport/DEReview.csv" with the above error of "file is read only" or "//ttc-appserver1/DataEntryReveiwReport/DEReview*.csv" with the error of "Error Opening Datafile. The Network path was not found."

Thanks!

Margaret
è¿é

 

[Eyespi20]

Oh, and the Dynamic runs fine, it's the Export that flops.

M

 

[gradley]

You should have your forward slashes // and / going \\ and \. I assume that was just a keying error.

 

[Eyespi20]

Yes -- it's \\ and \ in the actual job -- sorry

M

 

[gradley]

So the export flops with the same "file is read only" error even when using the '\\ttc-appserver1\DataEntryReviewReport\DataEntryReview.csv' static name, but when you ran it initially it worked fine. Have you deleted the initial file from the network and tried it. I'm still thinking this may be rights issue. Do you have full control rights to that folder or just modify?

I'm not sure what else I can suggest at this point.

 

[Eyespi20]

Yes, I've deleted the file.

The folder is a public one -- anyone can write to it from inside the network.

Thanks for all your help and patience!

Margaret