June 11, 2011 at 11:24 pm
Hello
I Need To Write a script witch create *.xlsx file in a given path.
i didnt succedded in doing so.
Doen Any one has a script ?
Thanks.
June 12, 2011 at 1:24 pm
why do you want to write script . It can be done easily using FileSystemtask.
June 12, 2011 at 1:28 pm
praveensc2003 (6/12/2011)
why do you want to write script . It can be done easily using FileSystemtask.
I'd like to see that. Please provide details of how the FST can create Excel files.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 12, 2011 at 6:25 pm
Phil Parkin (6/12/2011)
praveensc2003 (6/12/2011)
why do you want to write script . It can be done easily using FileSystemtask.I'd like to see that. Please provide details of how the FST can create Excel files.
I would like to see this as well.
What are you planning on using as a Datasource to create this data file?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 12, 2011 at 9:19 pm
Hi
since when File sysem task can create an excel file.
I dont think it can be done.
It is possible by script to delete an excel file but i want
a script that create an Excel File.
Thanks.
June 12, 2011 at 9:27 pm
amir.krugliak (6/12/2011)
Hisince when File sysem task can create an excel file.
I dont think it can be done.
It is possible by script to delete an excel file but i want
a script that create an Excel File.
Thanks.
You can use the file system object to delete a file.
If you want to create an Excel file in a script task you would use the Excel Object Model.
Can you provide additional details?
Depending upon what you are doing there is probably an easier solution to your requirement.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 12, 2011 at 9:33 pm
Hi
Thanks for the quick reply.
this is the script i am using.
the problem is that itws creates the file in the destination path , but the file created
cannot opend because it is not realy an excel , but a text file with xlsx as an extension.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.Data.SqlClient
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim Excel_Path1 As String = "\\tblbi02\d$\SSIS Projects\Prod\PUNARVIW_MTB_Chvot_Mesumnim\Chovot_Mesumanim_Export.xlsx"
Dim Excel_Path2 As String = "\\tblbi02\d$\SSIS Projects\Prod\PUNARVIW_MTB_Chvot_Mesumnim\Chovot_Mesumanim_Export2.xlsx"
'Dim oWrite As System.IO.StreamWriter
Dim oWrite As System.IO.FileStream
oWrite = IO.File.Create(Excel_Path1)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
June 12, 2011 at 9:48 pm
Perhaps I missed it but what is your source object?
Please define. I assume that your Source is a SQL Server query or table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 12, 2011 at 10:04 pm
the source object is an SQL SERVER Table which i need to transform to an Excel.
June 12, 2011 at 10:15 pm
You can use the SQL Server Import and Export Wizard or if you prefer you can do it manually by defining a Source Connection (Database) and a Destination Connection (File).
To use the SQL Server Import and Export Wizard:
http://msdn.microsoft.com/en-us/library/ms140052.aspx
This approach is a lot easier than using the Excel Object Model.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 14, 2011 at 6:33 am
We have had to do this in the past, and what we do is:
1) Build a .xlsx template and store it in a "Templates" Folder on our destination server. We include all the column headers that we need, etc.
2) Before extracting the data from SQL Server, we copy the template file to the final destination and rename it to what we want the file to be named.
3) Using expressions (or other methods) set the desintation connection on to point to the newly copied Excel file
4) Run the data flow
I know we had some issues with Excel 2007 to where we had to use the OLEDB driver to connect to the files, so the standard Excel destination in SSIS 2005 does not work for Excel 2007 files. There is at least 1 article here on SSC about connecting to Excel 2007 in this way: http://qa.sqlservercentral.com/articles/Integration+Services+(SSIS)/64585/
If your data is dynamic and the number of columns or the names can change, then this method won't work. But for standard extractions where the number of columns, datatypes and names are static this works well.
June 14, 2011 at 9:33 pm
it seems like a good idea .
i Will try using it.
Thanks
August 4, 2011 at 3:14 pm
You can create an excel xlsx file using the SSIS OLE DB using extended properties excel 12.0 but the resulting file is un readable by Excel 2007.
August 5, 2011 at 3:36 am
I have a task that emails reports in xslx format. I achieve this by having a dummy template xslx which gets passed through a series of tasks:
1. Data Flow
i. source table - which is a set of paramaters including query string, file name, destination etc.
ii. convert to unicode
iii. load into spreadsheet
2. Script to set password
3. Emailed to users
Best of luck
edit: should have read 2nd page of thread - my method is almost identical to Eric Heilman, and he explains it far better than I have.
August 5, 2011 at 6:39 am
Right. I use the "template" file approach when creating an .xlsx file buy you would think if using the same connection string for reading .xlsx files one could create and write to one. I think I'll play around with this in 2008 SSIS.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply