SSIS Execute SQL Task and RaisError

If you want to use sp to call an exe program, many ppl reported the raiserror function cannot manually fail a SSIS package step. The problem is we should set the raiserror function parameter severity to be 11 above (11-16). (RAISERROR with an error code of 11 (which is a level SSIS should recognize) ), here is the link I found: http://ulrichandhelvas.wordpress.com/2007/04/04/ssis-ole-db-connection-managers-no-errors-allowed/.

The situation is if you have an exe program, and you want to write a sp to call the exe, when include the sp in your SSIS, if the exe exit code is not 0, the SSIS step will also fail.

I wrote an sp:

CREATE PROCEDURE usp_MyProc

AS
BEGIN
SET NOCOUNT ON;

DECLARE @SQL AS VARCHAR(800)
DECLARE @ret INT

BEGIN
SET @SQL = "C:\Mytest\test.exe"
EXEC @ret = myserver.master.dbo.xp_cmdshell @SQL
IF( @ret <> 0)
BEGIN
RAISERROR ('An error occured',11,1)
END
ELSE
BEGIN
Print 'good'
END

END

END


In this case if my test.exe exit 1, my SSIS step will aslo fail.

Reflection-Call the Method in a Type

Here we want to write code equivallent to:

DateTime d = new DateTime(2008, 5, 1);
Console.WriteLine(d.ToShortDateString());

What we do is:

Firstly, we must create an instance of the DateTime object, which
requires you to create an instance of ConstructorInfo and then call the ConstructorInfo to Invoke method.

Type t = typeof(DateTime);
//Create a Type object
ConstructorInfo ci = t.GetConstructor(new Type[] { typeof(int), typeof(int),
typeof(int) });
//Type.GetConstructor requires a Type array,
Object d = ci.Invoke(new Object[] { 2008, 5, 1 });
//ConstructorInfo.Invoke requires an Object array.

Next, to call DateTime.ToShortDateString, we
must create an instance of MethodInfo representing the method and then
call MethodInfo.Invoke.

MethodInfo dToShortDateString = t.GetMethod("ToShortDateString");
Console.WriteLine((string)dToShortDateString.Invoke(d, null));

Reflection Related-Use BindingFlags to retrieve memebers

Type t = typeof(Console);
MemberInfo[] mi = t.GetMembers(
BindingFlags.NonPublic | BindingFlags.Static);
foreach (MemberInfo m in mi)
{
Console.WriteLine("{0}: {1}", m.Name, m.MemberType);
}

Example of using Reflection to get Constructor and Methods

We can use Assembly.Load, Assembly.LoadFile, Assembly.LoadFrom to load the assembly, and Assembly.GetType to create a Type object.

Here we just add a class, example see http://www.codersource.net/csharp_tutorial_reflection.html.

We use this example code:

public class TestDataType
{

public TestDataType()
{
counter = 1;
}

public TestDataType(int c)
{
counter = c;
}

private int counter;

public int Inc()
{
return counter++;
}
public int Dec()
{
return counter--;
}

}


In the Main class, add System.Reflection namespace. And we can use Type object's GetConstructs, GetMethods methods to get the information out.

The code is like:

TestDataType testObject = new TestDataType(15);
Type objectType = testObject.GetType();

ConstructorInfo[] info = objectType.GetConstructors();
MethodInfo[] methods = objectType.GetMethods();
foreach (ConstructInfo ci in info){....}
foreach (MethodInfo mi in methods){....}

...

Notice that, for the constructor, we get:
Void .ctor()
Void .ctor(Int32)
This is what we constructed.

For the methods, beside the one we constructed,
Int32 Inc()
Int32 Dec()
There are some implict Method, such as
System.ToString()
Equal()
GetHashCode()
We can see from ILDasm as well.