I have been asked to alter how we run the SSIS package. They do not want to use SQL Server Agent. They want to have Python to execute in the command shell. I have the execute statement set up as the following: EXECUTE [DatabaseNameHere].[dbo].[ProcedureNameHere] 'SSISPackageNameHere', 'DirectoryOnTheSameServerHereThatTheSSISPackageRuns', 'FileNameHereEndingWithExtentionCSVHere' This works. However, they want the file to be on a different server, a central location, where each database server goes to read the file. They do not want to copy the file to each database server. I tried to run EXECUTE [DatabaseNameHere].[dbo].[ProcedureNameHere] 'SSISPackageNameHere', 'DirectoryOnTheDIFFERENTServerHereThatTheSSISPackageRuns', 'FileNameHereEndingWithExtentionCSVHere' but it did not work. What do I need to do to get the package to read a file from a different location? I had the directory "DirectoryOnTheDIFFERENTServerHereThatTheSSISPackageRuns" coded as \\FileServerNameHere\Folder1\Folder2\Folder3\Folder4lcerni

Without knowing what this ProcedureNameHere procedure does it is impossible to know how you interact with any SSIS package as we cannot see it how you call it. -JensJens K. Suessmeyer http://blogs.msdn.com/Jenss

Are these servers in domain if so then set the SQL Server Service and SSIS to run through a Generic domain user and give the read access rights for the shared folder to this user i am assuming that you will be calling the dtexec.exe via XP_CMDSHELL -------------------------------------------------------- Surender Singh Bhadauria

Hi, without knowing what your procedure procedureNameHere does, it is impossible for us to see how you interact with the SSIS Package itself. In common if you are starting a SSIS package, make sure that the starting user / the impersonated user / the proxy account for SSIS has the approriate permissions. -JensJens K. Suessmeyer http://blogs.msdn.com/Jenss

CREATE PROCEDURE [dbo].[ExecuteSSISPackage] ( @strPackageName AS VARCHAR(100), @DataFeedFileLocation AS VARCHAR(500) = NULL, @DataFeedFileName AS VARCHAR(500) = NULL --, --@returncode INT OUTPUT ) AS BEGIN -- EXECUTE [DatabaseNameHere].[dbo].[ExecuteSSISPackage] 'SSISPackageNameHere_LJC', 'D:\DataFeeds\Assets\', 'FileNameHere.20110913.csv' -- EXECUTE [DatabaseNameHere].[dbo].[ExecuteSSISPackage] 'SSISPackageNameHere_LJC', '\\servername\folder\foldername2', 'FileNameHere.20110913.csv' DECLARE @cmd VARCHAR(3000) DECLARE @strDestinationServer VARCHAR(100) DECLARE @strDestinationDB VARCHAR(100) SET @strDestinationServer = (SELECT @@SERVERNAME) SET @strDestinationDB = (SELECT DB_NAME()) --========================================================================================================= IF @strPackageName = 'SSISPackageNameHere_LJC' BEGIN DECLARE @dtsExecCmd VARCHAR(4000) DECLARE @SSISPkgFilePath VARCHAR(4000) SET @SSISPkgFilePath = '\SSISPackageNameHere_LJC' DECLARE @SSISPkgConfigFilePath VARCHAR(4000) SET @SSISPkgConfigFilePath = CASE --Development --WHEN @strDestinationServer='Server1' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHereDev\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server1' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere_LJC\SSISPackageNameHere_LJC.dtsConfig' WHEN @strDestinationServer='Server1\DEV2' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHereDev - DEV2\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server1\DEV3' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHereDev - DEV3\SSISPackageNameHere.dtsConfig' --Production WHEN @strDestinationServer='Server3' THEN 'D:\databases (x86)\90\DTS\Packages\SSISPackageNameHere Default\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server4\I1' THEN 'D:\databases (x86)\90\DTS\Packages\SSISPackageNameHere - I1\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server5\I1' THEN 'D:\databases (x86)\90\DTS\Packages\SSISPackageNameHere - I1\SSISPackageNameHere.dtsConfig' --QA WHEN @strDestinationServer='Server6' THEN 'C:\SQL_DTSX_Files\Asset Script\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server6\QA2' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere - QA2\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server6\QA3' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere - QA3\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server6\QA4' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere - QA4\SSISPackageNameHere.dtsConfig' --UAT WHEN @strDestinationServer='Server7' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere-UAT\SSISPackageNameHere.dtsConfig' WHEN @strDestinationServer='Server7\UAT2' THEN 'C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere - UAT2\SSISPackageNameHere.dtsConfig' END --PRINT @SSISPkgConfigFilePath SET @dtsExecCmd = 'dtexec /SQL "' + LTRIM(RTRIM(@ssisPkgFilePath)) + '"' SET @dtsExecCmd = @dtsExecCmd + ' /conf "' + @ssisPkgConfigFilePath + '"' -- SSIS package config file for Database Connection SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::FileLocation].Properties[Value]";"\"' + @DataFeedFileLocation + '\""' SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::FileName].Properties[Value]";"\"' + @DataFeedFileName + '\""' --PRINT 'Print DTEXEC Command for debugging purposes: ' + @dtsExecCmd + ' --' SET @cmd = @dtsExecCmd --This is how the command line should look as an example: -- dtexec /SQL "\SSISPackageNameHere_LJC" /conf "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Packages\SSISPackageNameHere_LJC\SSISPackageNameHere_LJC.dtsConfig" /SET "\package.Variables[User::FileLocation].Properties[Value]";"\"D:\DataFeeds\Assets\\"" /SET "\package.Variables[User::FileName].Properties[Value]";"\"FileNameHere.20110902.csv\"" END --========================================================================================================= --EXEC @returncode = xp_cmdshell @cmd, NO_OUTPUT EXEC xp_cmdshell @cmd, NO_OUTPUT END lcerni

so dtexec will be called by the SQL Servers Service using xp_cmdshell thats why set the service logon account to a domain user and then set the rights for the shared folder -------------------------------------------------------- Surender Singh Bhadauria