Post reply

DYNAMIC SQL

  • September 23, 2011 at 9:38 am

    #148716

    Hi All,

    I have to create a job with the following command

    N'/FILE "C:\Packages\Test Folder\Package.dtsx" /CONFIGFILE "C:\Packages\Test Folder\Package.dtsConfig" /CHECKPOINTING OFF/SET "\Package.Variables[User::ID].Properties[value]";1 /REPORTING E',

    The value for the variable mentioned above is used for my testing purpose i need to write a dynamic sql to pass a value for that variable. Can someone help me write a dynamic sql to pass the value.

    Thanks!

  • September 23, 2011 at 10:30 am

    #1385891

    I came up with a solution

    DECLARE @cmd VARCHAR(4000)

    DECLARE @ID INT

    SET @ID = 1

    SELECT @cmd = '/FILE "C:\Packages\Test Folder\Package.dtsx" /CONFIGFILE "C:\Packages\Test Folder\Package.dtsConfig" /SET "\Package.Variables[User::ID].Properties[value]";'

    SELECT @cmd = @cmd+ CONVERT(VARCHAR(100), @ID) + '/CHECKPOINTING OFF/REPORTING E'

    but wen i execute the job it fails with the following error:

    Message

    Executed as user: Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "\Package.Variables[User::ID].Properties[value];1/CHECKPOINTING" is not valid. The command line parameters are invalid. The step failed.

    Please help me solve this issue

    Thanks! in advance

  • September 23, 2011 at 10:38 am

    #1385895

    I'm assuming SSIS step..

    You might look at adding a space:

    ];1 /CHECKPOINTING

    CEWII

  • September 23, 2011 at 11:02 am

    #1385924

    Thanks Elliott! that worked but still the job failed with this message

    Message

    Executed as user: . Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:00:30 PM DTExec: Could not set \Package.Variables[User::ID].Properties[value] value to 1. Started: 1:00:30 PM Finished: 1:00:30 PM Elapsed: 0.406 seconds. The package execution failed. The step failed.

  • September 23, 2011 at 11:27 am

    #1385944

    check to make sure there are appropriate spaces and I'm not sure if SSIS is case sensitive in this case but I'm going to go down that path and the V in value might need to be upper case..

    CEWII

  • September 23, 2011 at 1:00 pm

    #1386004

    Thanks a millions Elliot....!

Viewing 6 posts - 1 through 5 (of 5 total)

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