Solved

Drop Footer in SSIS, possibly use Conditional Split?

Posted on 2016-11-17
10
15 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now