Stored proc into Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Stored proc into Excel

Author

Topic

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-04 : 05:22:01

Hi Guys,

I'm in the process of trying to take the data from the last table create in a certain database into a spreadsheet which i can then distribute out to certain individuals

the proc i've created is

CREATE PROC USP_LastTableExtract
AS

BEGIN

DECLARE @TableName varchar(100)
DECLARE @SQL nvarchar(max)

SET @TableName =
(
SELECT
TOP 1 ss.name+'.'+st.name [TableName]
FROM
sys.tables st
INNER JOIN
sys.schemas ss
ON
st.schema_id = ss.schema_id
GROUP BY
ss.name,st.name
ORDER BY
max(st.create_date) desc
)

SET @SQL = 'SELECT * FROM '+@TableName

EXEC(@sql)

END

But then when i try and use a OLE DB source editor in ssis to extract the columns using a "data access mode" - "SQL Command" in preview this works fine and extracts the data, but then on the output columns it does display anything

is there a way to do this

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-06-04 : 05:43:43

Please anyone

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-04 : 13:13:10

use the query as source without last dynamic part and store result onto a ssis variable created. then using expression builder map datasource of oledb source to this variable to take correct tablename.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources