I'm outputting query results to an Excel sheet. Simple stuff, right?
But for some reason, some of the data is going to the wrong columns on the excel sheet.
I have redrawn the lines on the Mappings panel of the excel Destination editor but it keeps happening.
Any ideas? Thanks.
When you say "wrong columns" do you mean the column is in the wrong position (but it has the correct data for that heading), or that the data is wrong for the column/heading it appears in?
@robert_volk In 7 of the 9 columns, the column contains data that should be in another column. The column headings are arranged correctly. The package doesn't give any warning, errors when in Design mode.
Can you provide examples of what you mean? It appears you are using SSIS - to further troubleshoot we would need to see how the source is configured and the query it is using as well as the destination and how it is configured.
One thing that can cause issues for Excel are embedded control characters. For example, if the data has embedded carriage control, line feed, tabs, double-quotes the data can be shifted. However, that normally shifts things to the right - not to the left.
@jeffw8713 , this is the messy output:
The query:
DECLARE @currDate Date = GETDATE();
DECLARE @1DaysAgo Date;
Set @1DaysAgo = DateAdd(Day, -1, @currDate)
SELECT FreightBills.ShipmentId, CAST(Users.FirstName as nvarchar(50)) as [FirstName], CAST(Users.LastName as nvarchar(50)) as [LastName], FreightBills.CreateDT, FreightBills.BilledAmount, FreightBills.CustReportDT, CAST(Shipments.BOLNumber as nvarchar(50)) as [BOLNumber] , CAST(Shipments.ProNumber as nvarchar(50)) as [ProNumber], CAST(Orgs.Name as nvarchar(50)) as [Name]
FROM (((FreightBills INNER JOIN Users ON FreightBills.LastEditByUserId = Users.Id) INNER JOIN FreightBills AS FreightBills_1 ON FreightBills.ShipmentId = FreightBills_1.ShipmentId) INNER JOIN Shipments ON FreightBills.ShipmentId = Shipments.Id) INNER JOIN Orgs ON Shipments.ParentOrgId = Orgs.Id
WHERE (((FreightBills.AuditCompletionDT) Is Null) AND ((FreightBills.CreateDT)>@1DaysAgo) AND ((FreightBills.BilledAmount)>0) AND ((Shipments.ProNumber)<>' ') AND ((FreightBills.Status)<>6) AND ((FreightBills_1.StatusType)<>2))
ORDER BY FreightBills.CreateDT;
Of course, there are column headings, but not in the first row. Is that the cause? The report was OK until I added the report Title and date.
Thanks
Not sure what you mean with:
If you are using a template for Excel as the destination - and changed that template, you have to actually update that component in SSIS and remap the columns. With that said, I don't generally use SSIS to create Excel files - I find it much easier to create a report in SSRS and schedule the report to output the results as Excel.
In SSRS it is much easier to design an Excel report - whereas SSIS is much better at creating 'data' type files with a single row for the column names and single rows for the data.
All is good now, @jeffw8713. Yes I am using a template and the template file was the problem. I recreated it and now the formatting is correct.
Thanks
