December 15, 2015 at 1:09 pm
SQL Server 2012 Integration Services:
A simple package with a CSV input and an OLE DB Destination transformation to a table with this definition:
CREATE TABLE [dbo].[ApplicationList](
[Initiative] [varchar](50) NULL,
[TS Area] [varchar](50) NULL,
[App Code] [varchar](50) NULL,
[App Name] [varchar](900) NULL,
[Risk Availability] [varchar](50) NULL,
[Ownership] [varchar](50) NULL,
[Exit Approach] [varchar](50) NULL,
[Phase Exit Date Planned] [varchar](50) NULL,
[Start Date] [datetime] NULL,
[Design Exit Date] [datetime] NULL,
[DEV Exit Date] [datetime] NULL,
[SIT Exit Date] [datetime] NULL,
[PAT Exit Date] [datetime] NULL,
[Target Exit Date] [datetime] NULL,
[Exit Status] [varchar](50) NULL,
[Target Exit Date Planned] [varchar](50) NULL
) ON [PRIMARY]
Note that all the columns are varchar(50) except for the dates and one which is varchar(900)
However, when I point to this table in SSIS Designer, SSIS is seeing the varchar columns as nvarchar(255) except for the longer column which it thinks is nvarchar(300).
Why is the OLE DB destination component getting the datatypes (and lengths) wrong and how can I fix it?
Gerald Britton, Pluralsight courses
December 15, 2015 at 1:17 pm
I don't know why it got things wrong, but you can edit your connection and make the required connections.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 15, 2015 at 1:18 pm
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.
Of course. however the connections are already correct.
Gerald Britton, Pluralsight courses
December 15, 2015 at 1:57 pm
g.britton (12/15/2015)
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.Of course. however the connections are already correct.
I think Alvin meant 'corrections'.
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
December 15, 2015 at 2:05 pm
Phil Parkin (12/15/2015)
g.britton (12/15/2015)
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.Of course. however the connections are already correct.
I think Alvin meant 'corrections'.
Oops! Hate it when my fingers and brain are out of sync!
I meant: "you can edit your connection and make the required corrections."
Thanks Phil.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 16, 2015 at 7:15 am
Alvin Ramard (12/15/2015)
Phil Parkin (12/15/2015)
g.britton (12/15/2015)
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.Of course. however the connections are already correct.
I think Alvin meant 'corrections'.
Oops! Hate it when my fingers and brain are out of sync!
I meant: "you can edit your connection and make the required corrections."
Thanks Phil.
Well. OK, but the connections are already correct. There are no corrections to be made. Still, in design mode SSDT-BI is pulling the wrong schema from the database.
Gerald Britton, Pluralsight courses
December 16, 2015 at 7:54 am
g.britton (12/16/2015)
Alvin Ramard (12/15/2015)
Phil Parkin (12/15/2015)
g.britton (12/15/2015)
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.Of course. however the connections are already correct.
I think Alvin meant 'corrections'.
Oops! Hate it when my fingers and brain are out of sync!
I meant: "you can edit your connection and make the required corrections."
Thanks Phil.
Well. OK, but the connections are already correct. There are no corrections to be made. Still, in design mode SSDT-BI is pulling the wrong schema from the database.
- Go into Advanced Editor for the OLEDB destination.
- Select Input and Output Properties.
- Expand OLE DB Destination Input.
- Expand External Columns.
- Select each column whose data type you want to change and change it.
But, as Alvin already stated, we don't know why this is happening.
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
December 16, 2015 at 8:07 am
Phil Parkin (12/16/2015)
g.britton (12/16/2015)
Alvin Ramard (12/15/2015)
Phil Parkin (12/15/2015)
g.britton (12/15/2015)
Alvin Ramard (12/15/2015)
I don't know why it got things wrong, but you can edit your connection and make the required connections.Of course. however the connections are already correct.
I think Alvin meant 'corrections'.
Oops! Hate it when my fingers and brain are out of sync!
I meant: "you can edit your connection and make the required corrections."
Thanks Phil.
Well. OK, but the connections are already correct. There are no corrections to be made. Still, in design mode SSDT-BI is pulling the wrong schema from the database.
- Go into Advanced Editor for the OLEDB destination.
- Select Input and Output Properties.
- Expand OLE DB Destination Input.
- Expand External Columns.
- Select each column whose data type you want to change and change it.
But, as Alvin already stated, we don't know why this is happening.
I suspect you pointed SSIS to a different table than you wanted, or that the table has been modified since the connection was made.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 16, 2015 at 1:24 pm
Go into Advanced Editor for the OLEDB destination.
- Select Input and Output Properties.
- Expand OLE DB Destination Input.
- Expand External Columns.
- Select each column whose data type you want to change and change it.
Actually I tried that. But it didn't persist. After OKing the changes I opened up the advanced editor/io props/oledb input/external cols and they were back to their (incorrect) values.
Anyway, I left it for a while and when I came back, SSDT got the correct schema. Somehow got itself messed up. Who knows?
Gerald Britton, Pluralsight courses
December 16, 2015 at 1:27 pm
Sometimes the best option is to delete the connection and recreate it.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 17, 2015 at 5:49 am
Sometimes the best option is to delete the connection and recreate it.
This has often been my experience as well.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply