.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Issue: Load {NUL} from flat file to "Flat File Source" faild.

Posted By:      Posted Date: August 28, 2010    Points: 0   Category :Sql Server
Hi guys, I've met a strange issue when I was working to use SSIS to load data from flat file to database. It is a story about {NUL}. Sample Flat File: FIELD_SAMPLE|OTHERS ABC{NUL}DEFG|Any Others   I placed an Flat File Source to load these into package, and the configuration of the Connection Manager are as following: FIELD_SAMPLE: Unicode string [DT_WSTR], Length 8   Then I click the "Preview" button and I can see this data in the "Preview" window correctly (with the value "ABC{NUL}DEFG"). But when I tried to run this package, I've got an error that the "ABC{NUL}DEFG" is too long for this field. Then I changed the length to 20 and it was loaded into SSIS package. But I found the value in SSIS package is "ABC", and "{NUL}DEFG" was not there~ I have no ideal why it is ok in Preview but it is not ok when the package running~     Anyway, I hope to load the {NUL} from the flat file into target database. So that is there anybody could give me a hand?

View Complete Post

More Related Resource Links

Load and parse a file where the file path is located in a column of a flat file source

Hi, I have a situation where I have a CSV that contains a bunch of data that populates a bunch of related tables with FK constraints directly.  Thats no problem.  The problem is that one of the columns specifies a filepath to an XML file that is supposed to contain data to be populated in a table with an FK constraint.  How do I specify the file name to the XML source component at runtime? For example TableA has columns: TableAID GroupName TableB has columns TableBID TableAID (FK to Table A) MemberID (the data in question) TableA has a 1 to many relationship with Table B. The CSV has a bunch of columns including: TableAID File path to list of members for each group. I don't know how to solve this problem.  I tried using a ForEach ... but I couldn't get it to work. Thanks in advance for any assistance.

AjaxContorlToolkit - issue: Could not load file or assembly

Hi everyone. Can somebody please tell me how to solve this problem with my ajax controls ?I've tried with copy the .dll's files in the project, and seems they are working fine in the visual studio with drag&drop, but when I'm trying to start the project i got this error. Thanks a lot.--------------------------------------------------- Server Error in '/' Application. Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.IO.FileLoadException: Could not load file or assembly 'AjaxControlToolkit.resources.ar' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Assembly Load Trace: The following information can

How capture bad records from "Flat File Source" in SSIS


The flat file is having 10 columns so used 9 delimitor. Few records in the Flat file is having more than 10 columns. I need to redirect in to some error table and rest valid records needs to be loaded in target. Pls help.

SSIS: Error in the package when the data in the flat file source is modified


Hi All,

I have a package which loads data from a flat file source to an OLEDB destination, which is executed successfully and data is loaded perfectly.

But later when the data in the flat file source is modified i.e. if an extra column gets added to the text file, the package is throwing an error because it is unable to detect the extra added column.

How can i avoid this error??? I need my package to execute successfully ignoring the changes(added columns) happened in the flat file source.

Please provide me wth your suggestions and solutions....

Thanks in Advance!!

StreamReader issue with flat file



Please observe this flat file image: http://screencast.com/t/YMpeI5pOlI

You will on the bottom of the text file, line numbers shown by MSVS 2005 that tell me that a line exists, yet with no TEXT along side it, the text file has too many carriage returns at the bottom. Lets call these BLANK ROWS of data in my text file.

The issue is when I import this data like (vbNET):

Dim oRead As StreamReader
Dim str As String
Dim rows(0) As String

oRead = New StreamReader(FullPath)
str = oRead.ReadToEnd()
str = str.Replace(vbCr, "")
rows = str.Split(ControlChars.Lf)

The rows array has entries within it for the BLANK lines I show in the image attached. And this mucks up my indexes when I manipulate that rows data.

Can I do a 'Replace' or something to clear these BLANK lines before they get into my rows array ??

At the moment I just clean up the text file before import, but I need a coding solution to make my life easier !

Any ideas ! What do you do ?

Flat File Source Task Failure



I have an ETL process where I am loading flat files int a table with a Foreach Loop task. I came accross a file that keeps failing the SSIS package as the Flat File Task tries to read it. Is there a way quick way to locate the problem within the Flat file (text file).

Here is the error:

[Flat File Source [1]] Error: The column data for column "Immediate Cash Amount" overflowed the disk I/O buffer.

[Flat File Source [1]] Error: An error occurred while skipping data rows.

Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202091.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.





Dynamic directory location for "Flat File Source"



I am using a "Flat File Source" to read from a source file. Now I want the directory location to be dynamic because it will change during migration from development to UAT to Production.

Can anyone help me here on this. Ofcourse my intention is not to open and modify the package after migrating into a different environment. Actually I want to store the directory location in a table and want the "Flat File Source" to read the location form that table duing runtime. But not sure whether can I achieve this or not. If yes then how?

Regards - Koushik

Flat File Loading Problem -> Messed Up Delimiters and Error Logging Issue

Hey Everyone,

I have a Data-Flow task embedded in a Sequence Container (does not fail component on error) on the Control Flow panel of the SSIS designer.  This data flow task contains a connection to a Flat File Source -> A Data Transformation -> Into an OLE Db Destination.

The problem is that the Flat File isn't always delimited properly -> the client cannot be relied on to do this.

My question is when the delimiters are messed up, how can I capture the offending error row(s) from the Flat File Source?

What I've tried:
1) Set every column in the source flat file on error to: Redirect Row
2) Added a Script Transformation to pull the description and the record id out of the offending row
3) Added an Error file flat file destination to the end of the flow.

The package always fails on the Flat File Source and never Redirects the offending Row to the error output - I never see my onError Script Transformation go Green, Red, or Yellow - SSIS doesn't let it get there.

I'm really new to SSIS so sorry if this is a super basic question.

Here is the Error Text:

[Source - InventTable_csv [1]] Error: The column delimiter for column "RECID" was not found.
[Source - InventTable_csv [1]] Error: An error occurred while processing file "C:\------InventTable.csv" on data row 15228.

Can I Describe a Flat File Source to Save SSIS User Some Effort


I'm a complete SSIS beginner.  I suspect the answer to my question is out there I just don't know enough to construct the search that would find it.

I'm writing a program to generate fixed width format text files from proprietary databases for use as Flat File Sources in SSIS packages.  I know I can provide a header row containing column names.  But I have a lot more information about the file (obviously!).  I know how many columns there are, what the width of each column is, what the type of each column is, etc.

Is there some way I can record the information I have so that the SSIS user consuming my file doesn't have to input all that information through the Import and Export Wizard? 

For example, if I were generating these files for use with the BCP utility I could create a Format File to describe the structure of each field in my data file.  Is there some similar capability in SSIS or do the consumers of my data files have to input InputColumnWidth, Data Type etc. through the Advanced editor dialog box?

Not reading from flat file source when using a hidden share

I've got a Foreach (file) Loop Container set to go across all the files in a folder on a remote server.  These are existing shares across numerous servers.  When I use the current share path to the documents (\\servername\share$\folder) it loops across all the files but the flat file source gives me 0 rows of data.  If I share out the folder directly (\\servername\folder) then it loops across all the files and brings back all the data in those files.

No errors either way, but if I use the \share$ in the path the file source can't seem to get a handle on the files.  The Foreach Loop doesn't care either way.  Is there a known issue with using a hidden share UNC, or am I looking at the wrong issue?

Problem in Date Format While Exporting To Excel Destination From Flat File Source In SSIS



I have a flat file(.csv) which contains data(strings incuding date in yyyy-mm-dd format).This i am using in Flat File Source and want to export those data to an Excel Destination.Before exporting to Excel Destination i have created a Derived Column component where i am using an express to get day,month,year part and creating a date sting in dd/mm/yyyy format then conveting it to DT_DATE datatype and then exporting to Excel Component.But while exporting to Excel this date format is getting changed to mm/dd/yyyy format.

The expression i used in derived column is

(DT_DATE)(SUBSTRING(date,9,2) + "/" + SUBSTRING(date,6,2) + "/" + SUBSTRING(date,1,4))

where date is a column from Flat File Source.

Even I changed the Locale to English(United Kingdom) in Flat File Connection Manager for the above .csv file.But still it didnt work.

