June 10, 2014 at 5:06 pm
Hi all,
I need some help with a script in SSIS to check data connections/sources and send an email if it fails validation.
I found this script online, but I'm not real good with scripting and only VB scripting at that.
How can I modify this script or is there another one here that will do what I need. The data connection in question links to Salesforce and will fail validation if the password/security token is invalid.
The reason I need this script is that we have no control over the password changes and need to know if the salesforce team changes the password without informing us.
Here's the script that I'm putting as the 1st step in the package.
Thanks for any help.
#region Help: Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
* a .Net application within the context of an Integration Services control flow.
*
* Expand the other regions which have "Help" prefixes for examples of specific ways to use
* Integration Services features within this script task. */
#endregion
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion
namespace ST_bf7c41ed885c43a1bd1cbf7895952577
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region Help: Using Integration Services variables and parameters in a script
/* To use a variable in this script, first ensure that the variable has been added to
* either the list contained in the ReadOnlyVariables property or the list contained in
* the ReadWriteVariables property of this script task, according to whether or not your
* code needs to write to the variable. To add the variable, save this script, close this instance of
* Visual Studio, and update the ReadOnlyVariables and
* ReadWriteVariables properties in the Script Transformation Editor window.
* To use a parameter in this script, follow the same steps. Parameters are always read-only.
*
* Example of reading from a variable:
* DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
*
* Example of writing to a variable:
* Dts.Variables["User::myStringVariable"].Value = "new value";
*
* Example of reading from a package parameter:
* int batchId = (int) Dts.Variables["$Package::batchId"].Value;
*
* Example of reading from a project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].Value;
*
* Example of reading from a sensitive project parameter:
* int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
* */
#endregion
#region Help: Firing Integration Services events from a script
/* This script task can fire events for logging purposes.
*
* Example of firing an error event:
* Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
*
* Example of firing an information event:
* Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
*
* Example of firing a warning event:
* Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
* */
#endregion
#region Help: Using Integration Services connection managers in a script
/* Some types of connection managers can be used in this script task. See the topic
* "Working with Connection Managers Programatically" for details.
*
* Example of using an ADO.Net connection manager:
* object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
* SqlConnection myADONETConnection = (SqlConnection)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
*
* Example of using a File connection manager
* object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
* string filePath = (string)rawConnection;
* //Use the connection in some code here, then release the connection
* Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
* */
#endregion
/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
bool failure = false;
bool fireAgain = true;
foreach (var ConnMgr in Dts.Connections)
{
Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
ConnMgr.Name, ConnMgr.ConnectionString), "", 0, ref fireAgain);
try
{
ConnMgr.AcquireConnection(null);
Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",
ConnMgr.Name), "", 0, ref fireAgain);
}
catch (Exception ex)
{
Dts.Events.FireError(-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",
ConnMgr.Name, ex.Message),
"", 0);
failure = true;
}
}
if (failure)
Dts.TaskResult = (int)ScriptResults.Failure;
else
Dts.TaskResult = (int)ScriptResults.Success;
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}
June 11, 2014 at 12:26 am
Why doesn't this script work for you? Does it give an error?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 12:49 am
Koen Verbeeck (6/11/2014)
Why doesn't this script work for you? Does it give an error?
No error, it seems to just be passed by when I have bad credentials in the connection manager. It seems to execute, and the package stops in the validation phase, but my on error e-mail does not get sent.
Like I mentioned, I'm not at all familiar with C# programming/scripting and don't know how to extend this script to include the e-mail piece on failure.
June 11, 2014 at 12:56 am
craig.bobchin (6/11/2014)
Koen Verbeeck (6/11/2014)
Why doesn't this script work for you? Does it give an error?No error, it seems to just be passed by when I have bad credentials in the connection manager. It seems to execute, and the package stops in the validation phase, but my on error e-mail does not get sent.
Like I mentioned, I'm not at all familiar with C# programming/scripting and don't know how to extend this script to include the e-mail piece on failure.
You don't really need to extend the script. You can just fail it, and than add a Send Email Task after the script which you connect with an OnFailure precedence constraint (the red arrow).
The only thing you need to make sure is that the script actually works 🙂
Maybe you can add a message box that says "connection has failed" just to make sure that the script task is doing its job.
One small bit of advice: I never had any formal training in C#, but in time, I learned the basics of scripting by just diving in. I really encourage you to do the same thing. Just google around, read the scripts and look up the syntax that you don't know. Try to understand what people post in forums or on Stack Overflow. You'll only benefit in the end.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 9:16 am
Koen Verbeeck (6/11/2014)
craig.bobchin (6/11/2014)
Koen Verbeeck (6/11/2014)
Why doesn't this script work for you? Does it give an error?No error, it seems to just be passed by when I have bad credentials in the connection manager. It seems to execute, and the package stops in the validation phase, but my on error e-mail does not get sent.
Like I mentioned, I'm not at all familiar with C# programming/scripting and don't know how to extend this script to include the e-mail piece on failure.
You don't really need to extend the script. You can just fail it, and than add a Send Email Task after the script which you connect with an OnFailure precedence constraint (the red arrow).
The only thing you need to make sure is that the script actually works 🙂
Maybe you can add a message box that says "connection has failed" just to make sure that the script task is doing its job.
One small bit of advice: I never had any formal training in C#, but in time, I learned the basics of scripting by just diving in. I really encourage you to do the same thing. Just google around, read the scripts and look up the syntax that you don't know. Try to understand what people post in forums or on Stack Overflow. You'll only benefit in the end.
The issue is I don't think the script gets executed. When I try and run the Package in BIDS with the wrong credentials in it as a test, the package fails validation and stops before it ever gets to the script to execute it.
So maybe a better way of dealing with this is to forego a script and figure out a way of sending an e-mail if the package fails validation.
Do you know how I can do that? I already have delay validation set to true on the connection manager and that does not help.
June 11, 2014 at 12:01 pm
Also set DelayValidation on the package to true.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 12:08 pm
Koen Verbeeck (6/11/2014)
Also set DelayValidation on the package to true.
I have it set to true for the connection manager which made no difference, but see no where in either package or project properties to set a DelayValidation property.
June 11, 2014 at 12:10 pm
craig.bobchin (6/11/2014)
Koen Verbeeck (6/11/2014)
Also set DelayValidation on the package to true.I have it set to true for the connection manager which made no difference, but see no where in either package or project properties to set a DelayValidation property.
Click in the control flow canvas. Hit F4. Look for DelayValidition.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 12:30 pm
Koen Verbeeck (6/11/2014)
craig.bobchin (6/11/2014)
Koen Verbeeck (6/11/2014)
Also set DelayValidation on the package to true.I have it set to true for the connection manager which made no difference, but see no where in either package or project properties to set a DelayValidation property.
Click in the control flow canvas. Hit F4. Look for DelayValidition.
Got it. It worked. But I'm getting an invalid entry point error on the script even though I'm using main.
June 11, 2014 at 12:36 pm
What if you delete the script component, add a new one and copy paste only the following piece of code into the main() function?
bool failure = false;
bool fireAgain = true;
foreach (var ConnMgr in Dts.Connections)
{
Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
ConnMgr.Name, ConnMgr.ConnectionString), "", 0, ref fireAgain);
try
{
ConnMgr.AcquireConnection(null);
Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",
ConnMgr.Name), "", 0, ref fireAgain);
}
catch (Exception ex)
{
Dts.Events.FireError(-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",
ConnMgr.Name, ex.Message),
"", 0);
failure = true;
}
}
if (failure)
Dts.TaskResult = (int)ScriptResults.Failure;
else
Dts.TaskResult = (int)ScriptResults.Success;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 11, 2014 at 12:47 pm
Koen Verbeeck (6/11/2014)
What if you delete the script component, add a new one and copy paste only the following piece of code into the main() function?
bool failure = false;
bool fireAgain = true;
foreach (var ConnMgr in Dts.Connections)
{
Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', ConnectionString='{1}'",
ConnMgr.Name, ConnMgr.ConnectionString), "", 0, ref fireAgain);
try
{
ConnMgr.AcquireConnection(null);
Dts.Events.FireInformation(1, "", String.Format("Connection acquired successfully on '{0}'",
ConnMgr.Name), "", 0, ref fireAgain);
}
catch (Exception ex)
{
Dts.Events.FireError(-1, "", String.Format("Failed to acquire connection to '{0}'. Error Message='{1}'",
ConnMgr.Name, ex.Message),
"", 0);
failure = true;
}
}
if (failure)
Dts.TaskResult = (int)ScriptResults.Failure;
else
Dts.TaskResult = (int)ScriptResults.Success;
Bingo!!!!! That did it. it works now. Thank you for all your help.
June 11, 2014 at 12:49 pm
craig.bobchin (6/11/2014)
Bingo!!!!! That did it. it works now. Thank you for all your help.
No problem, glad it worked out.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply