.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

SSIS 2008 R2 Flat File Destination Producing Duplicates

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

I am writing to a flat file destination in a 2008 SSIS package. 99.99% of it works correctly. However, I get one duplicate record in the destination file.


Here is the basic flow of the package:

 1. Read two ISO-8859-1 encoded files and encode their text to UTF8 in memory

 2. Combine the two files together in memory and load them into a lookup cache

 3. Read another source file from disk

 4. Match an ID column

View Complete Post

More Related Resource Links

SSIS Flat File Destination


I have a requirement to output some large data sets as flat files.

They have specified the file format and I have no issues with all but one thing. They want the data files to end with "the standard EOF character that is used in Linux". When I look at the file I produce with SSIS in a text editor that shows the HEX values the last thing I see in the file is the newlline (0A) chactare for the last line. How can I get it to insert this EOF character they want?

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 ?

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

ssis 2008 Loading MULTIPLE EXCEL file

HI it is possible for me to load multiple text file using for each loop,but if i try the same process for EXCEL file then my excel source throws an error. Also when i created my variable in value  i have given the path of my excel file ,still it didnt help me..because the moment i map the variable with the connection string of source excel connnection(property->expression->connection string) it start giving error if you could help me or provide some reference it will be of great help http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html i tried this link but got stuck with it on step 12 and 14 (below  is my connection string) and variable is @Filename which i have created Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mushtaq.sheikh\Desktop\ForEach\New Microsoft Excel Worksheet1.xlsx;Extended Properties="Excel 12.0;HDR=YES"; The moment i try to edit my connection string by comparing connection string provided in link it gives error(an OLEBD error has occured with error code 0x80040E73) on excel source Thanks in advance

Adaptive Flat File Destination Columns

I'm sure it must possible to adjust the columns for a flat file output based on the input, but so far have not found the right tools. I have to convert quite a few tables to flat files, where the input columns have not been safely specified yet. The tables are still being changed.   e.g. I have a table with columns A,B,C. Columns D,E,F... could be added later, maybe B or C will even be removed or renamed. I have to transfer all data from the table except for column A to a flat file.   I find it annoying having to adjust the columns manually every time. Would be extreemly thankful for your advice.

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

SSIS flat file performance improvement


I have 10 text files which are on average of 6  GB in size. Out of those 10, 4 files average up to 10 GB in size.

We have 10 SSIS packages which loads those text files onto 2 staging tables. Each 5 of them loads 1 table.

Inside the package, its only a flat file source, data conversion for all columns from source, and write into the destination table.

And this process takes around 5 hours to complete. the size of 2 tables after the load completes is 60 M and 40 M each.


The other thing which I have observed is all the packages are scheduled through one schedule and the schedule  have 10 diff sql server jobs( for 10 packages). Does it have an impact on the processing time ,as all these jobs will be

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

Accessing a flat file/folder via SSIS that is out of my domain


I'm trying to copy a file to a desitnation in an SSIS package that requires a username and password in order to connect. I am doing this in VB Script task and have tried this:


Dim ServerShare, UserName, Password As String
Dim NetworkObject, FSO As Object
ServerShare = \\someserver\sharename$

Prevent SSIS from creating an empty flat file


I created a SSIS to export to a flat file (from a SQL command : a stored proc).

I don't wan't my SSIS to create an empty file if there is no data.


How can I achieve this ?





SSIS Script Componet to change Flat File LF to CR LF



I have a corrupt Flat File and I need to write a SSIS Script Component to Replace LF to CR LF in my flat file.

Any idea how to do it?



SSIS 2008: Problem reading dynamic file during execution



I have a SSIS 2008 project that reads a bunch of files from a file (always the same file) from Machine A.
My package is running on Machine B and this is where the information is being imported to, my Reporting DB (sql 2008) is also located in Machine B. 
I have a dynamic log file that stores the execution errors every time the package is executed and its name is made up of the timestamp of when package starts executing. The "DelayValidation" option for this connection is set to "TRUE". When i execute my Package from Visual Studio 2008, it runs without a problem.
The problem is when i deploy my package into SQL Server 2008 and try running it through DTEXEC. I always get the following error:


Error: 2010-11-03 14:57:29.06
   Code: 0xC001401E
   Source: MyImportPackage Connection manager "ImportLog"
   Description: The file name "\\ip-address\d$\Logs\ImportManager\20101103_145726.txt" specified in the connection was not valid.

SSIS Codepage Err on Flat File Inport


I'm trying to import a flat file into a table. nothing complex I thought but I get this err and i'm baffled -

The code page on column manager_id (70) is 1252 and is required to be 65001.

What's going on here and how do I fix this?

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?

SSIS 2008 - Question on OLEDB Destination to SQL Server 2000

Simple question, In a SSIS 2008 Package is it possible to have a OLE DB Destination pointing to another Server Machine running on SQL Server 2000 to do bulk insert operation ? . what are the potential pitfalls when while inserting into SQL Server 2000 ?

SSIS 2008 and output txt file of specifies structure


Hi All.
I have query to Oracle 11.

I need to iterate it and make txt file (UTF8).
In fact it consist of header + body + footer
body is made of records like:
"Afghanistan","Aland Islands","Albania"
values of in quotes. (in fact it is json file)

1?) Can I do it w/o script
 using Foreach Loop Container
 I don't see how to do
S_all = S_all + ',"' + field1 + '"'
where S_all is SSIS var

2?) If use script in data flow
Could anybody give code example (bettre in VB but C# also ok)
how to iterate recordset and make var like:
S_all = S_all + ',"' + field1 + '"'

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