This morning I determined that the problem was the size of the Excel Destination.

It kept growing and when I finally realized what the problem was it was over 86MB.

I started...

GSquared,

Thanks for your response.

It is very complicated. Many of the headings are dynamic based on the current date, e,g, 2010 (Last Year), Year To Date (2011 YTD), Month to Date...

Evil Kraig F (11/1/2011)

---

Welsh Corgi (11/1/2011)

---

I need to make the following column headings and several others dynamic:

CONVERT(VARCHAR(5), NULL) AS [2010],

CONVERT(VARCHAR(25), NULL) AS [2011 YTD],

...

Evil Kraig F (10/31/2011)

---

Yeah, it's fine except you have to put in a metadata component first.

SP as a data source runs into trouble when it's multi-statement, particularly if you use...

I was hoping that someone would be able to make a suggestion.

The first 3 columns on most Worksheet Contain a Heading.

The Column Names are on the fourth Row.

Is there...

Evil Kraig F (10/31/2011)

---

Yeah, it's fine except you have to put in a metadata component first.

SP as a data source runs into trouble when it's multi-statement, particularly if you use...

On several blogs it was stated that you have to SET fmtdata OFF. SET NOCOUNT ON, etc.

On another blog someone JOINED sys.objects and sys.indexes WHERE ObjectName = 'SPName'.

I wasn't to...

Thanks again Elliott.

Elliott Whitlow (10/31/2011)

---

I have seen it happen much more when "My Documents" was not on a local drive but instead of on a network or offline folder. The other...

Ninja,

Thanks for your feedback.

It is horrible, I never had this happen before.

I have SP2. I read that it appears to be a bug but I did not get anything definitive.

I have resolved the issue.

The only things that I do not like is that by default it gives the Alias Name Copy of Column Name to every Column.

You also have...

I have found that the Data Conversion Task is need for each column that use an Alias on.

An Alias is needed on all most all of the columns to meet...

Phil,

Thanks for getting back to me again.

I want back to my original small column set but since I last successfully ran it, I had added the CONVEERT function on the...

Koen Verbeeck (10/28/2011)

---

For .xlsx you need to use the ACE OLE DB provider. Normally this is automatically selected when you choose Excel 2007 in the Excel Connection Manager.

Also make sure...

Initially I had it set to an xlsx version.

Then I got an error that I must specify an xls.

I have since changed the file type and connection manager but I...