Solved

Drop Footer in SSIS, possibly use Conditional Split?

Posted on 2016-11-17
10
48 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 65

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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 500 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 65

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 65

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 65

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 65

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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