INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

XML Source - failed the pre-execute phase and returned error code 0x80070057

XML Source - failed the pre-execute phase and returned error code 0x80070057

XML Source - failed the pre-execute phase and returned error code 0x80070057

(OP)
I have a parent SSIS that uses a script for calling a child package that parses XML and dumps it into tables. The child package runs fine when standalone but fails throwing the error when called from the parent. All of the child GUIDs have been updated. "POLICIES" is the first XML node. I am not thinking that this is a script problem because it recognizes the XML Source and names the first node but C# scripts are not my thing. The error and script are below. Any ideas on why this error is happening? Thanks in advance for any help. There are lots of suggestions found through Google but nothing works so far.

ERROR:
XML Source - POLICIES failed the pre-execute phase and returned error code 0x80070057.

SCRIPT:
public void Main()
{

try
{

//------------------ Method / Code copied from ------------------------
//// http://benefic.net/run-ssis-child-packages-with-sc...
//---------------------------------------------------------------------

string sMgaId = Dts.Variables["MgaId"].Value.ToString();
string sBatchId = Dts.Variables["BatchId"].Value.ToString();
string sCurrentFileMask = Dts.Variables["CurrentFileMask"].Value.ToString();
string sXMLtoProcess = Dts.Variables["XMLtoProcess"].Value.ToString();
string sArchiveBad = Dts.Variables["XMLArchiveBad"].Value.ToString();
string sArchiveGood = Dts.Variables["XMLArchiveGood"].Value.ToString();
string sMgaName = Dts.Variables["MgaName"].Value.ToString();
string sEnv = Dts.Variables["Env"].Value.ToString();
string sChildPackage = Dts.Variables["XSDVersion"].Value.ToString();


//---------------------------------------------------------------------------------
// Using the Environment variable determine the location of the child package
//---------------------------------------------------------------------------------
string sPkgLocation = "";
switch (sEnv)
{
case "UAT":
sPkgLocation = "\\\\usplclwpdb005\\CNPS03_Morpheus\\SSIS\\MGA\\";
break;

case "PROD":
sPkgLocation = "\\\\usplwswpdb001\\NDS1_Morpheus\\SSIS\\MGA\\";
break;

default:
sPkgLocation = "\\\\usplwswpdb009\\WNPS03_Morpheus\\SSIS\\MGA\\";
break;
}



//----------------------------------
// Create a new Package object
//----------------------------------
Package oPkg;
Microsoft.SqlServer.Dts.Runtime.Application oApp = new Microsoft.SqlServer.Dts.Runtime.Application();

//-------------------------------------------------------------------------------------
// Create a child package listener... this is so we can see the return status of the
// subpackage before returning control back to the main package
//-------------------------------------------------------------------------------------
ChildEventListener eventListener = new ChildEventListener(Dts);

//--------------------------------------------------------------------------
// Set the Package location, name and eventlistener to the package object
//--------------------------------------------------------------------------

//oPkg = oApp.LoadPackage(packageLocation, eventListener);
oPkg = oApp.LoadPackage(sPkgLocation + sChildPackage, eventListener);


//----------------------------------
// Set Child package variables
//----------------------------------
WriteVariable("User::MgaId", sMgaId, oPkg);
WriteVariable("User::BatchId", sBatchId, oPkg);
WriteVariable("User::CurrentFileMask", sCurrentFileMask, oPkg);
WriteVariable("User::XMLtoProcess", sXMLtoProcess, oPkg);
WriteVariable("User::XMLArchiveGood", sArchiveGood, oPkg);
WriteVariable("User::XMLArchiveBad", sArchiveBad, oPkg);
WriteVariable("User::MgaName", sMgaName, oPkg);


PkgVarsSet(oPkg, "User::MgaId,User::BatchId,User::CurrentFileMask,User::XMLtoProcess,User::XMLArchiveGood,User::XMLArchiveBad,User::MgaName");


ReadVariable("User::MgaId", oPkg);
ReadVariable("User::BatchId", oPkg);
ReadVariable("User::MgaName", oPkg);
ReadVariable("User::XSDVersion", oPkg);
ReadVariable("User::XMLtoProcess", oPkg);
ReadVariable("User::XMLArchiveGood", oPkg);
ReadVariable("User::XMLArchiveBad", oPkg);
ReadVariable("User::CurrentFileMask", oPkg);
ReadVariable("User::XMLDataVariable", oPkg);
ReadVariable("User::PolicyCount", oPkg);





//-------------------------
// Execute child package.
//-------------------------
Dts.TaskResult = (int)oPkg.Execute(null, null, eventListener, null, null);

// If the subpackage did not fail
if (Dts.TaskResult != (int)DTSExecResult.Success)
{
Dts.Variables["ErrMsg"].Value = string.Concat(eventListener.ErrMsg.ToString(), " - ", eventListener.WarningMsg.ToString());

Dts.TaskResult = (int)DTSExecResult.Failure;
}
else
{
Dts.Variables["ErrMsg"].Value = string.Concat(eventListener.ErrMsg.ToString(), " - ", eventListener.WarningMsg.ToString());

Dts.TaskResult = (int)DTSExecResult.Success;
}
return;

}
catch (Exception e)
{ // Write the Error message to the Parent package variable for reporting
Dts.Variables["ErrMsg"].Value = e.ToString();
return;
}

}

