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

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

Flat File Source Task Failure

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server


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.





View Complete Post

More Related Resource Links

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

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?

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.

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!!

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

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?

Foreach Loop Container & File System Task to copy files from target server to source server


A one-off shot at using, SSIS, I appear to be missing something. 

From what I have read here, the Foreach Loop Container in which a File System Task has been placed is how one copies files contained in a source server folder to a folder on a target server.  I have defined Source and Destination pkg variables using UNC \\servername\aharedfolder and a FileName variable that is empty.  My impression is that this is suppose to iterate through and copies all files in the source folder to the target.  I only get the first file copied.

In the FLC Collection section  I am using the Foreach File Enumerator under which I have the Folder as \\servername\sharedfolder and files as *.* .  I am not using Expressions.  The Variables Mapping reference my FileName variable.

In the FST, I both path variables set to True and reference the destination and source variables described above.  Operation is Copy File.

I appears that I need to pass a list of the source folder file names to the FileName variable, rather than some built-in logic grabbing a files in the source folder.  Is a passed filename list what I am missing?

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 ?

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..

Export db to flat file

I'm trying to export data from a database to a flat file using the tool import and export of SQL Server 2008, I is generating an error that says: Error 1 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Tienda_Nombre" (201) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 2 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Proveedor_Nombre" (203) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 3 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Depto_Nombre" (205) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Error 4 Validation error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Subdepto_Nombre (207) is 1252 and is required to be 20 127. anaquel.dtsx 0 0 Validation Error 5 error. Data Flow Task 1: Data Flow Task 1: The code page on input column "Clase_Nombre (209) is 1252 and is required to be 20 127. I saved the package and opened it, I saw that in the metadata columns that I got error I get the value of 1252, flat file connection says that the code page is 20127. What should I do to correct these errors?

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

How do you specify special characters as flat file delimiters?

I have a flat file that has fields delimited by tilde {~} and rows delimited by {CR}{LF}.  The problem is, my source is spitting out the {NULL} (&H00) at the end of the row. The data displays correctly in the pre-viewer (with a non-print symbol at the end of the last field), but gives an error when I try to run the package. Example: "data"~"data"~"data"{NULL}{CR}{LF} I can process the row in c# using: "\0\r\n" as the row delimiter.   What is the equivalent notation for SSIS?   Thanks,

How to go to next iteration of ForEach Loop container on task failure

I'm using a ForEach Loop container to iterate through a set of files and import/validate the data.  Within each loop, the first thing that happens is that I fill a variable from a table and then use a script task to make sure that the rest of the package should be run for this particular file.  If it should continue, I set Dts.TaskResult = Dts.Results.Success, and if it shouldn't, I set it to Dts.Results.Failure.  My question is, how do I get it to exit the current iteration of the loop and continue to the next iteration?  The way it is now, the package just stops completely.  I set the FailPackageOnFailure property to False for the script task, but that doesn't make a difference.  I also tried pointing a failure precedence contstraint bacl to the loop container, but that's not allowed.  How can I make it just continue in the loop?   Thanks.

Need an example of error in OLE DB Source component when source is Excel file.

I want an example of error (not truncation) in individual row or individual cell that can occur in OLE DB Source component when source is Excel file. I'm trying out Error Output of data task component.  I have already tried out Error Output of OLE DB Destination component.

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay
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