Post reply

SSIS : using ENV var's in stored procedures.

  • January 29, 2008 at 7:56 am

    #121910

    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\ ) 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)

    AS

    declare @jid uniqueidentifier

    declare @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 job

    exec msdb.dbo.sp_add_job

    @job_name = @jname,

    @enabled= 1,

    @category_name = 'Samples',

    @delete_level= 1,

    @job_id = @jid OUTPUT

    exec 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 job

    exec msdb.dbo.sp_start_job

    @job_id = @jid

    Thanks in advance.

  • January 30, 2008 at 8:08 am

    #773314

    Firstly, it's a SSIS package, not DTS. SSIS is to DTS what SQL 2005 is to Access.

    Rule 101 of anything: Never hard-code anything parameters.

    You could get the environmental variable by using xp_regread. Not advisable though.

    A proper solution would be to have meta data somewhere. Surely your front end app has a config file of sorts. If not, a Meta data table in the DB that gets updated when you deploy would work.

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • January 30, 2008 at 8:46 am

    #773347

    You'd likely want to architect your way around this by either storing the SSIS packages in SQL Server or by pre-configuring the job on your SQL Server and then calling it by name in your stored procedure.

    As far as being able to access Env variables from within a T-SQL statement, I can't really help you there.

  • February 1, 2008 at 10:30 am

    #774335

    Thanks Crispin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply