June 19, 2018 at 2:57 pm
I have a csv file which I am processing through SSIS. But there are companies in the list which have some produce data and the length of those values are more than 4000 bytes which is causing this job to fail. When I change the Commodity data type to text, then the solution file complains about the conversion from unicode to non-unicode. Any idea what to do here?
CREATE TABLE [dbo].[Commodity](
[CompanyNids] [nvarchar](50) NULL,
[Commodity)] [nvarchar](MAX) NULL
) ON [PRIMARY]
June 20, 2018 at 8:26 am
NewBornDBA2017 - Tuesday, June 19, 2018 2:57 PMI have a csv file which I am processing through SSIS. But there are companies in the list which have some produce data and the length of those values are more than 4000 bytes which is causing this job to fail. When I change the Commodity data type to text, then the solution file complains about the conversion from unicode to non-unicode. Any idea what to do here?
CREATE TABLE [dbo].[Commodity](
[CompanyNids] [nvarchar](50) NULL,
[Commodity)] [nvarchar](MAX) NULL
) ON [PRIMARY]
Having changed your table, did you re-edit the components that detect the columns and their data types? What you posted for the table structure is clearly unicode values in the table, so I'm not sure when you say you change the data type to "text", exactly what you mean. If you did use text as opposed to ntext, that might make sense, but having ntext over nvarchar(max) is of no value, and would be a step in the wrong direction. You'll need to provide the details on exactly what you are doing in your SSIS package, and be much more detailed about your connection manager(s?) and what the package is doing. We can't see what you see, so please be as detailed as you can.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 21, 2018 at 7:06 am
sgmunson - Wednesday, June 20, 2018 8:26 AMHaving changed your table, did you re-edit the components that detect the columns and their data types?
Yes, I did.
sgmunson - Wednesday, June 20, 2018 8:26 AMYou'll need to provide the details on exactly what you are doing in your SSIS package, and be much more detailed about your connection manager(s?) and what the package is doing. We can't see what you see, so please be as detailed as you can.
I have a csv file with 50000+ records which I have to process. I am using SSIS to update one of our table which contains 2 columns (CompanyNids, Commodity). From a flat file connection, by going to advance option (Input and output columns), I am changing the data type from String to Unicode with 4000 bytes for commodity. I have already shared table structure. So I execute the package it runs and processes data but some of the values are not getting through because of the length.
I exported the data into an Excel, used the length function and found out that some of the values in Commodity column are bigger than 4000 bytes and that's the concern. I have to find a way to process all the data even when the length exceeds 4000 characters.
Please let me know if I need to provide anyone with more information.
June 21, 2018 at 11:34 am
In the flat file connection manager make sure the column is defined with a length long enough for the longest value.
June 21, 2018 at 12:44 pm
Joe Torre - Thursday, June 21, 2018 11:34 AMIn the flat file connection manager make sure the column is defined with a length long enough for the longest value.
I tried that but it threw an error. The max length it accepts is 4000
June 21, 2018 at 3:21 pm
NewBornDBA2017 - Thursday, June 21, 2018 12:44 PMJoe Torre - Thursday, June 21, 2018 11:34 AMIn the flat file connection manager make sure the column is defined with a length long enough for the longest value.I tried that but it threw an error. The max length it accepts is 4000
Just wondering if that file actually contains Unicode characters? If not, you could use DT_STR with a length up to 8,000 characters. You can then add a DerivedColumn Transformation to convert it to DT_WSTR or whatever SSIS uses for nvarchar(max). Alternatively, if you discover there are NO unicode characters in the table or in the file, then change the table to varchar(8000) or varchar(max) depending on what the maximum length of the data is.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 22, 2018 at 11:11 am
NewBornDBA2017 - Tuesday, June 19, 2018 2:57 PMI have a csv file which I am processing through SSIS. But there are companies in the list which have some produce data and the length of those values are more than 4000 bytes which is causing this job to fail. When I change the Commodity data type to text, then the solution file complains about the conversion from unicode to non-unicode. Any idea what to do here?
CREATE TABLE [dbo].[Commodity](
[CompanyNids] [nvarchar](50) NULL,
[Commodity)] [nvarchar](MAX) NULL
) ON [PRIMARY]
If you changed the input data type of an existing input column in your source, make sure you manually change the data type for the corresponding output column in the source as well. That doesn't happen automatically and often leads to data type conversion errors.
June 26, 2018 at 7:38 am
sgmunson - Thursday, June 21, 2018 3:21 PMJust wondering if that file actually contains Unicode characters?
It does contain Unicode characters.
June 26, 2018 at 7:39 am
sestell1 - Friday, June 22, 2018 11:11 AMIf you changed the input data type of an existing input column in your source, make sure you manually change the data type for the corresponding output column in the source as well. That doesn't happen automatically and often leads to data type conversion errors.
This is something I took care of before running into issues.
June 27, 2018 at 1:10 pm
NewBornDBA2017 - Tuesday, June 26, 2018 7:38 AMsgmunson - Thursday, June 21, 2018 3:21 PMJust wondering if that file actually contains Unicode characters?It does contain Unicode characters.
Then you'll need DT_NTEXT iin SSIS and nvarchar(max) as the destination column data type. See here for some semi-relevant commentary where the data source was Excel, but ignore that portion and focus on the parts that mention what I just did about DT_NTEXT and nvarchar(max).
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply