Hi All, I have a ActiveX script in DTS that I am trying to conver to SSIS so that it can have same functionality. The script executes a SQL stored proc which produces 3 resultsets from 3 queries. These results sets are different from each other so number of fields differs and also data types. These 3 result sets then are exported to 1 excel spreadsheet on the same tab in 3 separate sections. So the excel spreadsheet needs to have the result from each query in its own section. Its one result set after another they are underneath each other. So can i do this with SSIS script task or any other SSIS tool? The ActiveX script is below any help is GREATLY appreciated: ********************************************************************** Visual Basic ActiveX Script ************************************************************************ Function Main() Main = DTSTaskExecResult_Success ********************************************************************** Visual Basic ActiveX Script *********************************************************************** *****Declare variables**************** Dim cn,cmd,rs Dim objExcel, strPathExcel,strCN,counter, numOfColumns, strTime Dim numDay, numMonth, numYear, strDay, strMonth, strYear *******Initialize variables****************** ************************************************************ strPathExcel2 = "\\ds271023240\data\" counter = 1 Set cn =CreateObject("ADODB.Connection") Set cmd = CreateObject("ADODB.Command") Set rs=CreateObject("ADODB.Recordset") cn.Provider= "sqloledb" cn.Properties("Data Source").Value = "server" cn.Properties("Initial Catalog").Value = "database" cn.Properties("Integrated Security").Value = "SSPI" cn.Properties("Connect Timeout").Value =0 cn.Open cmd.ActiveConnection = cn Set objExcel = CreateObject("Excel.Application") objexcel.displayalerts=false strPathExcel = "C:\Excelsheet.xls" objExcel.Workbooks.open strPathExcel *******PhotoPackages****************** counter=1 cmd.CommandText = "StoredProc1" cmd.CommandType = 4 Set rs = cmd.Execute objExcel.Workbooks.open strPathExcel Set objSheet = objExcel.activeWorkbook.Worksheets("All") numOfColumns = rs.fields.count Do while not rs.EOF counter=counter+1 For i = 1 To numOfColumns objSheet.Cells(counter, i).Value=rs(i-1) Next rs.moveNext loop counter=counter+3 Set rs = rs.nextRecordset numOfColumns = rs.fields.count Do while not rs.EOF For i = 1 To numOfColumns objSheet.Cells(counter, i).Value=rs(i-1) Next rs.moveNext loop counter=counter+3 Set rs = rs.nextRecordset numOfColumns = rs.fields.count Do while not rs.EOF For i = 1 To numOfColumns objSheet.Cells(counter, i).Value=rs(i-1) Next rs.moveNext loop objExcel.activeWorkbook.Worksheets("All").Activate objSheet.Range("A1:B1").Select cmd.CommandText = "StoredProc2" cmd.CommandType = 4 Set rs = cmd.Execute Set objSheet = objExcel.activeWorkbook.Worksheets("ForEachFran") counter=1 numOfColumns = rs.fields.count Do while not rs.EOF counter=counter+1 For i = 1 To numOfColumns objSheet.Cells(counter, i).Value=rs(i-1) Next rs.moveNext loop strTime=Now numDay = Day(strTime) numMonth = Month(strTime) numYear = Year(strTime) If numDay < 10 Then strDay = "0" & numDay Else strDay = numDay End If If numMonth < 10 Then strMonth = "0" & numMonth Else strMonth = numMonth End If strYear = Right(numYear, 2) objExcel.activeWorkbook.Worksheets("ForEachFran").Activate objSheet.Range("A1:B1").Select objExcel.activeWorkbook.Worksheets("All").Activate objExcel.ActiveWorkbook.saveas strPathExcel2 & "Excelsheet_" & strMonth & strDay & strYear & ".xls" DTSGlobalVariables("gvFileName")= strPathExcel2 & "Excelsheet_" & strMonth & strDay & strYear & ".xls" ***Close all objects************ rs.close set rs=nothing set cmd=nothing set cn=nothing objExcel.activeWorkbook.Worksheets("All").Activate objExcel.ActiveWorkbook.close objexcel.quit set objexcel =nothing End Function

You would need to load the Excel component onto the server, whcih most server admins don't like. Also, I have seen cases where ActiveX from DTS would not execute on SSIS. Give it a shot. Have you tried it at all?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

You need to use the Script Component Task for this kind of Excel interoperability. There are numerous code examples for both VB and C#.Arthur My Blog

A good place to start with the Excel interop: Working with Excel Files with the Script TaskArthur My Blog

I am new to scripting can you provide some examples. I would just need a script to export 3 SQL queries to one excel spreadsheet. Thanks

If you want to send each query to a separate sheet in the same workbook, then you just need to use Data Flow Tasks to use OLE DB Sources to retrieve the data, and send each data flow to an Excel Destination specifying a specific sheet. You may need to supply a "template" sheet to "fill"... Talk to me now on