?
Solved

Drop Footer in SSIS, possibly use Conditional Split?

Posted on 2016-11-17
10
Medium Priority
?
60 Views
Last Modified: 2016-11-23
Hi Experts,
I want to use a SSIS to import a flat file into a SQL table and if I manually delete the last row (Footer) it runs perfectly. I had built a script which read the whole file then deleted all the records in the file and then wrote all the records back into the file minus the last row and it worked great for a few months but now the file has grown and it usually has 50,000 + records and this approach is not reliable anymore as sometimes I come in to see an error.
I have read, I can use a conditional split to ignore the footer, but I am having issues getting it working.
The data rows always start of with a number (4 digits) and the Footer always says "Trailer 1" (That might help)

I have never used a conditional split before so I am sort of winging it. So that might be part of my problem.
If anyone can give me a good example of how to do what I am trying to do or even let me know if I am barking up the wrong tree, it would be GREATLY appreciated.
0
Comment
Question by:Niall292
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41891683
The problem you're experiencing with mixed column mappings in a single flat file is that SSIS can't handle that mix, it has to be one mapping of a source file, so any oddballs like a footer will throw off your data pump.

The low-brain solution would be to import the file as a single column into a table with an IDENTITY column plus another column called value (or whatever) containing the entire row.  Then in T-SQL just delete the MAX(identity column) row, then parse the set into separate columns.

IF your column mapping has the first column as the first four characters, you could do a conditional split where the path that is not equal to 'Trai' goes to the destination, and the other path (equals) is simply ignored.  BUT that is not possible if that column is a numeric data type.

So .. your call.
0
 

Author Comment

by:Niall292
ID: 41891734
Hi Jim,
Thank you for your response, it is greatly appreciated,
but I now noticed that the footer row is in a whole different format than the data rows.
The file is tab delimited:
In the data tows it holds the 4 characters and then goes to a tab but in the footer row it has  "Trailer 1" in the first column. So although I have a condition saying to continue when  SUBSTRING(COLUMNa,1,4) != "TRAI"
It still errors out with a Truncation error and when I check the errors in says Row 444543 (Which is the footer row). What I now think , is this Column is not COLUMNa so is there a way I can say the first column without naming it.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 2000 total points
ID: 41891749
>So although I have a condition ... It still errors out with a Truncation error and when
I'm guessing the the file fails validation when it sees the oddball row, before it can process that Conditional Split.   So the one-row plus IDENTITY solution might be the only way to go.

Unless you know a really spiff VB.NET / C#.NET / PowerShell ? developer  in your building that knows how to write code to suck in file, delete a single row based on your requirements, then re-save that file.   If so, you can call that in SSIS in the control flow before it hits the data pump.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

Expert Comment

by:Jim Horn
ID: 41891753
<Barney Stinson in 'How I Met Your Mother'>  OR ....

Have two data pumps.  One to suck in the file as all text with the first four characters as a column, then do your conditional split, then save the rest in your destination.   Then the second one does what your existing one does now, but without the footer row to deal with.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41891761
<Barney Stinson in 'How I Met Your Mother'>  OR ....

Ask the source of this data if they will create two more files for you:  One with just the details, and one with just the footer.   Then SSIS can consume the one with just the details, and if you wanted for auditing purposes you can create another data pump to consume the footer file and do whatever check totals you wish.
0
 

Author Comment

by:Niall292
ID: 41891763
"how to write code to suck in file, delete a single row based on your requirements, then re-save that file. " Actually that is not terribly difficult, (I am a c#.net developer) but I like to learn about new tools. I will give that option a try and let you know how I get on.
Thank you for you help
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41891768
Ok.  Do us a favor and if you know how to do that in C# then post the eventual solution here, as I don't know how to pull that off as I'm primarily a T-SQL and straight SSIS guy, and that would be damn handy.  Or better yet, write an article on it and get 3,000 points for starters out of the deal.

Thanks.
Jim
0
 

Accepted Solution

by:
Niall292 earned 0 total points
ID: 41892929
I ended up building a .exe and then calling it from the SSIS

 static void Main(string[] args)
        {
           string sourcePath = @"\\\\Server\Files_Path\Files_Directory\"; // Directory where files are
            var cls = new SearchDirectory(); // class which searches directory. not included in code
            string[] fileNames = cls.file(sourcePath);  // put names of files in array
            RemoveRecord(sourcePath + fileNames[0], sourcePath + "NewFile.txt");
            RemoveRecord(sourcePath + fileNames[1], sourcePath + "AnotherFile.txt");
        } 

        public static void RemoveRecord(string path, string writefile)
        {
            using (StreamReader sr = new StreamReader(path))
            { 
                var stream = File.OpenWrite(writefile);
                var sw = new StreamWriter(stream);
                string line;

                while ((line = sr.ReadLine()) != null) //read each line from file
                {
                    if (line.Substring(0, 4) != "TRAI") // if line does not begin with TRAI then write line to new file
                    {
                        sw.WriteLine(line);
                    }
                }
                sw.Flush();
                sw.Close();
            }
        }

Open in new window

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41892944
Looks like a winner.  Thanks for posting.  Go ahead and accept your commend as the answer, and any of mine if they helped you.
0
 

Author Closing Comment

by:Niall292
ID: 41898882
I never really thought of mixing SSISs and my own executables before and really it would make my job a lot easier I think.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question