October 31, 2011 at 3:17 pm
I want to use a Stored Procedure as a Data Source.
I understand that there are some issues with respect to mapping, etc.
Has anyone done this before?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 31, 2011 at 3:30 pm
Could you explain a little more?
I use stored procedures as data sources in a majority of cases without issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 31, 2011 at 3:47 pm
Yeah, it's fine except you have to put in a metadata component first.
SP as a data source runs into trouble when it's multi-statement, particularly if you use INSERT INTO #tmp. It confuses the parser and the metadata goes kersplat.
What you need to do is put in a defining component first, then the rest of the script. My usual pattern is something like:
CREATE PROC SSIS_Source
AS
IF 1=0
BEGIN
SELECt
CONVERT(VARCHAR(50), NULL) AS Col1,
CONVERT(INT, NULL) AS col2
END
... proc goes here.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 31, 2011 at 4:40 pm
On several blogs it was stated that you have to SET fmtdata OFF. SET NOCOUNT ON, etc.
On another blog someone JOINED sys.objects and sys.indexes WHERE ObjectName = 'SPName'.
I wasn't to make sure that I can map the columns. I will need to use the Data Conversion Task because the Destination is Excel, Uni-Code Issue.
I was planning on Creating Several Local Temporary Tables to gather Aggregates based on various Time periods based on the current Date.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 31, 2011 at 4:58 pm
Evil Kraig F (10/31/2011)
Yeah, it's fine except you have to put in a metadata component first.SP as a data source runs into trouble when it's multi-statement, particularly if you use INSERT INTO #tmp. It confuses the parser and the metadata goes kersplat.
What you need to do is put in a defining component first, then the rest of the script. My usual pattern is something like:
CREATE PROC SSIS_Source
AS
IF 1=0
BEGIN
SELECt
CONVERT(VARCHAR(50), NULL) AS Col1,
CONVERT(INT, NULL) AS col2
END
... proc goes here.
Thanks Kraig.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 31, 2011 at 5:55 pm
Welsh Corgi (10/31/2011)
Thanks Kraig.
My pleasure. You're correct though, you do want NOCOUNT off, but that's standard for me (sorry, was pseudocode above). FMTDATA setting is only needed under a particular set of circumstances, but if you're getting a wonky error it never hurts to toss it in.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2011 at 8:22 am
Evil Kraig F (10/31/2011)
Welsh Corgi (10/31/2011)
Thanks Kraig.My pleasure. You're correct though, you do want NOCOUNT off, but that's standard for me (sorry, was pseudocode above). FMTDATA setting is only needed under a particular set of circumstances, but if you're getting a wonky error it never hurts to toss it in.
I think you mean NOCOUNT ON 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 1, 2011 at 11:56 am
Evil Kraig F (10/31/2011)
Yeah, it's fine except you have to put in a metadata component first.SP as a data source runs into trouble when it's multi-statement, particularly if you use INSERT INTO #tmp. It confuses the parser and the metadata goes kersplat.
What you need to do is put in a defining component first, then the rest of the script. My usual pattern is something like:
CREATE PROC SSIS_Source
AS
IF 1=0
BEGIN
SELECt
CONVERT(VARCHAR(50), NULL) AS Col1,
CONVERT(INT, NULL) AS col2
END
... proc goes here.
Craig,
Thanks again for your help.
I need to make the following column headings and several others dynamic:
CONVERT(VARCHAR(5), NULL) AS [2010],
CONVERT(VARCHAR(25), NULL) AS [2011 YTD],
CONVERT(VARCHAR(25), NULL) AS [OCT 2011]
If I perform:
SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE()))
Returns the value 2010 but no column heading.
Do you know of a way to do this?
Thanks!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 1, 2011 at 12:07 pm
nm - misread your post
November 1, 2011 at 12:11 pm
Could you use dynamic SQL?
If so, you can do this:
DECLARE @SQLCommand NVARCHAR(MAX)
SET @SQLCommand = 'SELECT ' + CHAR(10)
SET @SQLCommand = @SQLCommand + 'TestVal AS ''' + CAST((SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE()))) AS VARCHAR(4)) + ''''
PRINT @SQLCommand
November 1, 2011 at 12:14 pm
Try this:
SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE())) [2010]
I don't know that dynamic column headers is wise, as SSIS is very particular about metadata, and will likely have a problem with header names that change.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
November 1, 2011 at 12:25 pm
i *think* he wants [2010] to be the dynamic column header decided by that select query
November 1, 2011 at 12:49 pm
Welsh Corgi (11/1/2011)
I need to make the following column headings and several others dynamic:CONVERT(VARCHAR(5), NULL) AS [2010],
CONVERT(VARCHAR(25), NULL) AS [2011 YTD],
CONVERT(VARCHAR(25), NULL) AS [OCT 2011]
If I perform:
SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE()))
Returns the value 2010 but no column heading.
Do you know of a way to do this?
Thanks!
Do NOT use dynamic column headers. SSIS will constantly require you to 'touch' it to refresh the metadata, and that'll be for each run that changes the column information. Also, you'll have to re-route and rename anything that flows from that point into the dataflow.
DynamicColumns + SSIS = bad.
(And yes, I did mean NOCOUNT ON, heh, whoops. 🙂 )
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2011 at 3:10 pm
Evil Kraig F (11/1/2011)
Welsh Corgi (11/1/2011)
I need to make the following column headings and several others dynamic:CONVERT(VARCHAR(5), NULL) AS [2010],
CONVERT(VARCHAR(25), NULL) AS [2011 YTD],
CONVERT(VARCHAR(25), NULL) AS [OCT 2011]
If I perform:
SELECT DATEPART(YEAR,DATEADD(year,-1,GETDATE()))
Returns the value 2010 but no column heading.
Do you know of a way to do this?
Thanks!
Do NOT use dynamic column headers. SSIS will constantly require you to 'touch' it to refresh the metadata, and that'll be for each run that changes the column information. Also, you'll have to re-route and rename anything that flows from that point into the dataflow.
DynamicColumns + SSIS = bad.
OK thanks for the tip. Currently the Column Headings and data are being derived from an Excel Macro.
I need to come up with dynamic aggregates for the following columns:
20102011 YTDOCT 2011 MTDSEPT 2011AUG 2011Week of OCT 23Week of OCT 16Week of OCT 9Week of OCT 2
Previous Year, YTD, MTD, Previous Month, 2 Months Ago, Previous Week, etc.
All what fun.
I'm about to populate the last tab and use the existing macro which uses funky R1C1 Excel Notation. :w00t:
Thanks again Craig. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 2, 2011 at 3:59 am
Why not just have the "column headings" be the first row of the data you're inserting into the excel spreadsheet?
EG, do something like:
SELECT
Val1,
Val2,
Val3,
2 AS RowOrder
FROM [Table]
UNION
SELECT
'Val1 Column Header',
'Val2 Column Header',
'Val3 Column Header',
1 AS RowOrder
ORDER BY RowOrder
The table that is being UNION'ed to can be dynamically created in your stored proc. Then you just need to insert the columns in the appropriate order.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply