SSIS DateTimePicker in script task!!! HELP! please.
-
Im trying to get a datetimepicker (a calendary thing) for the user to give some date input.
Without luck, what Im doing wrong?
with the below code I cant get the datepicker be visible. Only a small empty form.
thanks for any help.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Drawing;
namespace ST_c99fa6d71844403e8caa48fa8f9185a1.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
public void Main()
{
Form a = new Form();
System.Windows.Forms.Application.Run(a);
DateTimePicker DateTimePicker1 = new DateTimePicker();
//Set size and location.
DateTimePicker1.Location = new System.Drawing.Point(10, 10); // OJO hubo que agregar una referencia en propiedades de proyecto a system.drawing etc
DateTimePicker1.Size = new Size(160, 21);
// Set the alignment of the drop-down MonthCalendar to right.
DateTimePicker1.DropDownAlign = LeftRightAlignment.Right;
// Set the Value property to 50 years before today.
DateTimePicker1.Value = System.DateTime.Now.AddYears(-50);
//Set a custom format containing the string "of the year"
DateTimePicker1.Format = DateTimePickerFormat.Custom;
DateTimePicker1.CustomFormat = "MMM dd, 'of the year' yyyy ";
// Add the DateTimePicker to the form.
// this.Controls.Add(DateTimePicker1);
a.Controls.Add(DateTimePicker1);
DateTimePicker1.Show();
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
-
You probably will not get much help here for this. Try posting this on an ASP.NET forum.
Jared
CE - Microsoft -
I don't really like SSIS packages having direct user input. I would rather develop a small .NET application that has the datepicker that starts an SSIS package providing a few parameters.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
thanks but does someone know if it is even posible to make somthing like I want to do?
maybe SSIS is not ready (or able) to manage complex windows forms or something...
-
oskaroh (2/21/2012)
thanks but does someone know if it is even posible to make somthing like I want to do?maybe SSIS is not ready (or able) to manage complex windows forms or something...
That's because it is not supposed to manage complex windows forms. It is an ETL tool, supposed to run without any human interaction.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I think I understand your point there. And thanks for the advice, but I need this thing my way this time.
So im glad to say I got it working! 😀
the only thing I needed was to add the datepicker to the form before set the form to running. Still got to work in take the datepicker value to a user variable but that is trivial. Anyway I'll be uptdating with the final code in case someone wants to do something alike.
-
This is how I did it, sorry for the bad programming, if any.
Create a global scope variable datetime and use it as an input (read and write) variable for a script task (in this case the language is c#)
Some strings are in spanish.
😛
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Drawing;
namespace ST_c99fa6d71844403e8caa48fa8f9185a1.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
Form Form1 = new Form();
Button button1 = new Button();
DateTimePicker DateTimePicker1 = new DateTimePicker();
DialogResult dResult;
public void Main()
{
Form1.Size = new Size(260, 200);
Form1.FormBorderStyle = FormBorderStyle.Fixed3D;
Form1.StartPosition = FormStartPosition.CenterScreen;
button1.Text = "ACEPTAR";
button1.Location = new System.Drawing.Point(95, 120);
// Set the alignment of the drop-down MonthCalendar to right.
DateTimePicker1.DropDownAlign = LeftRightAlignment.Left;
DateTimePicker1.Value = DateTime.Today.AddDays(1); // le sumo un dia pues normalmente se carga para el dia de ma?ana
Form1.Controls.Add(DateTimePicker1);
Form1.Controls.Add(button1);
//Set size and location.
DateTimePicker1.Location = new System.Drawing.Point(15,15); // tuve que agregar una referencia en project->add_reference system.drawing dll
DateTimePicker1.Size = new Size(220, 41);
/* EVENTOS */
DateTimePicker1.ValueChanged += new System.EventHandler(DTP_ValueChanged); //
button1.Click += new System.EventHandler(OnClickButton1);
System.Windows.Forms.Application.Run(Form1); // Debe ser la ultima linea
//Dts.TaskResult = (int)ScriptResults.Success;
}
/*EVENTO DATE PICKER */
private void DTP_ValueChanged(object sender, System.EventArgs e) {
dResult = MessageBox.Show("Es correcta esta fecha?: "+DateTimePicker1.Value.ToString(), "Fecha Escogida", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dResult == DialogResult.Yes) {
Dts.Variables["FechaDestino"].Value = DateTimePicker1.Value; // 'FechaDestinoStock' nombre de la variable
Form1.Controls.Remove(DateTimePicker1);
Form1.Close();
}
else Dts.Variables["FechaDestino"].Value = DateTimePicker1.Value;
}
/*EVENTO BOTON */
private void OnClickButton1(object sender, System.EventArgs e) {
Dts.Variables["FechaDestino"].Value = DateTimePicker1.Value; // 'FechaDestinoStock' nombre de la variable
Form1.Controls.Remove(DateTimePicker1);
// MessageBox.Show("Fecha: " + Dts.Variables["Fecha"].Value + "" + "Corte: " + Dts.Variables["Corte"].Value);
Form1.Close();
}
}
}
[/URL]Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply