Hi
Here is a screen shot showing the nulls in the text file (hex = 00). When exporting this to a flat file text file in a data flow task, it treats the nulls as a carriage retun line feed and ignores the rest of the line. We need to either replace the nulls with a space, or get the data flow to ignore nulls. The nulls are part of a long field string. The source file is a SQL table which needs to output to a text file.
Solution within SSIS preferred.
Thanks
text file screenshot showing nulls
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi
Thanks for your response. It works perfectly now.
We can't do anything about the format of the original file, but it does get initially imported to an SQL table before being exported to the the text file in the example. I have added an execute SQL task to the package to replace the nulls in the table before it is exported to text.
Business Accounts
Answer for Membership
by: stelth240Posted on 2009-10-20 at 12:17:11ID: 25617415
The normal way to replace a value is to user REPLACE() but I don't know that you'd be able to enter that null value into the function correctly. What's causing the null characters to be in the string in the first place? In the first sentence you said "...screen shot showing the nulls in the text file... When exporting this (the text file in the screen shot?) to a flat file text file (another text file?)..." Does that mean you are going from a text file to another text file, or is the source coming from somewhere else?
I'm asking this because if the data is originally stored in SQL Server, you can use this to remove the null characters:
REPLACE(DataColumn, CHAR(0), ' ')
You would do that in the source component or in the stored procedure or view called in the source component.
If the data is originally text, you can store it temorarily into a table in SQL and then do the same replace. I'm not sure of a way to remove them in SSIS since there is no CHAR() function that I'm aware of.