I want to copy the data from Oracle table to excel file in DTS packages.
I have used transformation task, but it is asking for table but excel is a file.
please any body could tell em about this?
Thanks and Regards,
Purushotham,Use connection first.
In connection you will be able to find Excel files or Oracle.
Good Luck.
I have the following query;
SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME)) AND (DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112)
AS DATETIME)) AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)
what I would like to do is reference 2 cells in Excel with dates if I need to run this manually. How would I change the sql to do this. Thanks
DateTime1 = cell(1,1)
DateTime2 = cell(2,1)
Something like this:
Code Snippet
declare @.DateTime1 datetime,
@.DateTime2 datetime
select @.DateTime1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')
select @.DateTime2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')
SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (@.DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME))
AND (@.DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112) AS DATETIME))
AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta')
AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)
You'll need to ensure that 'Ad Hoc Remote Queries' is enabled on your instance to allow OPENROWSET to function.
|||Expanding upon Dale's suggestion, if you want to be able to run the code as it is, or on occasion, manually, then it would be better to create a Stored Procedure and call the Stored Procedure. (NOT tested and not asserted to be accurate or runnable -presented for ideas only.)
This procedure can be called in this fashion:
EXECUTE PROCEDURE dbo.MyProcedure
In this case it will attempt to find date values in the Excel File and use them.
If the cells are empty, then the procedure will use the columns DateTime1 and DateTime2.
(From the original posting, I'm assuming that there are such columns...)
EXECUTE PROCEDURE dbo.MyProcedure '2007/06/01', '2007/07/01'
In this case, the procedure will use the dates supplied.
Code Snippet
CREATE PROCEDURE dbo.MyProcedure
( @.InDate1 datetime = NULL,
@.InDate2 datetime = NULL
)
AS
BEGIN
DECLARE
@.ExDate1 datetime,
@.ExDate2 datetime
SELECT @.ExDate1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')
SELECT @.ExDate2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')
SELECT TOP 1
DateTime,
TagName,
[Value],
Time = convert( varchar(15), DateTime, 108 )
FROM v_AnalogHistory
WHERE ( coalesce( InDate1, nullif( ExDate1, '' ), DateTime1 ) >=
cast( convert( varchar(8), getdate() -4, 112) AS datetime )
AND coalesce( InDate2, nullif( ExDate2, '' ), DateTime2 ) <
cast( convert( varchar(8), getdate() -3, 112) AS datetime )
AND TagName = N'AIT-500_TOTAL_NH2CL'
AND wwRetrievalMode = N'delta'
AND convert( decimal(38,3), [Value] ) IS NOT NULL
)
ORDER BY
convert( decimal(38,3), [Value] ),
convert( varchar(15), DateTime, 108 )
END
GO
