Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Turn Truncate Off in a Table

Author  Topic 

taunt
Posting Yak Master

128 Posts

Posted - 2014-06-10 : 17:53:06
Hello, I'm trying to import data from a delimited file into a table. I have truncate set to ignore in import file. I run the job it will succeed, but imports nothing. Is there a way to turn off truncate in a table? I believe that is where the issue is. Is there any issues with turning truncate off?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-10 : 17:57:44
You can't turn it off. TRUNCATE cannot activate a trigger either.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-06-11 : 01:30:58
Can you confirm you have INSERT permissions to the table?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-11 : 04:18:27
It depends which truncate you are talking about?
Truncate table as in "empty" the table?
Are you talking about truncating data in column? This is what I think you refer to. You are importing larger pieces of data that can fit into the target columns.
If that's the case, you can turn off warning and error for truncate information.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-06-12 : 13:40:42
quote:
Originally posted by SwePeso

It depends which truncate you are talking about?
Truncate table as in "empty" the table?
Are you talking about truncating data in column? This is what I think you refer to. You are importing larger pieces of data that can fit into the target columns.
If that's the case, you can turn off warning and error for truncate information.




Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



If you do that, don't you also have to setup an error path for the rows that will be skipped due to the truncation? If not, then you would not get that row and have no way of identifying the failure.

The only other options are to increase the size of the destination column, or perform a data conversion to truncate the data so it will fit in the destination column.
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2014-06-12 : 18:56:03
OK I finally figured out what my issue was. I had to turn on retain null values in the original file. That was the issue why the import wouldn't import. I still have a issue with a column truncating (after the 49th character). I have ignore truncate turned on. The column is set to nvarchar(MAX). The conversion is set to length of 4000, but yet the column still gets cut off. What might be the cause of this?
Go to Top of Page

taunt
Posting Yak Master

128 Posts

Posted - 2014-06-18 : 15:16:51
OK I'm beyond stumped. Here's my info... The field in sql is set up as this: nvarchar(max). I'm trying to import from a flat file (a comma separated txt file). The column in question in advanced settings type: string [DT_STR], width: 4000. That goes to a data conversion that has the settings convert to Unicode string [DT_WSTR], width: 4000, but what keeps happening is after the 50th character it will cut off on the additional text. What's the issue? What am I missing?

Thanks
Go to Top of Page
   

- Advertisement -