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>
