SSIS 2008 - Truncate Data

Hi Experts,

This is my question:
Is there a way to truncate the data rather than error if it’s over 400 characters?
Is it SSIS that handles this?
Or is this done in SQL Server?

If so please help and show a way to do so?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rtayIT DirectorCommented:
In SSIS use the derived column transformation with a substring expression.

an example would be if you want to return the a total of 10 characters starting with the first character
rtayIT DirectorCommented:
One warning that I forgot was that the data type must be DT_WSTR.  So you can change the data type to that string format and then change it back to the original type before database insert if necessary.
Amour22015Author Commented:
i myself am kind of new to SSIS, but what would SSIS have to do with Truncation problems with a table in SQL?
I mean would not the problem be maybe some kind of SP?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rtayIT DirectorCommented:
Maybe I don't understand your question.  Are you bringing in data from a source file / database etc and trying to normalize it to bring it into a destination table?  If you are, and one of the fields is over 400 characters and the destination only accepts 400 characters, you would use the derived column to fix the issue before the data is inserted.  

if that is not what your looking for, please give me some more info and I will see if I can help.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Give us more information. How are you importing the data?
Without knowing more I would say that a simple LEFT(FieldName, 400) would be a valid solution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Amour22015Author Commented:
These are the questions I need to go back with and ask, thanks.
Amour22015Author Commented:
So this is my response to the question:

I was hoping you would be able to look at the SSIS and let us know if the developer set the import for that field to truncate rather than error if the data coming in from the file is over 400 characters.

So how would one do this?

Looks like there would be some kind of SP that could take care of this?

I don't think SSIS deals with this directly?

Right now I am having trouble opening the SSIS package I get this:
See Attachment
Vitor MontalvãoMSSQL Senior EngineerCommented:
First of all you can insert the image directly in you post.
Second, the error is access denied meaning that you don't have access to open the SSIS package.
Amour22015Author Commented:
Turns out I did not have permission for SSIS package so I will not be able to check.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.