Solved

Drop Footer in SSIS, possibly use Conditional Split?

Posted on 2016-11-17
10
44 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
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

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…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

756 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