[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Drop Footer in SSIS, possibly use Conditional Split?

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
Niall292
Asked:
Niall292
  • 6
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Niall292Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
Niall292Author Commented:
"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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Niall292Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Niall292Author Commented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now