December 27, 2006 at 11:56 am
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
December 27, 2006 at 3:25 pm
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
December 28, 2006 at 12:23 am
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
December 28, 2006 at 6:43 am
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
December 28, 2006 at 7:15 am
Can you please post in english, I dont understand spanish
December 28, 2006 at 7:30 am
I can help you to translate but here is a better example.
http://support.microsoft.com/?kbid=319951
Rgds
JFB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply