I am fairly new to SSIS, but am having loads of fun... but I've run into an
error that seems obvious, but I cannot seem to diagnose, and I have yet to
actually get a pivot to work. I alway get the error 0xC0202090, "The pivot
key value "11" is invalid."

Unfortunately, I don't see much documentation about this error, and the
error itself is fairly worthless. What exactly does "invalid" mean? Wrong
type? No match? Neither of these seems to be my issue, though, because the
data types are all INT and I have accounted for all of possible values. My
setup is pretty straightforward:

Pivot Input (Name, Type, PivotUsage)
ID_LOGIN DT_WSTR 1
PLAN_SEQUENCE DT_I4 2 (Possible values 11,12,13,14,21,22,23,24)
PLAN_SCHOOL DT_WSTR 3

Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 4170
SEQ11_NAME 11 4195
SEQ12_NAME 12 4195
SEQ13_NAME 13 4195
SEQ14_NAME 14 4195
SEQ21_NAME 21 4195
SEQ22_NAME 22 4195
SEQ23_NAME 23 4195
SEQ24_NAME 24 4195

The only thing I know is that the error is definitely coming because of the
input into the pivot transformation, but since the input column type is
DT_I4, it surely cannot be invalid because of the type. How else could it be
invalid?!!?!

I even tried them as strings, and got the exact same result.

Do you know exactly what this error means?

--
Joel D Kraft
Case Western Reserve University

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Joel,
I understand that you were using Pivot Transformation component in SSIS to
transfer data, however you encountered the error regarding invalid pivot
key.
If I have misunderstood, please let me know.

I recommend that you check if you set SourceColumn to the LineageID of
PLAN_SEQUENCE. If so, please correct it to the LineageID of PLAN_SCHOOL
since the pivoted values are actually from PLAN_SCHOOL, while ID_LOGIN and
PLAN_SEQUENCE are pivoted rows and columns.

Generally for a pivoted table, the layout is as following:
============================
rows | data
So if the sourcecolumn (data area) was specified to match other column
(rows area), it is invalid.

Also based on your description, I performed the following test and it
worked fine:
================================================== ==================
Step 1. Create a test table

CREATE TABLE LOGIN_INFO
(
ID_LOGIN nvarchar(20) PRIMARY KEY,
PLAN_SEQUENCE int,
PLAN_SCHOOL nvarchar(20)
)

INSERT INTO LOGIN_INFO VALUES(N'SEQ11_NAME',11,N'SCHOOL11')
INSERT INTO LOGIN_INFO VALUES(N'SEQ12_NAME',12,N'SCHOOL12')
INSERT INTO LOGIN_INFO VALUES(N'SEQ13_NAME',13,N'SCHOOL13')
INSERT INTO LOGIN_INFO VALUES(N'SEQ14_NAME',14,N'SCHOOL14')
INSERT INTO LOGIN_INFO VALUES(N'SEQ21_NAME',21,N'SCHOOL21')
INSERT INTO LOGIN_INFO VALUES(N'SEQ22_NAME',22,N'SCHOOL22')
INSERT INTO LOGIN_INFO VALUES(N'SEQ23_NAME',23,N'SCHOOL23')
INSERT INTO LOGIN_INFO VALUES(N'SEQ24_NAME',24,N'SCHOOL24')


Step 2. Create a SSIS package in BIDS and run it

1. Drag an OLE DB Source to the Data Flow pane and have the connection
point to the test database and select the table LOGIN_INFO.

2. Drag a Pivot Transformation component to the Data Flow pane, drag the
green line from OLE DB Source to Pivot.

3. Double click the Pivot component and choose all the three columns as
Input Columns and then switch to the "Input and Output Properties" tab,
check and record the LineageID of every input column:
ID_LOGIN: 32
PLAN_SEQUENCE: 35
PLAN_SCHOOL: 38

then expand Pivot Default Output->Output Columns, click Add Column to add
the following columns:
Pivot Output (Name, PivotKeyValue, Source)
ID_LOGIN (none) 32
SEQ11_NAME 11 38
SEQ12_NAME 12 38
SEQ13_NAME 13 38
SEQ14_NAME 14 38
SEQ21_NAME 21 38
SEQ22_NAME 22 38
SEQ23_NAME 23 38
SEQ24_NAME 24 38

4.Click OK and drag an Excel Destination to specify an excel file to export
data, double click the component, click New... to specify the excel file,
click New... beside "Name of the Excel sheet" to create a new sheet with
the name LOGININFO, switch to Mappings, and click OK.

5. Run the package.

Step3. Check the Excel file to see if the output is correct.

================================================== ================

Anyway I admit that the warning is not friendly and informative, we welcome
you give Microsoft your feedback at https://connect.microsoft.com/sql so
that our product team can hear your voice closely and improve this feature
in future.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know. Have a nice day!



Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Awww I figured it out... I abbreviated what I was doing for my post, because
I was trying to not convolute things. There was actually a second output
field, and one of the possible pivot values was was missing for that second
output column. I will definitely submit a suggestion about the error
message. It should have been something like "An output field for %1 is not
mapped for pivot value %2."

I'm surprise this type of error isn't caught before the package executes,
though.

Thanks!
Joel


--
Joel D Kraft
Case Western Reserve University