Specify output excel file name in a DTS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Specify output excel file name in a DTS package

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-10-19 : 22:07:33

Hi all,

I have a DTS package which exports data into an Excel file, with the source as the results from a SQL Server stored procedure, and the destination as an Excel file which already exists.

Is it possible to run the package with a different Excel file name each time? For example, I want to use dtsrun command prompt utility but let the user specify an excel file name to be saved.

Thanks a lot,
delpiero

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-19 : 23:33:15

Create a global variable for the excel connection.
Pass the variable value when you run dtsrun.

Probably this could help
http://www.sqldts.com/default.aspx?201
http://www.sqldts.com/default.aspx?200

Thanks

Karunakaran

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-20 : 00:12:03

I am not very sure about the DTS utility but yes after generating the .bas file of the dts and attaching in you client application, you can remove the hardcoding if the file name you have specified. You can pass that as a parameter.

regards
Sachin

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-10-20 : 04:56:33

Thanks a lot. The global variable and Dynamic Properties task do work.

Just one more question: I am now using local path and file names. Is it possible to specify networked path for the file name variable? It seems that no matter I use mapped drive letters or UNC path names, an error will be returned specifying failure to create file.

Thanks,
delpiero

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-20 : 04:59:28

For network files try something like this

\\machinename\sharedfoldername

EDIT:- \\machinename\sharedfoldername\filename

The shared folder might need full permissions...I havent tried it with DTS. But with Bulk Insert it does work, so it should work for DTS also..

Karunakaran

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-10-20 : 06:04:34

But I still got the "failure to create file" error. If I specify an UNC path which poitns to the same machine as the SQL Server, it works, but it doesn't work for other networked machines no matter I set full permissions on the folder, share the folder, or map drive to the folder.

Actually I want to make the excel file output generated on the client machine ... Is it possible to do so?

Thanks,
delpiero

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-20 : 06:16:43

quote:
Originally posted by delpiero

But I still got the "failure to create file" error. If I specify an UNC path which poitns to the same machine as the SQL Server, it works, but it doesn't work for other networked machines no matter I set full permissions on the folder, share the folder, or map drive to the folder.

Actually I want to make the excel file output generated on the client machine ... Is it possible to do so?

Thanks,
delpiero

I doubt you will have an acces to the client machine unless its in the same network. If its in the same network try to run the package using a login which has admin permission. The normal SQL Agent account might not have permissions to access the client's hard disk.

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources