December 20, 2011 at 7:08 am
This is posted here for lack of a better place to put it.
I am populating an Excel sheet with a report that uses different column sizings. There is a header row with a single column, a detail row with 4 columns, and a footer row of a single column. Here's an example:
Report Covers January 1, 2011 through March 3, 2011 <- header row
CName CZip Program SignUpDate <- data row
Boss Hoss 55124 Doodah 01/10/2011 <- data row
Jethro Gibbs 12345 NCIS 02/27/2011 <- data row
Total Customers on the Program: 2 <- Footer row
I can't use a .csv because when I rename it to Excel, it doesn't import the detail data into 4 columns. It imports it into one and the receiver of this data requires properly formatted Excel. I managed to get my header, detail, and footer detail pulled from a proc where I stuck the header & footer in column one and put spaces in the other 3 columns. But now I'm running into an issue where I have an extra header on the spreadsheet.
I am using an Execute T-SQL task to create the Excel Sheet. This task works fine, but it creates Sheet1 with the below names in the first row of the sheet. In other circumstances, I would be happy with this, but not today.
CREATE TABLE `Sheet1` (
CName LongText,
CZip LongText,
Program LongText,
SignUpDate LongText
)
GO
Now my spreadsheet looks like this:
CName CZip Program SignUpDate
Report Covers January 1, 2011 through March 3, 2011 <- header row
CName CZip Program SignUpDate <- data row
Boss Hoss 55124 Doodah 01/10/2011 <- data row
Jethro Gibbs 12345 NCIS 02/27/2011 <- data row
Total Customers on the Program: 2 <- Footer row
I need to get rid of this first row (the faux header created by the T-SQL task) before I send everything to the customer. Any thoughts?
December 22, 2011 at 10:49 am
If you would provide the code you are using for the export to Excel, that would be of great help in answering your question.
Also, have you considered doing this in SSRS instead?
December 22, 2011 at 11:01 am
jerry-621596 (12/22/2011)
If you would provide the code you are using for the export to Excel, that would be of great help in answering your question.
There's not a whole lot to it. Data flow task. OLEDB Source. Excel Destination. Derived Column Transformation to turn everything to Unicode.
SELECT <headerinfo> AS Column1, SPACE(20) AS Column2, SPACE(50) AS Column3, SPACE(10) AS COLUMN4
UNION ALL
SELECT 'Column1' AS Column1, 'Column2' AS Column2, 'Column3' AS Column3, 'Column4' AS COLUMN4
UNION ALL
SELECT Column1, Column2, Column3, Column4
FROM dbo.MyTable
UNION ALL
SELECT <FooterInfo> AS Column1, SPACE(20) AS Column2, SPACE(50) AS Column3, SPACE(10) AS COLUMN4;
It's really that simple. Except for the formatting issue.
Also, have you considered doing this in SSRS instead?
Actually, I didn't think about that because our company is moving off of SSRS and into Crystal / Business Objects.
EDIT: Sorry. I forgot about the header stuff.
December 22, 2011 at 12:43 pm
I need to get rid of this first row (the faux header created by the T-SQL task) before I send everything to the customer. Any thoughts?
I've been playing around with Excel spreadsheets and SSIS and found some old DTS techniques still work. After the Data Flow task, add an ActiveX script task:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oBook
Dim oSheet
Dim oXLS
Dim oFile
oFile = "\\MyPath\MyFile.xls"
Set oXLS = CreateObject("Excel.Application")
With oXLS
Set oBook = .Workbooks.Open(oFile)
End With
Set oSheet = oBook.Worksheets("Wage Data")
With oSheet
.Range("A1","J1").Font.Bold = True
.Columns("E").ColumnWidth = 25
.Range("A1","D1").ColumnWidth = 8.50
.Range("B1").Value = "New Header"
.Rows(2).Delete
End With
oSheet.Activate
With oBook
.save
End With
oXLS.Quit
Set oXLS = Nothing
Set oBook = Nothing
Set oSheet = Nothing
Set oFile = Nothing
End Function
This script is an example of formatting I've been able to do successfully. You're looking for the Rows(2).Delete.
December 27, 2011 at 5:49 am
It's a good suggestion, but how do I get the dynamic file name (which has a date appended to it) and path (which changes depending on the environment I'm running the package in) into the ActiveX script?
I don't see an option for parameters like in the regular script task.
December 27, 2011 at 7:37 am
HA! Figured it out. Rather than use the ActiveX Task, which is deprecated and may be removed from SSIS without further notice in the future, I've adapted your code into a regular VB.Net Script Task. It really didn't take much.
In the Script Task "read" variables, I put the variable with the dynamic path name in it. Then I changed the oFile part of the script. Here's the code:
Public Sub Main()
Dim oBook
Dim oSheet
Dim oXLS
Dim oFile
oFile = CStr(Dts.Variables("MyFileName").Value)
oXLS = CreateObject("Excel.Application")
With oXLS
oBook = .Workbooks.Open(oFile)
End With
oSheet = oBook.Worksheets("Motor")
With oSheet
.Range("A1", "D1").Font.Bold = True
.Columns("E").ColumnWidth = 25
.Range("A1", "D1").ColumnWidth = 8.5
.Range("B1").Value = "New Header"
.Rows(1).Delete()
End With
oSheet.Activate()
With oBook
.save()
End With
oXLS.Quit()
oXLS = Nothing
oBook = Nothing
oSheet = Nothing
oFile = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
Thank you for the assist. This worked perfectly!
December 29, 2011 at 8:20 am
I've adapted your code into a regular VB.Net Script Task.
Nice. I think I'll do the same!
I looked there in the begining. But not knowing anything about .NET, I figured I'd have to add something to the Imports in order to work with Excel and couldn't find any examples. So that's when I went to the ActiveX script.
I think you've helped me more than I did you.
December 29, 2011 at 8:24 am
Randy Doub (12/29/2011)
I've adapted your code into a regular VB.Net Script Task.
Nice. I think I'll do the same!
I looked there in the begining. But not knowing anything about .NET, I figured I'd have to add something to the Imports in order to work with Excel and couldn't find any examples. So that's when I went to the ActiveX script.
I think you've helped me more than I did you.
You're welcome.
BTW, the Script task will complain about the variable declarations because there's no "AS <datatype>" on them. But it works just fine without the type declarations.
August 2, 2013 at 7:31 am
Hi,
Can you help me with the C# version of the same code snippet?
August 2, 2013 at 9:40 am
kogila.r (8/2/2013)
Hi,Can you help me with the C# version of the same code snippet?
Unfortunately I don't know C# very well.
After I'm done with my various high priority fires at work, I'll take a gander at it as an excuse to teach myself C#. But I can't guarantee an ETA on the task.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply