April 6, 2006 at 5:42 am
I have several flat files (source) that are used for a daily upload to sql server. The first row of these files contains header information that is used to update a "status" table. The subsequent rows are the actual data.
I have a "status load" that needs to load the FIRST ROW only to a status table. In SQL 2000, in the "transform data task", I could select "FIRST ROW = 1" and "LAST ROW = 1". This worked fine. Now in SSIS, this option doesn't exist when you are using the "DATA FLOW TASK". In the FLAT FILE CONNECTION MANAGER, I've tried using the ADVANCED-SUGGEST TYPES-NUMBER OF ROWS and set this to 1, but it doesn't seem to work.
Within the "DATA FLOW TASK", I'm running a script to transform some dates. I would image that I could add to the script and issue a return if ROW <> 0, but that would entail that I would be hitting this script and returning millions of times, until the entire file is read through. For time/performance, I don't want to do this. I just want to load the first row, transform it and exit the package.
Any suggestions?
Steve
April 6, 2006 at 6:57 am
How about running a SQL query, using OPENDATASOURCE or OPENROWSET to access the flat file, and use SELECT TOP 1 ... for the query?
Mark
April 8, 2006 at 10:52 am
All in here: "SSIS Nugget: Select Top N in a data-flow"
http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
April 15, 2011 at 11:56 am
It's been a while since I've spent much time with SSIS. I've just received a project with a similar requirement: the first row of a pipe-delimited text file contains header data that needs to be parsed out into a header data staging table and has a different layout than the rest of the file. I can figure out how to import the rest of the file, but I'm having a hard time getting the first record into the header table without importing everything into one giant single-column table and then deleting back out anything that isn't flagged as a header record.
Mark, I've tried your suggestion (never used either command before so it was a chance to dig through BOL), but both commands are disabled for security reasons.
Jamie, you mention in your article that your solution requires a third party adapter to be installed. Is there any way around this? Our servers are locked down extremely tightly and I can guarantee that I won't be able to install the adapter without jumping through months' worth of hoops.
I CAN go my original route, but I don't like the idea of importing 2000 records and deleting 1999 of them right back out very much.
Jennifer Levy (@iffermonster)
April 15, 2011 at 12:08 pm
There's another option. Set up a single column flatfile import. Then put in a script component as a transformation with *2* outputs.
This article will walk you through the necessary code you'll need to take the stream and push it around to the other streams:
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/
These will assist with the inbound rows:
http://msdn.microsoft.com/en-us/library/ms135939.aspx
This blog discusses a similar concept:
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm
No, it will not be simple, but it can be done.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 15, 2011 at 12:59 pm
Thanks, Craig! I'll take a look and hopefully I'll be able to get things working.
Jennifer Levy (@iffermonster)
April 18, 2011 at 12:06 am
Wouldn't the "easiest" solution be to just write a script task in .NET and read out that one single line?
Or am I missing something here?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 8:09 am
Another easy way to pull just header data is to do a conditional split off of the flat file source based on some sort of identifier in the header row (most headers have some unique value(s) that identify it as a header record, just as there usually is on the trailer (if any) and the actual data rows). Then discard the rest of the file in the bit bucket, and shred out whatever you need from the header.
April 19, 2011 at 9:50 am
Thanks all! Got the coding finished yesterday, waiting on one more bit of info from the business before I can test it. Here's hoping...
Jennifer Levy (@iffermonster)
April 19, 2011 at 11:16 am
dg227 (4/19/2011)
Another easy way to pull just header data is to do a conditional split off of the flat file source based on some sort of identifier in the header row (most headers have some unique value(s) that identify it as a header record, just as there usually is on the trailer (if any) and the actual data rows). Then discard the rest of the file in the bit bucket, and shred out whatever you need from the header.
This is a great solution for small files, but what happens when you have a flat file with a file size over a gigabyte? You'd have to read it all in, just to throw everything away except one row.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 12:26 pm
Though the approximate file size(s) wasn't noted, you're correct in that it'd be a waste for very large files; this is just a quick and dirty way to just grab what you need without scripting or anything more involved. Obviously every situation/file is different and should be handled in the most efficient way possible.
April 19, 2011 at 12:31 pm
dg227 (4/19/2011)
Though the approximate file size(s) wasn't noted
About 2000 records (about 8 posts ago). Not really large, unless there are 500 columns 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply