July 8, 2008 at 3:35 pm
Hi all,
I am trying to find out if it's possible to generate an excel sheet using an SSIS Script Task. I have results from 4 tables that need to be included in a sheet and I need to write a custom header for each section. The issue I am having is, there is no COM tab in the script designed references page to add the Excel libraries. I would like to avoid creating the file through T-SQL. Has anyone else experienced this issue?
July 9, 2008 at 6:07 am
I typically like to create template files for exports to Excel.
In the case you are describing, I would create a workbook with 5 sheets in it. Four of the sheets would be simple table worksheets for me to export into. The fifth sheet would have the custom headers and references to the other four worksheets. In SSIS, rather than creating a new sheet each time, I would use a file system task to make a copy of my template and extract my data into it.
Now, if the custom header had information like a date, a customer name, etc. I would have one more sheet with that data exported into it and I would reference that sheet for my variable header information. Excel makes all of this pretty easy to deal with.
July 9, 2008 at 9:08 am
Thank you for your reply. My main issue right now is dealing with adding the Excel references. In my script task I wan to create an Excel object (Dim xlApp As excel.application). Without the Excel library references, the script designer doesn't know what an Excel object is. I found this is a limitation with the BIDS designer. If I open Visual Studio 2005 and go to the references page, I have the COM tab available to add the Excel reference. BIDS only provides the .Net tab.
July 25, 2008 at 10:14 pm
Hello Michael,
I really like your idea of exporting data into an existing [template] Excel spreadsheet. Do you have a simple example, or site reference that you might be able to share or point me to?
Thanks,
Henry
August 11, 2008 at 1:12 pm
Hi,
I have data as input which i fetch from table A and table B but i want to transfer that merge to excel file and that i want to generate dynamically.Want to write at runtime in it using script component or any other way.if anyone have good suggestion for that please help me out...Any help is highly appreciated..thanks
August 12, 2008 at 4:59 am
How about using DTS to spit out all data in excel using a script
August 12, 2008 at 10:43 am
Provided that you only want to write out data you might consider writing your merged information as a Comma Separated File (.csv). Excel will open a .csv file. If the user then alters the Excel worksheet to include formulas etc., it will have to be saved as a normal Excel workbook that is with the .xls file extension.
July 16, 2009 at 5:27 pm
Hi Henry,
I have sample package which can export data to excel sheets. Here the excel files are create dynamically. Please contact at prasant.nanda@gmail.com
November 18, 2009 at 3:25 am
Hi Henry,
Yes dynamic excel file can be created. There are two different approaches for the same.
Approach 1: If column names are known to u then :
1.Create output excel report file with name as “Report_<<current date>>”
2.Create Dynamic excel sheet through execute SQL task in the above created report file.
3.Prepare SQL statement based on user specified date input parameter
4.Read data from SQL DB using a data source
5.Transfer data from SQL server to output report Excel Destination
Approach 2: If column names are unknown to u then :
1.Prepare SQL statement based on user specified date input parameter
2.Read data from SQL DB using a data source
3.Define output file name as “Report_<<current date>>”
4.Create a “Script task” of SSIS which will generate the Xml schema with data.
5.Xml created in step #4 above will be saved as output excel file
December 3, 2009 at 12:10 pm
Hy ,
I am getting error in my ScripT task
I use
Imports Excel = Microsoft.Office.Interop.Excel
When I instance
Dim xlApp As New Excel.Application --- ERROR
( ERROR IN New Excel.Application ( )
December 10, 2010 at 4:44 am
hi dear,
you can try this code:
This will generate a new Excel workbook and a new sheet in it.
As per your Requirement you can easily edit it. (This is example only)
If feel problem let me know..!
.......................................................................................................................................
PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
PRINT ''
GO
SET NOCOUNT ON
DECLARE @Conn int -- ADO Connection object to create XLS
, @hr int -- OLE return value
, @src varchar(255) -- OLE Error Source
, @desc varchar(255) -- OLE Error Description
, @Path varchar(255) -- Drive or UNC path for XLS
, @connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit -- Whether the XLS Worksheet exists
, @WKS_Name varchar(128) -- Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) -- Linked Server name for XLS
, @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation
, @sql varchar(8000) -- INSERT INTO XLS T-SQL
, @Recs int -- Number of records added to XLS
, @Log bit -- Whether to log process detail
SELECT @Recs = 0
, @Log = 1
SET @Path = 'E:\Rahul\'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Path = 'E:\Rahul\RecordsHistory_MobDW.xls'
SET @connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = CONVERT(varchar(10),GETDATE(),112)
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (TableName nvarchar, RowsCount int)'
SET @sql = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (TableName, RowsCount) '
SET @sql = @sql+'SELECT au_id AS SSN'
SET @sql = @sql+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name'
SET @sql = @sql+', phone AS Phone '
SET @sql = @sql+'FROM Rahul.dbo.Dim_Date_Test'
IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
-- Create the Conn object
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @connect
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @hr = sp_OAMethod @Conn, 'Open'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
OR @hr = 0x80042732
BEGIN
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append'
SET @WKS_Created = 0
END
SET @hr = 0 -- ignore these errors
END
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
-- Linked Server allows T-SQL to access the XLS worksheet (Table)
-- This must be performed after the ADO stuff as the XLS must exist
-- and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login'
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @Path
, @provstr = 'Excel 8.0'
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
EXEC (@SQL)
PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login'
EXEC sp_dropserver @ServerName, 'droplogins'
END
GO
SET NOCOUNT OFF
PRINT ''
PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' '
GO
------------------------------------------End----------------------------------------------------
😎 :hehe: 😎
Rahul Vairagi
July 1, 2011 at 9:08 am
Hi Prasant,
Can you please help me out to send the sample package script which dynamically generate excel sheet using SSIS package.
Thanks,
Ahad
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply