May 15, 2009 at 2:26 pm
I am using following script to build a command and then execute it
Declare @cmd varchar(1000)
select @cmd = 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /f'' "S:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\Locations\Main.dtsx"'
print @cmd
exec master..xp_cmdshell @cmd
print @cmd line has following output:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /f' "S:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\Locations\Main.dtsx"
however when the script gets to the exec line it through out following message:
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
i guess I need to escape parenthesis.. but not having luck with this
May 15, 2009 at 3:03 pm
You need quotes around the command you are sending to shell when there are spaces in the path
Declare @cmd varchar(1000)
select @cmd = '''C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec'' /f'' "S:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\Locations\Main.dtsx"'
print @cmd
May 18, 2009 at 8:22 am
I tried the following but not luck
declare @str1 varchar(1000)
set @str1 = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTexec.exe"'
set @str1 = @str1 + ' /f S:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\Locations\Main.dtsx'
print @str1
exec master..xp_cmdshell @str1
still cant figure it out...
May 18, 2009 at 3:07 pm
can you add DTexec.exe to the enviroment variables via right clicking mycomputer and choosing properties?
Andrew
May 19, 2009 at 8:07 am
try like this
Declare @cmd varchar(1000)
select @cmd = '(''C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec'' /f'' "S:\Microsoft SQL Server\MSSQL.1\MSSQL\JOBS\Locations\Main.dtsx") '
print @cmd
May 19, 2009 at 8:23 am
Create a batch file. put the command in the bactch file.
In the stored procedure just run this batch file. It will work.
May 20, 2009 at 5:57 am
lzhang (5/19/2009)
Create a batch file. put the command in the bactch file.In the stored procedure just run this batch file. It will work.
There is no need to invoke the command-line utility.
I posted a full stored proc with the correct SQL command a few months ago . . . will try to get the code, as I am not on my work computer, but do a search for DTEXEC on SQLServerCentral.
It takes the same parameters, depending on where you saved / installed your package - /f if it's in the File System, or /S(?) if it's in MSDB. With the MSDB-resident package, you can further set variables, set username/passwords, change the /SERVER parameter ... in short, overwrite everything in the config.
May 20, 2009 at 6:10 am
OOps - a few incorrect memories above.
Found my code - it does call the DTEXEC utility with xp_cmdshell but not using the pathname.
The syntax is really difficult, but if you create and use the following "launcher" it will work:
The package called this way must be installed (with the manifest) into the SQL SERVER STORE, not the file system.
CREATE PROCEDURE [dbo].[PT_xdts_StartPackage]
(
@i_strPackageName as varchar(200)
-- NOTE: the package name above must be preceded by \ (backslash) as running from the SQL Store (not MSDB file).
-- If containing spaces, must be enclosed in double quotation marks eg "\ExportToMarquee"
,@i_strServerName as varchar (20)
,@i_strconnMgrName as varchar (60)
,@i_strCatalogName as varchar(60)
,@i_strParam1 as varchar(200) = NULL
)
AS
BEGIN -- proc
declare @cmd varchar(4000)
set @cmd = 'dtexec /SQL ' + @i_strPackageName + ' /SERVER ' + @i_strServerName + ' /CONNECTION ' + @i_strconnMgrName +';"Data Source=' + @i_strServerName + ';User ID=sa;Password=THisisClearTextPwd;Initial Catalog='
set @cmd = @cmd + @i_strCatalogName + ';Provider=SQLNCLI.1;Persist Security Info=True;" '
set @cmd = @cmd + ' /USER sa /PASSWORD TheClearTextPasswordAgain /CHECKPOINTING OFF /REPORTING V '
if @i_strParam1 IS NOT NULL
set @cmd = @cmd + @i_strParam1
exec master.dbo.xp_cmdshell @cmd
END -- proc
This would be called from another SP which sets the values of the variables (remember the \ in the package name).
PT_sp_ImportData accepts a single parameter from its own caller (the application)
(that is what is expected in the SSIS package as strLocationID and gets passed on to the Launcher in the param argument)
CREATE PROCEDURE [dbo].[PT_sp_ImportData]
(
@i_varLocation varchar(20) = NULL
) AS
begin -- proc
declare @intErrorNum int
if (@i_varLocation is null)
begin
select @i_varLocation = '1'
end
set @strCMDSSIS = ' /set \Package.Variables[User::strLocationID].Properties[Value];' + @i_varLocation
exec @intErrorNum = dbo.PT_xdts_StartPackage '\mySSISpackage'
,'SQLSrv_instance'
,'connManager_In_SSISpkg'
,'myDatabaseName'
, @strCMDSSIS
end -- proc
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply