Running SSIS Package through command line with user input as variable

Question

I wanted to move a file from one location to another, in my SSIS package i have set the source in a file system task. I have created a variable called Path which is

C:\Users\VishalJethwa\Documents\Projects\Batch File SSIS Package Executer\

and a blank variable called Destination (which will be the folder name set in cmd) and finally a full path variable which concatenates both together to form the destination property in the file system task. I have tried the following to make it so the user inputs the folder name that they want to move the file to but it doesnt seem to work. I get the error

, this is the error messaeg Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 13:55:48 Error: 2014-02-04 13:55:48.81 Code: 0xC002F304 Source: Move File File System Task Description: An error occurred with the following error message: "Could not f ind file 'C:\Users\VishalJethwa\Documents\Projects\Batch File SSIS Package Execu ter\Source\Plank.txt'.". End Error Progress: 2014-02-04 13:55:48.81 Source: Move File Operation Complete: 100% complete End Progress Warning: 2014-02-04 13:55:48.81 Code: 0x80019002 Source: Move File Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allow ed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the e rrors. End Warning Warning: 2014-02-04 13:55:48.81 Code: 0x80019002 Source: BatchFileTest Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allow ed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the e rrors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:55:48 Finished: 13:55:48 Elapsed: 0.125 seconds '/SET' is not recognized as an internal or external command, operable program or batch file.

Below is the code in my batch file

@ECHO OFF
SET /p Loc = What folder do you wish to copy it to?: 
DTEXEC /FILE "C:\Users\Admin\Documents\Projects\Batch File SSIS Package Executer\BatchFileTest\BatchFileTest\BatchFileTest.dtsx" 
/SET "\package.Variables[User::Destination].Value";Loc
pause

Any ideas?

Answer 1

You have 3 issues.

Issue the first

While awaiting a response to my comment, my prime assumption is that this a casing issue. Entities in an SSIS package are case sensitive.

The listed code attempts to set the value of \package.Variables to a value. There is no package available, only Package

Thus /SET "\Package.Variables[User::Destination].Value";Loc

You are probably going to want to also pass Loc in under double quotes unless it's going to be a DOS 8.3 style name.

Issue the second

The use of Loc is going to be the literal string Loc. You need to use the variable %Loc%

Issue the third

You are not assigning a value to Loc. You cannot have spaces between the equal signs in DOS

Final results

I created a sample package, SetDestination, with a single Script Task that does nothing more than fire the value of Destination with the OnInformation event. The code in there is simply

    public void Main()
    {
        bool fireAgain = false;
        Dts.Events.FireInformation(0, "emit", string.Format("Destination: {0}", Dts.Variables[0].Value.ToString()), string.Empty, 0, ref fireAgain);
        Dts.TaskResult = (int)ScriptResults.Success;
    }

I then modified your batch file as so

@ECHO OFF
set loc=unset
SET /p Loc=What folder do you wish to copy it to?:
"C:\Program Files (x86)\microsoft sql server\110\dts\binn\dtexec.exe" /FILE "C:\Users\bfellows\Documents\Visual Studio 2012\Projects\SSISPOC\PackageDeploymentModel\SetDestination.dtsx"  /SET "\Package.Variables[User::Destination].Value";"%Loc%" /rep i
pause

Execution results

What folder do you wish to copy it to?:Stuff
Microsoft (R) SQL Server Execute Package Utility
Version 11.0.3401.0 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved.

Started:  8:23:15 AM
Info: 2014-02-04 08:23:15.84
   Code: 0x00000000
   Source: SCR Emit emit
   Description: Destination: Stuff
End Info
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  8:23:15 AM
Finished: 8:23:15 AM
Elapsed:  0.188 seconds
Press any key to continue . . .

Biml

For those following along at home, assuming you have the free Bids Helper add on installed, the following Biml creates the reference package. Use the above batch file, with a corrected .dtsx location and SQL Server version, to invoke it and you should get the same results as I do.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="SetDestination" ConstraintMode="Parallel" ProtectionLevel="DontSaveSensitive">
            <Variables>
                <Variable DataType="String" Name="Destination"></Variable>
            </Variables>
            <Tasks>
                <Script ProjectCoreName="ST_232fecafb70a4e8a904cc21f8870eed0" Name="SCR Emit Destination">
                    <ReadOnlyVariables>
                        <ReadOnlyVariable VariableName="User.Destination" />
                    </ReadOnlyVariables>
                    <ScriptTaskProject>
                        <ScriptTaskProject ProjectCoreName="ST_c41ad4bf47544c49ad46f4440163feae" Name="TaskScriptProject1">
                            <AssemblyReferences>
                                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
                                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
                                <AssemblyReference AssemblyPath="System.dll" />
                                <AssemblyReference AssemblyPath="System.AddIn.dll" />
                                <AssemblyReference AssemblyPath="System.Data.dll" />
                                <AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
                                <AssemblyReference AssemblyPath="System.Xml.dll" />
                            </AssemblyReferences>
                            <Files>
                                <File Path="AssemblyInfo.cs">
                                    using System.Reflection;
                                    using System.Runtime.CompilerServices;

                                    //
                                    // General Information about an assembly is controlled through the following
                                    // set of attributes. Change these attribute values to modify the information
                                    // associated with an assembly.
                                    //
                                    [assembly: AssemblyTitle("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
                                    [assembly: AssemblyDescription("")]
                                    [assembly: AssemblyConfiguration("")]
                                    [assembly: AssemblyCompany("Varigence")]
                                    [assembly: AssemblyProduct("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
                                    [assembly: AssemblyCopyright("Copyright @ Varigence 2013")]
                                    [assembly: AssemblyTrademark("")]
                                    [assembly: AssemblyCulture("")]
                                    //
                                    // Version information for an assembly consists of the following four values:
                                    //
                                    //      Major Version
                                    //      Minor Version
                                    //      Build Number
                                    //      Revision
                                    //
                                    // You can specify all the values or you can default the Revision and Build Numbers
                                    // by using the '*' as shown below:

                                    [assembly: AssemblyVersion("1.0.*")]
                                </File>
                                <File Path="ScriptMain.cs">
                                    using System;
                                    using System.Data;
                                    using Microsoft.SqlServer.Dts.Runtime;
                                    using System.Windows.Forms;

                                    // if SSIS2012, use the following line:
                                    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

                                    // if earlier version, use the next line instead of the above line:
                                    // [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
                                    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
                                    {
                                    enum ScriptResults
                                    {
                                    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                                    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
                                    };

                                    public void Main()
                                    {
                                    bool fireAgain = false;
                                    Dts.Events.FireInformation(0, "emit", string.Format("Destination: {0}", Dts.Variables[0].Value.ToString()), string.Empty, 0, ref fireAgain);
                                    Dts.TaskResult = (int)ScriptResults.Success;
                                    }
                                    }
                                </File>
                            </Files>
                        </ScriptTaskProject>
                    </ScriptTaskProject>
                </Script>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Answer 2

I think you are missing the % around the Loc

@ECHO OFF
SET /p Loc = What folder do you wish to copy it to?: 
DTEXEC /FILE "C:\Users\Admin\Documents\Projects\Batch File SSIS Package Executer\BatchFileTest\BatchFileTest\BatchFileTest.dtsx" 
/SET "\package.Variables[User::Destination].Value";"%Loc%"
pause

As billinkc said putting them in "" will help if you are sending a location that contains spaces.