How to specify the filename in DTS package ?

i don't think this is possible.

you can change the method you import data from the excel file. for example you can create a stored procedure that will do this, using the filename as parameter.

in the stored procedure you should follow this steps:
1. add a linked with sp_addlinkedserver
2. add a login for the linked server with sp_addlinkedsrvlogin

Example:

EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\MyData\DistExcl.xls',
   NULL,
   'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL

3. now you can use the data from excel file as it is a table (Worksheet = table)

Example:
select * from ExcelSource...[Sheet1$]

or you can use OpenDataSource:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="<<your path to the .XLS file>>";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

Bogdan

bp... "i don't think this is possible."  this is not correct

gorth.. "Dynamic Properties Tasks"   yes this is the way to do it

Sangdo,   ther eare a numer of different options as to how to specify the filename but as gorth says you need a dynamic properties task.

how do you want to pass the spreasheet name into your dts package ?

I start the package from a delphi application :

 > try
 > _con.Execute('EXEC master..xp_cmdshell ''dtsrun /S 127.0.0.1 /Usa /Psa /N "Import bord"''',cmdText, eoExecuteNorecords]);
 > pgImportation.StepBy(8);
 > self.Refresh;
 > ShowMessage('Fin de l''importation');

So, If I can specify from delphi the filename It should be nice.
Otherwise I try also a stored procedure the start the package :

 > CREATE PROCEDURE [dbo].[spBord]
 > @filename nvarchar(255)
 > out
 > as
 > EXEC master..xp_cmdshell 'dtsrun /S 127.0.0.1 /Usa /Psa /N "Import bord"'
 > GO

Is it possible via one of that way ?

Sang-Do

Exciting.

I try this as soon as possible.

With this :

> EXEC master..xp_cmdshell 'dtsrun /S 127.0.0.1 /Usa /Psa /N "Import Bord"
> /A "SetFilename":"BordName"="E:\Moviecheck\Import\bord0004.xls" "SetFilename":"CampName"="E:\Moviecheck\
> Import\camp0004.xls"'  

SetFilename is the name of my dynamic property

I get :

 > DTSRun:  Loading...
 > DTSRun:  Executing...
 > DTSRun OnStart:  DTSStep_DTSDataPumpTask_2
 > DTSRun OnStart:  DTSStep_DTSDynamicPropertiesTask_1
 > DTSRun OnFinish:  DTSStep_DTSDynamicPropertiesTask_1
 > DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147220470 (8004040A)
 >    Error string:  Data Source Object name or SQL Statement is required to obtain a Rowset.
 >    Error source:  Microsoft Data Transformation Services (DTS) Package
 >    Help file:  sqldts80.hlp
 >    Help context:  4700
 
 > Error Detail Records:
 
 > Error:  -2147220470 (8004040A); Provider Error:  0 (0)
 >    Error string:  Data Source Object name or SQL Statement is required to obtain a Rowset.
 >    Error source:  Microsoft Data Transformation Services (DTS) Package
 >    Help file:  sqldts80.hlp
 >    Help context:  4700
 
 > DTSRun OnFinish:  DTSStep_DTSDataPumpTask_2
 > DTSRun:  Package execution complete.
 > NULL


I don't know if my error is in the command or if I made an error in the creation of the Dynamic property ?

I fact I have 2 file name to set.
I try to do this via One Dyn. property.
Maybe I have to try with 2 ?

did you set the dynamic properties tas kthe first step in the dts?    you must put on-success rules between that and your firrst task

one task can set multiple properties, you souldnt need 2 for what you are doing

yes I did

but looking @ your trace

 DTSRun OnStart:  DTSStep_DTSDataPumpTask_2
 > DTSRun OnStart:  DTSStep_DTSDynamicPropertiesTask_1


DataPumpTask2 is being started before DynamicProperties task

here is the new trace :

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish:  DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart:  DTSStep_DTSDataPumpTask_2
DTSRun OnError:  DTSStep_DTSDataPumpTask_2, Error = -2147220470 (8004040A)
   Error string:  Data Source Object name or SQL Statement is required to obtain a Rowset.
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts80.hlp
   Help context:  4700
 
Error Detail Records:
 
Error:  -2147220470 (8004040A); Provider Error:  0 (0)
   Error string:  Data Source Object name or SQL Statement is required to obtain a Rowset.
   Error source:  Microsoft Data Transformation Services (DTS) Package
   Help file:  sqldts80.hlp
   Help context:  4700
 
DTSRun OnFinish:  DTSStep_DTSDataPumpTask_2
DTSRun:  Package execution complete.
NULL

Thank for all

ok i think the best think is to open up the dts package, manually assign the global parameter values  and then manually execute the dynamic properties task.   once youve done this, then tak a look at the properties of your connections and verify if they are ok.

When you setup the Dynamic properties task, you needed to click on the Datasource of your Connection and set it to the Global variable in that order correct? Only need one Dynamic task for all of these things.

Ok,
The first executes with success but the second, by specifying the filename directly in the package setting values to globals variabnles, I get this error message :

 > microsoft jet database engine cannot find object 'e:\moviecheck\import\bord0004.xls.
 > Please verify if the object exists and if the path is correct ..

I'm sure the path is correct.
Sql server is installed on my workstation and tha path is local.

check which account the DTS package is running under.  It may not have permissions

Ok everything works well for a lot of files excepted for one and of course it was the file  I was testing on.