SSIS, creating columns from rows based on id from result set

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » SSIS, creating columns from rows based on id...

SSIS, creating columns from rows based on id from result set

Rate Topic

Display Mode

Topic Options

Author

Message

crbnldy

Posted Wednesday, January 23, 2013 1:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:57 PM
Points: 2, Visits: 27

I have the following and is getting the attached error. I am trying to loop through a table and change the rows to field names. I do not know in advance hows many rows will be returned for each ID (using a for each loop container to pass the id to this sql task). I am getting the attached error when I try to execute the @query . 8175 in the query is replace with the ? is the SSIS package

Any assistance will be greatly appreciated as I know very little SSIS

DECLARE @cols NVARCHAR(MAX)
SELECT @cols = isnull(@cols + ',', '') + '[' + Col_Name + ']'
FROM (SELECT DISTINCT top 5 dbo.tblQuestions.Question AS Col_Name
FROM dbo.tblQuestions INNER JOIN
dbo.tblResponses ON dbo.tblQuestions.ID = dbo.CSAT_tblResponses.Question_ID
WHERE (dbo.tblResponses.Survey_ID = 8175)) as tb1
ORDER BY Col_Name
DECLARE @query NVARCHAR(max)
SET @query = N'SELECT dbo.tblResponses.Question_ID, dbo.tblQuestions.Question, ' + @cols + ' FROM
(SELECT t1.Col_Name, dbo.tblResponses.Question_ID,
t2.dbo.tblQuestions.Question,
FROM dbo.tblQuestions AS t1 Inner
JOIN dbo.tblResponses AS t2 ON t1.dbo.tblQuestions.ID = t2.dbo.tblResponses.Question_ID
) as p

PIVOT
(
max(dbo.tblQuestions.Question)
FOR Col_Name IN
( '+ @cols +' )
) AS pvt '

execute @query

-------------error-----------

Msg 203, Level 16, State 2, Line 23
The name 'SELECT dbo.tblResponses.Question_ID, dbo.tblQuestions.Question, [Company_Name],[Customer_Name],[Response Time],[Survey ID ],[Survey Response ID] FROM
(SELECT t1.Col_Name, dbo.tblResponses.Question_ID,
t2.dbo.tblQuestions.Question,
FROM dbo.tblQuestions AS t1 Inner
JOIN dbo.tblResponses AS t2 ON t1.dbo.tblQuestions.ID = t2.dbo.tblResponses.Question_ID
) as p

PIVOT
(
max(dbo.tblQuestions.Question)
FOR Col_Name IN
( [Company_Name],[Customer_Name],[Response Time],[Survey ID ],[Survey Response ID] )
) AS pvt ' is not a valid identifier.

Post #1410782

robert.gerald.taylor

Posted Thursday, January 24, 2013 6:47 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 740, Visits: 790

Can you run your generated SQL in SSMS? It looks like there is a problem with the SQL from the error.

HTH,
Rob

Post #1411112

crbnldy

Posted Thursday, January 24, 2013 8:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 12:57 PM
Points: 2, Visits: 27

I did run the SQL in SSMS and got the error above. I am sorry for not including that in the post. I am trying to determine what I am missing in the script why it is erroring in SSMS. ( I am using that script in SSIS after testing in SSMS.)

Post #1411179

robert.gerald.taylor

Posted Friday, January 25, 2013 6:21 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 12:57 PM
Points: 740, Visits: 790

You can't use the T-SQL in SSIS until you get it working in SSMS (as I'm sure you know). I don't have the DDL for your tables, but double click on the 'Msg 203, Level 16, State 2, Line 23' error message (in red). This will move your cursor to the offending T-SQL line in SSMS.

HTH,
Rob

Post #1411661

opc.three

Posted Saturday, January 26, 2013 11:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:46 PM
Points: 6,730, Visits: 11,783

@crbnldy, if you provide some DDL so to create your tables, some DML so we can create some test data and the shape of the expected resultset I am confident we can help you out.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1412054

Phil Parkin

Posted Monday, January 28, 2013 4:10 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 4,247, Visits: 9,500

I echo others' comments about DDL ...

But also noticed that your SQL has a spurious comma between

't2.dbo.tblQuestions.Question'

and 'FROM'

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1412331

« Prev Topic | Next Topic »

Permissions