Excel Destination Plus Issue

[NewGuy]

Hi,

I'm using CozyRoc's Excel Destination Plus to create a collection of Excel Spreadsheets with data exported from SQL Server.

I loop through a list of users using a For Each container.

Within the Container I have two tasks, 

• one File System task that copies an Excel Template File and embeds the username into the file name ie "User1_ContactList.xlsx"
  
• The second is a Data Flow task that populates the excel file using an Ole DB Source and the Excel Destination Plus.
  

The Ole DB Source uses a variable for the sql statement so it's something like "SELECT * FROM ContactList WHERE UserID = '" +  @[User::strUserID] + "';"

If I have 10 users, I'll get 10 files.

The first file generates perfectly, let's say it has 20 records.

On the second round through the foreach, if the second user has 10 records, the new file will have 20 records (10 from the first user followed by 10 from the second user).

On the third round through the foreach, if the 3rd user has 2 records, the new file will have 20 records (2 from the third user, followed by 8 from the second user, followed by 10 from the first user).

On the 4th round through the foreach, if the 4th user has 30 records then it works (since the 30 records overwrote all 20 records from the previous users)

I have stepped through the process many times, I am positive that a fresh new template with no data is being copied over every time the ForEach statement runs.

I have also tried using a parameter query in the source instead of a variable statement.

Also, in my Excel Destination Plus component, I have Overwrite checked (instead of Clear) because I want to keep the formatting and other content from my template, when I check Clear instead, this issue does not present itself, the files are created with the correct data in them.

It's as if there is some sort of buffer in the in the Excel Destination Component that is not getting cleared out every time you run through the ForEach container.

Any ideas or suggestions would be _Greatly_ appreciated.

Imran

[Ivan Peev]

Hi Imran,

Right-click on the Excel connection manager and select Properties. Set RetainSameConnection=False. Try again to execute the package. Did it work properly now?

[NewGuy]

Thank-you Ivan,

That worked perfectly.

Imran

[Leigh Anne Duvall]

We were previously on 1.6SR1 and just upgraded to 1.8. We've had nothing but problems since. Prior to the upgrade, DATETIME fields were being correctly converted in the Excel Destination. (Using Excel Destination Plus). Since the upgrade, the DATETIME fields are being displayed as INT or DEC fields. If I open the workbook and right-click, I can change the formatting to DATE but we have clients that have automated routines built on the spreadsheets they were getting before the upgrade. I've been trying to manually CONVERT to VARCHAR and it's still not taking the change in the Excel Destination. How can we make this work? (PS-We have over 400 packages running and don't have time to open each one. We're having to wait for client complaints.)