SQL Server Integration Services
You could add a row number to your data, and use a conditional split to remove the first 6 rows.
To add the row number, you can use a script component or the Row Number transform from http://www.sqlis.com/93.aspx. Then add a conditional split that checks to see if the row number is less than 7 and send those rows to a different output, which you should not connect to any other tasks, so that the rows are discarded.
You may consider creating a named range in your Excel sheet with data you want to import and selecting that table in Excel source.
To be honest I've tryed to replicate your problem. Let me see if i got it straight :
5 empty rows followed by a row with column headers followed by data. That was the excel file.
After i used an excel source i used an data viewer and it showed that the empty rows were skipped by default.
I have a similar situation, but mine differs in that the first 5-6, or so, rows contain non-essential information (report name, legend, etc) which I need to bypass in order to get to the header row. I'm somewhat new to SSIS, so I'm not sure if I'm looking at the right control flow object, or not, but I was looking at the Foreach Loop...and, then trying to determine if one of the Foreach Loop enumerators would allow me to sequentially process a worksheet in an Excel file so that I could interogate several columns to try to determine if the row is my header row, or data rows, or just the non-essential information. Can you tell me if looking in the right direction
Thanks for any help.
Thanks. I tried that yesterday. First, I defined a data-flow with an Excel Source being my Excel file, a Data Conversion to make sure the string columns were defined as string, and then a Recordset Destination. Then in my Control Flow I have my Data Flow task input into a ForEach loop to iterate thru the rows (I inserted a Script Task to load a variable with just the first column of each row so that I could display it with a messagebox). This seems to run ok until I encounter a row with a null value in one of my string columns, at which point I get the error (Error: The type of the value being assigned to variable "User::uDescription" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ) So, this is partially working. In my Data Conversion I've tried defining the string columns as either "string [DT_STR]" and "unicode string [DT_W( can't see the entire data type description)". Is there a recommended way of dealing with nulls in data columns
Thanks again...this is making some sense...there just seems to be many options, or possible ways of transforming the data.