• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1156
  • Last Modified:

Skip rows in Excel using SSIS

I have an Excel file that I am using as my source to create a CGL file in SSIS. I need to skip the first 5 rows every time and only want data from the following columns. F6,F7,F5,F64,F10,F86,F11,F61,F63,F167,F125,F87,F2 is there an easy way to accomplish this task in SSIS?
weekly-batch.xls
0
mburk1968
Asked:
mburk1968
  • 3
  • 2
2 Solutions
 
ralmadaCommented:
you can use the OpenRowset() property on the Excel Datasource. Something like Sheet1$A6:Z5000, check this link for more details:
http://weblogs.asp.net/mikebosch/archive/2007/10/26/ssis-skipping-rows-and-stripping-subtotals.aspx 
0
 
mburk1968Author Commented:
I get the following error attached. this is what I placed in OpenRowSet: HeaderTemplate$A6:FK100
Error.docx
0
 
ralmadaCommented:
try adding brackets there
[HeaderTemplate$A6:FK100]
0
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.

 
mburk1968Author Commented:
Screen shot of error attached after using []
error.JPG
0
 
Reza RadConsultant, TrainerCommented:
Easiest would be to give each row a number (a bit like an identity in SQL Server) and then use a conditional split to filter out everything where the number <=5

reference:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/97144bb2-9bb9-4cb8-b069-45c29690dfeb


0
 
ralmadaCommented:
Well, I'm not sure why the suggestion I given in the link on my first comment didn't work, but alternatively you can try this link:
http://www.sqlis.com/sqlis/post/Row-Number-Transformation.aspx 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now