DTS job - import to excel

Question

Post reply

DTS job - import to excel

am-244616

SSCarpal Tunnel

Points: 4936
More actions
December 27, 2006 at 11:56 am

#76632

Guys,
I want to create DTS job to import data from a table on nightly basis to a excel spreadsheet.
select * from employee where deptid = 1
the results of this query should be imported to a excel spreadsheet as a daily night job.
any suggestions/inputs would be helpful
Thanks

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

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

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 1

All I want to do is the following

I have 2 select statements

select * from employee where daterecorded between '11/01/2006' and '11/31/2006'

select * from person where id in (select id from employee where daterecorded between '11/01/2006' and '11/31/2006')

The results of these 2 sql statements should be imported to excel spreadsheet with corresponding 2 tabs for results of each of this sql statement.

I plan to pass the dates to stored procedure for the select statements

Can this implemented with stored procedure and DTS job

any suggestions would help

Thanks

MohammedU SSCoach Points: 19644 More actions · Answer 2

Yes, you can do it using dts package and can be scheduled too...

In your DTS you should drop and recreate tables in Excel other wise by default sql appends the data to excel...

You may find good info at SQLDTS.COM

MohammedU
Microsoft SQL Server MVP

Luis Heredia Valued Member Points: 65 More actions · Answer 3

hola, no se mucho escribir en ingles, asi que lo pongo en spanish, una vez me encontre con esa misma situacion y lo que encontre fue lo siguiente:

1-creo mi conexion de sql de donde voy a sacar los datos

2-creo mi conexion a excel y le especifico la ruta del archivo que se va a crear.

3-luego los conecto con el [Tansform Data Task] y luego le indico la sentencia sql que necesito, luego le doy a [Destination] y se vera el nombre de la tabla que el proceso generara, en este caso sera [New_Table], luego le damos a OK.

4-luego agrego Active Script Task y le agrego esta funcion:

Function Main()

Dim iMsg

set iMsg = CreateObject("CDO.Message")

Dim objMail

Set objMail = CreateObject("CDO.Message")

objMail.From = " l.heredia@unibe.edu.do"

objMail.To = " l.heredia@unibe.edu.do"

objMail.AddAttachment ( "C:\DTS\DATOS_EXCEL.XLS")

objMail.Subject="DATOS EXCEL"

objMail.TextBody = "DATOS EXCEL"

objMail.Send

Set objMail = nothing

Main = DTSTaskExecResult_Success

End Function

NOTA: que el C:\DTS\DATOS_EXCEL.XLS es el nombre del archivo que le puse a la conexion que cree en Excel.

5-Luego agrego un Execute Sql Task

y le indico que me borre la tabla que se creo [New_Table] y le digo que me la cree otra vez de esta forma:

DROP TABLE New_Table

GO

CREATE TABLE `New_Table` (

`AdmitidoID` Long ,

`Nombres` VarChar (244) ,

`CarreraID` VarChar (2) ,

`CarrDescripcion` VarChar (80) ,

`PlanEstudioID` VarChar (10) ,

`PlEsDescripcion` VarChar (60) ,

`NaCiDescripcion` VarChar (60) ,

`CategoriaFinancieraID` VarChar (2) ,

`AdmitidoComoID` Long ,

`AdCoDescripcion` VarChar (60) ,

`ColegioID` Long ,

`ColeDescripcion` VarChar (60) )

6-Luego selecciono EXECUTE SQL TASK y CONNECTION SQL y voy al menu WORKFLOW y le doy a ON SUCCESS

7-Luego selecciono EXCEL y el SCRIPT y voy al menu WORKFLOW y le doy a ON COMPLETION

8-Luego lo guardo y si quiero le pongo Schedule y a probar se ha dicho

Cualquier duda me agregas al MSN kavallo3582@hotmail.com

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 4

Can you please post in english, I dont understand spanish

jfb SSCrazy Points: 2469 More actions · Answer 5

I can help you to translate but here is a better example.

http://support.microsoft.com/?kbid=319951

Rgds

JFB