SSIS ForEach Column loop

Question

Post reply

SSIS ForEach Column loop

bartedgerton

SSCrazy

Points: 2062
More actions
April 28, 2017 at 4:38 pm

#330002

I am trying to pivot a very very wide source table (not my design!) into rows in my destination table.   Can someone tell me if there is a better method to do this than what I've come up with, and, if not, can anyone see an issue with the parameterized query I'm trying to use for an OLE DB Source, that I can't seem to get configured...
So I have a pretty simple table of questions and answers, something like this:
CREATE TABLE [QA_rowperquestion] (
     [ContactId] INT
   , [Question] VARCHAR
   , [Answer] VARCHAR
);
I have a bunch of data in this table from other sources, now I have to add new data source which is arranged differently:
CREATE TABLE [QA_rowperclient] (
     [ContactId]
   , [Answer1]
   , [Answer2]
   , [Answer3]
...
)
I have to pivot about 200 question columns in this new data source into rows in the destination table, and it's possible that those columns may change in the source without notice, so this needs to be dynamic, hopefully...
I've got an SSIS package with a ForEach loop container formed from a metadata query that pulls a list of columns currently in the source table, and then executes a data flow for each column.
The plan is to run a query to load my destination table, and use the column name variable to dynamically get the right column "question" (aka column name), using a OLE DB Source with a parameter:
SELECT
     [ContactId]
   , '?' AS [Question]
   , ? AS [Answer]
FROM [QA_rowperclient]
Only problem is that SSDT is puking on configuring the parameter ordinals. I get one of two errors "The parameter type for '@P1' cannot be uniquely deduced.." or "The batch could not be analyzed because of compile errors".   I can map the parameter to my faking out the UI by using a query that doesn't give me what I want, but at least this proves that I have the User::ColumnName parameter working.
This works:
SELECT * FROM [QA_rowperclient] WHERE [id] = ?
This doesn't:
SELECT ? FROM [QA_rowperclient]
The problem seems to be more like SSDT can't parse out the query than I'm making an error.   Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182901 More actions · Answer 1

SELECT ? FROM [QA_rowperclient] is not a valid query
😎

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 2

I can't help thinking that someone needs to fix the design of this whole thing but, if you absolutely need it, please see the following article for how to do a dynamic CROSS TAB, which is an ancient but still very effective method of doing a pivot and you don't need SSIS, SSDT, or any of the other SQL Server 4 letter words. 😉
http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246944 More actions · Answer 3

You cannot parameterize column names in a query, so I think that this is the reason for the errors you are seeing.
If these 'questions' do not have some associated 'Question' table, I'd suggest that you create one and do some normalising. Even if you cannot change the design of the source data, you can do something to post-fix it for your own purposes, in your ETL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin