Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2008-01-29 : 09:53:27
|
| Hi, I have a small requirement because of which i am facing some migration problems. For changing the variables say InitialCatalog and Custom logging destination path, i am making use of XML configuration files. Till here i have no issues. But problem starts here. Basically DTS package is called by calling a stored procedure from a front end application. Within the stored procedure the package location say (D:\Packages\<packagename.dtsx>) is being hard coded due to which i need to change the package location paths each time when i am migrating the stored procedures to Testing and Production environments. Is there any efficient way of avoiding the hard coding of the package paths using Windows Environment vars or something else. If so please help me out. Any help would be greatly appreciated.create procedure spexecDTS_Pkg@g_p1 varchar(50),@g_p2 varchar(50)ASdeclare @jid uniqueidentifierdeclare @cmd varchar(4000)SET @cmd = '"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /F "D:\Packages\test.dtsx" 'SET @cmd = @cmd + ' /SET "\Package.Variables[User::userid].Properties[Value]";'+'"'+@g_p1+'"'SET @cmd = @cmd + ' /SET "\Package.Variables[User::cname].Properties[Value]";'+'"'+@g_p2+'"'DECLARE @jname varchar(128)SET @jname = cast(newid() as char(36)) -- Create jobexec msdb.dbo.sp_add_job @job_name = @jname, @enabled = 1, @category_name = 'Samples', @delete_level = 1, @job_id = @jid OUTPUTexec msdb.dbo.sp_add_jobserver @job_id = @jid, @server_name = '(local)'exec msdb.dbo.sp_add_jobstep @job_id = @jid, @step_name = 'Execute DTS', @subsystem = 'CMDEXEC', @command = @cmd-- Start jobexec msdb.dbo.sp_start_job @job_id = @jidThanks in advance. |
|
|
|
|
|
SSIS: using windows environment var's in sp's