mercredi 24 décembre 2014

SSIS import with header record


I have fixed length text files that I have to import into SQL Server daily. These files contain header records (01) and Detail records (02). I am trying to build an SSIS package to do this. I have a script component that splits the data out but I cant get it to work. I am new to SSIS ...please help.



using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void PreExecute()
{
base.PreExecute();
}

public override void PostExecute()
{
base.PostExecute();
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
if (Row.Line.Substring(0, 2) == "01")
{
HeaderBuffer.AddRow();
HeaderBuffer.NAME_EDC = Row.Line.Substring(231, 60);
HeaderBuffer.DUNS_EDC = Row.Line.Substring(291, 13);
}
else if (Row.Line.Substring(0, 2) == "02")
{
DetailBuffer.AddRow();
DetailBuffer.REC_TYPE_DTEAIL = Row.Line.Substring(1, 2);
DetailBuffer.PMT_NUMBER = Row.Line.Substring(3, 6);
DetailBuffer.PMT_ACTION_CD = Row.Line.Substring(9, 2);
DetailBuffer.PMT_AMOUNT = Row.Line.Substring(11, 18);
DetailBuffer.ADJUSTMENT_CD = Row.Line.Substring(29, 2);
DetailBuffer.ADJ_AMOUNT = Row.Line.Substring(31, 18);
DetailBuffer.ACCT_EDC = Row.Line.Substring(49, 50);
DetailBuffer.ACCT_OLD_EDC = Row.Line.Substring(99, 50);
DetailBuffer.NO_ACCT_EGS = Row.Line.Substring(149, 30);
DetailBuffer.PMT_POST_DATE = Row.Line.Substring(179, 8);
DetailBuffer.XREF_NUMBER = Row.Line.Substring(187, 30);
DetailBuffer.UNDISCOUNTED_AMOUNT = Row.Line.Substring(217, 18);
DetailBuffer.DISCOUNT_ADJUSTMENT_AMOUNT = Row.Line.Substring(235, 18);
DetailBuffer.INVOICE_ADVICE_NUMBER = Row.Line.Substring(253, 30);
}
}

public override void CreateNewOutputRows()
{
}

}


The error I get says:



Error at Data Flow Task [Script Component [85]]: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully. Error at Data Flow Task [SSIS.Pipeline]: "Script Component" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation






Aucun commentaire:

Enregistrer un commentaire