I searched a lot in google and failed to get any solution on this.

Can anyone help me in solving this issue ?

User validation Issue in xlsx file



I am validating user from Login.xlsx file. It was working fine by validating email and password from that file. For testing i got new file same contents of file but added some more user names.

So after that when i validating the user i am getting the following error "Data type mismatch in criteria expression".

But when i replaced the new to old one it is working fine. The contens of both files are the same. So why this kind of error happened.


Could not load file or assembly - Web Deployment Project



I have the following projects in a solution :

- A Web Application

- A Class Library

- A Web Deployment project

The Web Application compiles and run fine when I don't go through the Web Deployment Project. However when I try to use that, I get the following error:

Error 1 An error occurred creating the configuration section handler for hibernate-configuration: Could not load file or assembly 'NHibernate' or one of its dependencies. The system cannot find the file specified. (F:\Folke\Folke.csproj_deploy\Release\web.config line 4) 1 1 Folke.csproj_deploy

"An error occurred creating the configuration section handler for hibernate-configuration: Could not load file or assembly 'NHibernate' or one of its dependencies. The system cannot find the file specified. (F:\Folke\Folke.csproj_deploy\Release\web.config line 4)"

This is the beginning of my Web.config:


Parser Error Message: Could not load type MasterPage File



Server Error in '/' Application.

Parser Error

Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Could not load type 'wamsilverlight.Web.Wam'.

Source Error:

Line 1:  <%@ Master Language="C#" AutoEventWireup="true" CodeBehind="Wam.master.cs" Inherits="wamsilverlight.Web.Wam" %>
Line 2:  
Line 3:  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Source File: /Wam.Master    Line: 1

Version Information: Microsoft .NET Framework Version:2.0.50727.4952; ASP.NET Version:2.0.50727.4927


All of the following have been tried over and over again:

1.) Delete all existing .dlls

2.) Delete MasterPage File and recreate it.

3.) Delete MasterPage perform a c

Can Not load WPF project in Visual Studio 2005 on Win7 64 system, says "Project file not supported b

Dear all I am facing an issue in creating new wpf project or loading a WPF project in Visual studio 2005. I have installed Visual Studio 2005 Service pack - 1 (http://www.microsoft.com/downloads/details.aspx?familyid=bb4a75ab-e2d4-4c96-b39d-37baf6b5b1dc&displaylang=en) but still I can not see the option for wpf project  while creating a new project. Generally we can see WPF listing under item File -->New-->Project--> ".Net Framework 3.0" in VS 2005. I have few WPF project and when i try to load them it says "Project file not supported by this Installation". My System Config is given below: OS: Windows 7 (64 Bit) VS: Visual Studio 2005 with Service Pack 1 .Net Framework: 3.5 (By default inbuilt in Win7 OS)   I am suspecting something bugging related to .Net framework 3.0 in Win7. We have one option in control panel to alter .NetFramework 3.5 under "Turn Windows Features on or Off" but there I can not see something related to 3.0 :-(.   Kindly suggest how can I load my existing project and how can enable option to create new wpf Projects.   I saw few solutions suggested by people on following Thread bt most of them related to Windows XP or Vista: http://social.msdn.microsoft.com/Forums/en/vswpfdesigner/thread/06cf7bca-982c-44cf-aec9-99cf399b3000 Do someone has solution for Windows 7 (64 bit OS)

Join 2 flat file data flows - retain unmatched rows

I have two data flows from two separate flat files. They may contain matching IDs (account number), in this case specific data from each flow should be used to create one row. When there is no match, the rows would stand on their own. At the end of the flow, I need both flows combined into one flow, with one record for each key record (account number). If I were able to use a look-up, I could easily union the no-match data flow back into the match data flow and have the desired result. I cannot use a look-up, since the source is flat files, but this is exactly the functionality I am trying to achieve. Solutions I want to avoid: staging tables, and cache transformations. Any ideas are appreciated.

Inserting a row on an extract flat file

Hi, I want to add in a header row to an extracted file. The file contains 5 columns of data but I only want to add in a 5chr id (stored in a variable) in the very first line of the extracted flat file.. please can you assit and tell me the best way of doing this..
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend