I need to create excel file using dynamic query in SSIS

Question

I need to create excel file using dynamic query in SSIS

bhupesh.siwas

Old Hand

Points: 304
More actions
March 9, 2008 at 10:02 pm

#128205

Hi,
Is it posible to generate excel file with data using dynamic query in SSIS Package. Source query will change every time and I need the columns as well in the excel file according to dynamic query.
Please help me in this.
Bhupesh

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You could use a variable as your sql command, but you still need to set the variable. How do you know what you want the query to be?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bhupesh.siwas Old Hand Points: 304 More actions · Answer 2

Hi

I am using 2 variables. In one variable I am giving SQL query and in second variable I am giving the Create command for excel. using second variable I can create dynamic excel file with same column names as coming in the query in first variable. In dynamic query if table name, no. of columns and columns names remain same then the SSIS package is working and I am able to generate the dynamic excel file. Because source and destination columns mapping remain same as initially defined in the package. But if I change the columns in the query then the defined mapping in the package doesn't match with new columns and package give error. Is there any solution for this.

Thanks

Bhupesh

Rudy-388257 SSC Journeyman Points: 78 More actions · Answer 3

Well I figured it out. Here is the code as it stands. This will be developed further to make it more dynamic. I've hard coded the name of the excel file to save. More code can be written, and it will to dynamically create the saved excel filename. Such as adding a date to the name. I will complete this by creating a few dts variables.

@sql

@Filepath

@filename

@EmailBody

@EmailSubject

@attachment which equals @filepath + @filename.

I added a script task into my package. When it is finalized, the sql statement within the script will read my @sql variable, and execute that statement. To reuse this I will pass the variables to this package, and it will take my sql query and generate an excel file with the proper column headers, with some formatting.

Option Explicit Off

Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.VisualBasic

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()

'Variables for the database-connection.

Dim ofso

Dim colIndex As Integer

Const stCon = "uid=myusername;Dsn=myodbcName;pwd=mypassword;"

Dim finalExcelFile As String = "c:\MySavedExcel.xls" ' My saved workbook

stSQL = "SELECT * FROM Mytable"

cnt = CreateObject("ADODB.Connection")

rst = CreateObject("ADODB.Recordset")

cnt.Open(stCon) 'Open the connection.

With rst 'Open the recordset.

.CursorLocation = 3

.Open(stSQL, cnt)

End With

'Variables for Excel and the added workbook.

xlApp = CreateObject("Excel.Application")

xlapp.DisplayAlerts = False

xlWBook = xlapp.Workbooks.Add() ' create a workbook

xlWBook.Worksheets(2).delete() ' delete the 2nd worksheet

xlWBook.Worksheets(2).delete() ' delete the 3nd worksheet

xlWsheet = xlwbook.worksheets(1) ' sets active sheet to the 1st worksheet in workbook

xlWsheet.name = "renamed" ' rename new workbook

xlRange = xlWSheet.Range("A2") ' Starting in the 2nd row add the contents of the recordset

'Populate the range.

xlRange.Offset(0, 0).CopyFromRecordset(rst) ' Copy data from recordset

xlapp.DisplayAlerts = True

For colIndex = 0 To rst.Fields.Count - 1 ' For each column in the dataset loop through and add the column headers in row 1

xlWBook.Worksheets(1).Cells(1, colIndex + 1) = rst.Fields(colIndex).Name

With xlWBook.Worksheets(1).Cells(1, colIndex + 1) ' Format my header row

.Interior.ColorIndex = 36

.Font.Bold = True

.HorizontalAlignment = 3

End With

xlwsheet.Columns("A:Z").autofit() ' Adjust column width to cell contents

'---------------------------------------------------------------------------

'Make Excel available to the user.

With xlApp

.ActiveWorkbook.SaveAs(finalExcelFile, 1)

.ActiveWorkbook.close()

End With

xlwbook = Nothing ' clean up

xlwsheet = Nothing

rst = Nothing

xlapp.Application.Quit()

xlapp = Nothing

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

kishorkumar.j Valued Member Points: 71 More actions · Answer 4

Hi,

In your script,the source table is copied to the excel sheet. I need some solution for the following requirement:

I am looping through several tables and fetches all the tables data to a single excel file in a single worksheet (data in excel file should be populated one by one table with the table name (stored in variable))

tam-928946 SSCommitted Points: 1583 More actions · Answer 5

Do you have any workaround to handle .xlsx files as well? I really like your script and want to do the same for Excel 2007 files but somehow xlApp = CreateObject("Excel.Application") code handling .xls files only.