//-------------------------------------------------------------------------------------------------
// ChildEventListener
// A custom EventListener that re-throws child package events in the master package.
// Useful when running a master package for identifying why a child package failed in the error
// messages viewable in visual studio, SQL agent job history log file viewer, etc.
//-------------------------------------------------------------------------------------------------
class ChildEventListener : DefaultEvents
{
public string ErrMsg = "";
public string WarningMsg = "";

//----------------------------------------------------------------------------------------------
// The constructor
// Takes a dts as an argument so errors can be re-thrown against the currently running dts.
//----------------------------------------------------------------------------------------------
Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts;
public ChildEventListener(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts)
{
this.dts = dts;
}

public override bool OnError(DtsObject source, int errorCode, string subComponent, string description
, string helpFile, int helpContext, string idofInterfaceWithError)
{
ErrMsg = string.Concat("[ERROR]: ", description);
return true;
}

//public override void OnInformation(DtsObject source, int informationCode, string subComponent
// , string description, string helpFile, int helpContext
// , string idofInterfaceWithError, ref bool fireAgain)
//{
// ErrMsg = string.Concat(ErrMsg, " -- INFO -- ", description);
//}

public override void OnWarning(DtsObject source, int warningCode, string subComponent, string description
, string helpFile, int helpContext, string idofInterfaceWithError)
{
WarningMsg = string.Concat("[WARNING]: ", description);
}
}


private object ReadVariable(string varName)
{
object result;
try {
Variables vars = null;
Dts.VariableDispenser.LockForRead(varName);
Dts.VariableDispenser.GetVariables(ref vars);
try {
result = vars[varName].Value;
}
catch (Exception e) {
throw e;
}
finally {
vars.Unlock();
}
}
catch (Exception e) {
throw e;
}

return result;
}

//------------------------------------
// Read a variable in a package.
//------------------------------------
private object ReadVariable(string varName, Package pkg) {
object result;
try {
Variables vars = null;
pkg.VariableDispenser.LockForRead(varName);
pkg.VariableDispenser.GetVariables(ref vars);
try {
result = vars[varName].Value;
}
catch (Exception e) {
throw e;
}
finally {
vars.Unlock();
}
}
catch (Exception e) {
throw e;
}
return result;
}

//-----------------------------------------------------
// Write to a variable in the currently running DTS.
//-----------------------------------------------------
private bool WriteVariable(string varName, object varValue) {
try {
Variables vars = null;
Dts.VariableDispenser.LockForWrite(varName);
Dts.VariableDispenser.GetVariables(ref vars);
try {
vars[varName].Value = varValue;
}
catch (Exception e) {
//System.Windows.Forms.MessageBox.Show(e.Message);
throw e;
}
finally {
vars.Unlock();
}
}
catch (Exception e) {
//System.Windows.Forms.MessageBox.Show(e.Message);
throw e;
}
return true;
}

//--------------------------------------
// Write to a variable in a package.
//--------------------------------------
private bool WriteVariable(string varName, object varValue, Package pkg) {
try {
Variables vars = null;
pkg.VariableDispenser.LockForWrite(varName);
pkg.VariableDispenser.GetVariables(ref vars);
try {
vars[varName].Value = varValue;
}
catch (Exception e) {
//System.Windows.Forms.MessageBox.Show(e.Message);
throw e;
}
finally {
vars.Unlock();
}
}
catch (Exception e) {
//System.Windows.Forms.MessageBox.Show(e.Message);
throw e;
}
return true;
}

//-------------------------------------------------------------------------------------------------------
// Write to variables in a package the value of the like-named variables in the currently running DTS.
//-------------------------------------------------------------------------------------------------------
private bool PkgVarsSet(Package pkg, String varList) {
string[] userVariableArray;
userVariableArray = varList.Split(',');
foreach (string s in userVariableArray) {
WriteVariable(s, ReadVariable(s), pkg);
}
return true;
}

//-------------------------------------------------------------------------------------------------------
// Write to variables in the currently running DTS the value of the like-named variables in a package.
//-------------------------------------------------------------------------------------------------------
private bool PkgVarsGet(Package pkg, String varList) {
string[] userVariableArray;
userVariableArray = varList.Split(',');
foreach (string s in userVariableArray) {
WriteVariable(s, ReadVariable(s, pkg));
}
return true;
}




#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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close