SSIS pakage throw an errror "**[SSIS.Pipeline] Error: Script Component failed the post-execute phase and returned error code 0x80004002.
**" In Script component its shows task run successfully but Control flow task shows Error.
Here is my Script task Code:
public override void CreateNewOutputRows()
{
#region Getting Variable values
// Getting DBPackage variable value.
Object varDBPackage;
IDTSVariables100 vars = null;
VariableDispenser.LockForRead("User::DBPackage");
VariableDispenser.GetVariables(out vars);
// Getting ReportPath variable value.
Object varReportPath;
IDTSVariables100 ReportPath = null;
VariableDispenser.LockForRead("User::ReportPath");
VariableDispenser.GetVariables(out ReportPath);
// Getting GetAllProvidersOutput variable value.
Object varGetAllProvidersOutput;
IDTSVariables100 GetAllProvidersOutput = null;
VariableDispenser.LockForRead("User::GetAllProvidersOutput");
VariableDispenser.GetVariables(out GetAllProvidersOutput);
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
try
{
// Assign values into variable
varDBPackage = (Object)vars["User::DBPackage"].Value;
varReportPath = ReportPath["User::ReportPath"].Value;
varGetAllProvidersOutput = GetAllProvidersOutput["User::GetAllProvidersOutput"].Value;
// Extract the data from the object variable into the table and fetching Description from XML.
da.Fill(dt, varDBPackage);
}
catch (Exception ex)
{
varDBPackage = null;
varReportPath = null;
varGetAllProvidersOutput = null;
}
finally
{
vars.Unlock();
ReportPath.Unlock();
GetAllProvidersOutput.Unlock();
}
#endregion
String GetAllProvidersOutputPath = String.Format(@"{0}\{1}", varReportPath.ToString(), Convert.ToString(varGetAllProvidersOutput));
var xDoc = XDocument.Load(GetAllProvidersOutputPath);
var prefix = xDoc.Root.GetNamespaceOfPrefix("d1p1");
foreach (DataRow item in dt.Rows)
{
string code = item["CODPROVIDERCODE"].ToString().ToUpper();
string submissionEndDate = item["MAXSUBMISSIONENDDATE"].ToString();
var node = xDoc.Root.Element(prefix + "AARetrieve")
.Elements(prefix + "Organization")
.FirstOrDefault(x => x.Attribute(prefix + "Code").Value.ToUpper() == code);
string description = string.Empty;
if (node != null)
description = node.Elements(prefix + "OrganizationData").FirstOrDefault().Elements().Select(x => x.Attribute("Description").Value).FirstOrDefault();
Output0Buffer.AddRow();
Output0Buffer.CODPROVIDERCODE = (item["CODPROVIDERCODE"].ToString());
Output0Buffer.MAXSUBMISSIONENDDATE = Convert.ToDateTime(item["MAXSUBMISSIONENDDATE"]);
Output0Buffer.PROVIDERDESCRIPTION = description;
}
}
Error:
[Script Component [23]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{D1A469BC-F371-4D15-832F-BACB50C162E9}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)). at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper100 wrapper)
I am quite new in SSIS so kindly guide me what causes the Issue.
Thanks
User contributions licensed under CC BY-SA 3.0