Can I set Excel connection manager's data source as a variable?

I do not know the Excel file name to load in design time.

Would like to pass the value to a variable in the package in run time?

How to do this?

Thanks,

Guangming

Property Expressions is the feature you want. You can assign and expression to most properties, including the DataSource property of a connection. The expression would just be the name of your variable, e.g.

@.[User:MyVar]

Some UIs expose expressions as a separate tab, or just expand expressions from the normal properties grid.

|||I set ExcelFilePath to a variable:

varExcelFileFullPath.

I set an initial value ( \\test\testShare\aaab.xls ) to it when designing the package. It works

Later I run it inside SQL server as below. It failed.

exec xp_cmdshell 'dtexec /SQL "\NE_LoadExcel_Test_direct" /SERVER ** /USER ***** /PASSWORD ****

/CONNECTION "DataSource-Excel";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\test\testshare\aaab2.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"

/CONNECTION "Test.NGAEarth";"Data Source=Test;Initial Catalog=NGAEarth;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.NGAEarth"

/set "\Package.Variables[User::varPreSQLAction].Properties[Value]";"delete from Features where type = ''aaab''"

/set "\Package.Variables[User::varExcelWorkbookName].Properties[Value]";"Sheet1$"

/set "\Package.Variables[User::varDestinationTableName].Properties[Value]";"Features"

/set "\Package.Variables[User::varExcelFileFullPath].Properties[Value]";"\\test\testShare\aaab2.xls"

'

Actually, I tried to change other variables. E.g., I changed the file name from aaab2.xls to aaab.xls, Excel worksheet name from Sheet1$ to Sheet2$.

It seems the package never get the parameters I set as above. It always gets the values I set in design time. The errors I got are:

Error: 2006-02-01 15:54:23.58

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

|||

Finally I found the problem:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming