.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

SSIS flat file performance improvement

Posted By:      Posted Date: September 30, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

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!  

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

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 R2 Flat File Destination Producing Duplicates


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

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.

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?

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?

Problems running a bat file within a SSIS package

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

SSIS multiple data flows accessing the same raw file

I have multiple data flow tasks accessing the same raw file at virtually the same time.  I would like to know if the data flow tasks lock the raw file during read?  Can multiple data flow tasks read the same raw file at the same time.  I'm currently developing with a very small data set, but I would like to know if I should be concerned with the possibility of the data flow tasks competing for access to the same raw file. Thanks.
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