How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

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

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

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-01-30 15:49:52.46

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-01-30 15:49:52.51

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

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

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

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell