Koen,

I could not use the Excel Connection Manager.

I'm using Excel 2007...

I started a new post because the way I worded it was not the best for a reply.

http://www.sqlservercentral.com/Forums/Topic1202548-148-1.aspx

My connections string has been working fine as Excel 12.0 as soon I changed it it corrupted my package.:w00t:

I don't know of a way to set the connection string via...

An Excel File with an .xlsm is an Excel File that is Macro Enabled.

I'm not used to it either, until now. 🙂

kramaswamy (11/8/2011)

---

Don't set the connection string property. Set the ExcelFilePath property. I find that it causes fewer headaches than trying to build a string for the connectionstring, since unlike the...

I hope that this helps but the Connection String Property of the Destination Connection Manager is set to:

DataSource=C:\SSIS\TXQuoteActivity\TXQuoteActivity.xlsm;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;

I believe that it may have something to do with the...

drew.allen (11/8/2011)

---

If SSRS is a possibility,...

Thanks Michael.

Thanks Drew.

I probably won't be able to use this for the SSIS Excel Worksheet as Dynamic Headers followed by the Output from a CTE.

I have a Data Conversion Task before...

What I was trying to do will not work in SSIS, Tested it, please disregard.

It will not work in SSIS, Tested it, disregard.

Since I resolved the original problem I have moved my new question to a new post:

http://www.sqlservercentral.com/Forums/Topic1202082-338-1.aspx

It looks like I have it. Need to do Current Week & Weeks Prior.

It could be better.

SELECTCAST(YEAR(DATEADD(yy, -1, GETDATE())) AS VARCHAR(4)) AS 'PrevYear',

...

I have several sheets that have Report Headings and the Column Headings are on Row 7.

I currently have mappings to Worksheets that do not have the Report Heading but have...

Thanks Ron.

I was doing it a little differently.

I do not see a property on my Excel Connection where I can set the Header to False.