How do I prompt for input from an SSIS package?
This can be acheived as below: This will be in your intial script component.
System.Windows.Forms.Form frm = new Form();
TextBox txt = new TextBox();
Button inputset = new Button();
public void Main()
{
inputset.Text = "Set Variable Value";
inputset.Width = 200;
inputset.Height = 100;
inputset.Click += new EventHandler(inputset_Click);
txt.Name = "Input";
frm.Controls.Add(txt);
frm.Controls.Add(inputset);
frm.ShowDialog();
MessageBox.Show(Dts.Variables["Value1"].Value.ToString());
Dts.TaskResult = (int)ScriptResults.Success;
}
void inputset_Click(object sender, EventArgs e)
{
Dts.Variables["Value1"].Value = Convert.ToInt32(txt.Text);
frm.Close();
}
This should be the initial component in your package to set the variable value or construct you SQL Command.
Running SSIS Package through command line with user input as variable
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>
Can I prompt user for Field Mappings in SSIS package?
SSIS packages are generally headless because they typically will run as a scheduled job somewhere on a database server. That said, there are definitely ways to do this.
One option that I have used is SQL Management Objects (SMO) to connect to the SQL Server Agent where the job is hosted. A client can interactively run such a job and even update the user on execution status. The same client could ask the user for input prior to kicking off the job, and you could store such input in a place where the package can access it.
Passing parameters to a package from command prompt
you are trying to pass a string value which need to be double quoted, you have to add \" at the beginning and the end of value
dtexec.exe /f c:\pkg1.dtsx /set "\Package.Variables[User::var1].Value";\""test"\"
How to call .dtsx file which has input parameters from a stored procedure?
Using DtExec and xp_cmdshell
One way to do that is to run DtExec utility from file system using xp_cmdshell utility inside sql server.
First you have to enable the xp_cmdshell utility:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
-- WITH OVERRIDE disables the configuration value checking if the value is valid
RECONFIGURE WITH OVERRIDE
GO
-- To enable the xp_cmdshell component.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Revert back the advance option
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO
Then you can use the following command to execute the package and pass avariable value as parameter:
DECLARE @SQLQuery AS VARCHAR(2000)
DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'
SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'
EXEC master..xp_cmdshell @SQLQuery
GO
References
- Executing a SSIS Package from Stored Procedure in SQL Server
- Run an SSIS package from the command prompt with DTExec.exe
- DTEXEC Command Line Parameters Using Command Files
- dtexec Utility
Helpful links
- How to Call SSIS Package from the Stored Procedure
- Run an SSIS package from SSMS with Transact-SQL (if using SSISDB)
- How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure (if using SSISDB)
Getting error while passing input parameter in package using cmd
I think you need to escape EOL character with ^ in your script
dtexec.exe /FILE "D:\Share\CRM#96012 - SSIS Package Live to Tr-28_08_2019\Move_live_to_training_Object\Move_live_to_training_Object\Live_To_Training_Db_Objects.dtsx" ^
/SET \Package.Variables[User::DesDBName].Properties[Value];"FireTrMJ" ^
/SET \Package.Variables[User::Password].Properties[Value];"Pnx@2017" ^
/SET \Package.Variables[User::ServerName].Properties[Value];"192.168.10.157" ^
/SET \Package.Variables[User::SourceDBName].Properties[Value];"FireLiveMJ" ^
/SET \Package.Variables[User::UserName].Properties[Value];"sa"
Related Topics
How to Perform a Bitwise Group Function
Select Distinct Values from 1 Column
#1146 - Table 'Phpmyadmin.Pma_Tracking' Doesn't Exist
Counting Rows for All Tables at Once
Recursive Query Used for Transitive Closure
In SQL Is There a Difference Between Count(*) and Count(<Fieldname>)
How to Get All Article Pages Under a Wikipedia Category and Its Sub-Categories
Postgresql: Fill Null Values in Timeserie Query with Previous Value
Is Null VS = Null in Where Clause + SQL Server
Pros and Cons of Autoincrement Keys on "Every Table"
Conversion to Datetime Fails Only on Where Clause
Fill Null Values with Last Non-Null Amount - Oracle SQL