Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1082
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mburk1968Author Commented:
Screen shot of error attached after using []
error.JPG
0
 
Reza RadCommented:
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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