July 19, 2018 at 4:06 pm
Please help me asap. SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile with same datatype. Here is my situation:
i have source flatfile with 20 columns which i need to load on daily basis to sql staging table. Suddenly they removed one of the field and added new one. Still my package is taking all the data without catching the error even though there are different input column compare with target table column names. I would like to catch the issue and send email before dataflow. Please advise.
July 20, 2018 at 5:22 am
saptek9 - Thursday, July 19, 2018 4:06 PMPlease help me asap. SSIS Dataflow is not throwing any error when there is invalid column in the source flatfile with same datatype. Here is my situation:
i have source flatfile with 20 columns which i need to load on daily basis to sql staging table. Suddenly they removed one of the field and added new one. Still my package is taking all the data without catching the error even though there are different input column compare with target table column names. I would like to catch the issue and send email before dataflow. Please advise.
So you want to compare the column names in the file against those which you expect and throw an error in the event of a mismatch?
I do that using a script task. I can provide some guidance, but first, are you comfortable writing C# code?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 20, 2018 at 5:25 am
i know c# in moderate level not an expert. Please provide me help
July 20, 2018 at 5:47 am
saptek9 - Friday, July 20, 2018 5:25 AMi know c# in moderate level not an expert. Please provide me help
OK, here goes.
First of all, here is my outer method: public string[] FileColumnNames;
private void ValidateTextFile()
{
//Read the header row in the text file
string FileLine1 = File.ReadLines(SourceFilePath).First();
FileColumnNames = FileLine1.Split(FileColumnDelimiter);
CompareColumns();
}
and here is CompareColumns. It assumes that MetaDataColumns is an array containing the expected column names.
private void CompareColumns()
{
bool columnCountError = false;
//Do the actual and expected numbers of columns match?
if (FileColumnNames.Length != MetaDataColumnNames.Length)
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column count mismatch. File {0}/{3} contains {1} column(s). The metadata contains {2} columns.", Filename, FileColumnNames.Length, MetaDataColumnNames.Length, TableOrSheetName), 'E');
columnCountError = true;
}
if (columnCountError)
{ return; }
for (int i = 0; i < FileColumnNames.Length; i++)
{
if (!string.Equals(FileColumnNames, MetaDataColumnNames, StringComparison.OrdinalIgnoreCase))
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column name mismatch. {0} in file {1}/{3} does not match {2} in file metadata", FileColumnNames, Filename, MetaDataColumnNames, TableOrSheetName), 'E');
}
}
}
LogJobHistoryPackageInformation is a method which logs errors. You'd need to do your own error handling here.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2018 at 9:31 am
Phil Parkin - Friday, July 20, 2018 5:47 AMsaptek9 - Friday, July 20, 2018 5:25 AMi know c# in moderate level not an expert. Please provide me helpOK, here goes.
First of all, here is my outer method:public string[] FileColumnNames;
private void ValidateTextFile()
{
//Read the header row in the text file
string FileLine1 = File.ReadLines(SourceFilePath).First();
FileColumnNames = FileLine1.Split(FileColumnDelimiter);
CompareColumns();
}and here is CompareColumns. It assumes that MetaDataColumns is an array containing the expected column names.
private void CompareColumns()
{
bool columnCountError = false;
//Do the actual and expected numbers of columns match?
if (FileColumnNames.Length != MetaDataColumnNames.Length)
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column count mismatch. File {0}/{3} contains {1} column(s). The metadata contains {2} columns.", Filename, FileColumnNames.Length, MetaDataColumnNames.Length, TableOrSheetName), 'E');
columnCountError = true;
}if (columnCountError)
{ return; }for (int i = 0; i < FileColumnNames.Length; i++)
{
if (!string.Equals(FileColumnNames, MetaDataColumnNames, StringComparison.OrdinalIgnoreCase))
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column name mismatch. {0} in file {1}/{3} does not match {2} in file metadata", FileColumnNames, Filename, MetaDataColumnNames, TableOrSheetName), 'E');
}
}
}LogJobHistoryPackageInformation is a method which logs errors. You'd need to do your own error handling here.
Thank you!! i have getting FIleColumnDelimeter does not exist in current context. please advise.
July 23, 2018 at 10:35 am
saptek9 - Monday, July 23, 2018 9:31 AMPhil Parkin - Friday, July 20, 2018 5:47 AMsaptek9 - Friday, July 20, 2018 5:25 AMi know c# in moderate level not an expert. Please provide me helpOK, here goes.
First of all, here is my outer method:public string[] FileColumnNames;
private void ValidateTextFile()
{
//Read the header row in the text file
string FileLine1 = File.ReadLines(SourceFilePath).First();
FileColumnNames = FileLine1.Split(FileColumnDelimiter);
CompareColumns();
}and here is CompareColumns. It assumes that MetaDataColumns is an array containing the expected column names.
private void CompareColumns()
{
bool columnCountError = false;
//Do the actual and expected numbers of columns match?
if (FileColumnNames.Length != MetaDataColumnNames.Length)
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column count mismatch. File {0}/{3} contains {1} column(s). The metadata contains {2} columns.", Filename, FileColumnNames.Length, MetaDataColumnNames.Length, TableOrSheetName), 'E');
columnCountError = true;
}if (columnCountError)
{ return; }for (int i = 0; i < FileColumnNames.Length; i++)
{
if (!string.Equals(FileColumnNames, MetaDataColumnNames, StringComparison.OrdinalIgnoreCase))
{
LogJobHistoryPackageInformation("File Validation Error", string.Format("Column name mismatch. {0} in file {1}/{3} does not match {2} in file metadata", FileColumnNames, Filename, MetaDataColumnNames, TableOrSheetName), 'E');
}
}
}LogJobHistoryPackageInformation is a method which logs errors. You'd need to do your own error handling here.
Thank you!! i have getting FIleColumnDelimeter does not exist in current context. please advise.
Please help me asap. I am still struggling to get this done.
July 23, 2018 at 10:35 am
So ... declare it, or use literal text if it's always the same!
I did not give you my code it its entirety, you'll have to fill in a few blanks.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 24, 2018 at 10:35 am
Phil Parkin - Monday, July 23, 2018 10:35 AMSo ... declare it, or use literal text if it's always the same!
I did not give you my code it its entirety, you'll have to fill in a few blanks.
i have columns named userid, fname, mname, company, lname, add1,add2, add3,city,state,zipcode columns were there. now add3 column got removed from the source. But still ssis did not recognize the error and keep on populating the data without throwing the error as i defined target table datatype nvarchar(4000) for all columns.
July 24, 2018 at 11:41 am
saptek9 - Tuesday, July 24, 2018 10:35 AMPhil Parkin - Monday, July 23, 2018 10:35 AMSo ... declare it, or use literal text if it's always the same!
I did not give you my code it its entirety, you'll have to fill in a few blanks.i have columns named userid, fname, mname, company, lname, add1,add2, add3,city,state,zipcode columns were there. now add3 column got removed from the source. But still ssis did not recognize the error and keep on populating the data without throwing the error as i defined target table datatype nvarchar(4000) for all columns.
My code finds errors like that.
The lineif (FileColumnNames.Length != MetaDataColumnNames.Length)
would detect the fact that the source file had fewer columns than expected.
I think, perhaps, that you might need to get a consultant involved.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 25, 2018 at 2:50 pm
Phil,
This is cool code. I don't really import flat files like I used to but I was curious as to how you would make the fields maintainable (maybe in a SQL Lookup table) as opposed to an array in C#.
Not expecting an answer I just thought this was interesting...
July 25, 2018 at 3:01 pm
Chris Hurlbut - Wednesday, July 25, 2018 2:50 PMPhil,
This is cool code. I don't really import flat files like I used to but I was curious as to how you would make the fields maintainable (maybe in a SQL Lookup table) as opposed to an array in C#.
Not expecting an answer I just thought this was interesting...
Hi Chris. That is how I do it. All of the metadata is stored in tables and read in from C# when required. I hate hard coding stuff when it's not difficult to avoid it.
And thank you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 1, 2018 at 11:39 pm
Im still stuck on the point that SSIS did not error out when the data in the source file did not match the expected meta data. IS there a configuration setting that was flipped to ignore errors/warnings and/or dont validate??
----------------------------------------------------
August 2, 2018 at 12:01 pm
MMartin1 - Wednesday, August 1, 2018 11:39 PMIm still stuck on the point that SSIS did not error out when the data in the source file did not match the expected meta data. IS there a configuration setting that was flipped to ignore errors/warnings and/or dont validate??
Once you create the connection manager - it utilizes the metadata in SSIS for the column names and ignores the actual column names in the file. Essentially - the connection manager will ignore row 1 when it reads the file and will map the data in the file to the column names stored in the connection manager. The advantage to this approach is that you can change the column names in the connection manager to something more appropriate (if needed) - the disadvantage is that a column name change will not be recognized.
If the column name changes - but the type of data and length do not change (or the length you defined is large enough for the new data) - SSIS will read that data into the defined column with no errors.
I have - on occasion - changed the column names in the connection manager because the name in the file is not workable due to brackets or other invalid characters. I prefer not to change the column names and instead modify the column names in the destination table - but again, sometimes it is necessary.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply