.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

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

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
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!  

View Complete Post

More Related Resource Links

Import flat file from the report to filtered data



Is there a way to use a flat file which could be imported from the report in order to filtered the data displayed ?



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

Import Excel file with Data Connection into SSIS


I have got an Excel spreadsheet with a Data Connection that I want to import into SSIS. The problem is that the Data Connection does not seem to run at the time the file is imported, so it only calls "old" data.

How can I set SSIS up to run the Data Connection first, or is there something I need to change in my Excel spreadsheet setup?

When a person opens the Excel file normally, they first need to "Enable" the Data Connection in the Security Settings. Once that is done the data will update immediately. I wonder if that is something I somehow need to change to make it work in SSIS?




I want to import values of current_time, name, and age from the XML file below. Then I will export those values into sql.

by using "xml source" I can recieve values from name and age, but not from current_time.

my question is, how to get

SQL 2005 Import of Flat File always truncates data longer than 50 chars

My source data is a flat file, semicolon-delimited, and has three columns.  The first is 10 chars, the second can be up to 240, and the third can be up to 240.  For the import I select "Flat File Source" for the Data Source.  When I select my source file, I use the Flat File Manager, Advanced tab, to configure it as follows:

I leave the defaults for Column 0.
For Column 1 I select a DataType of Unicode String and set the OutputColumnWidth to 240.
For Column 2 I select a DataType of Unicode String and set the OutputColumnWidth to 240.

On the Destination page I choose "SQL Native Client", and point it at my database.  The destination table has 3 columns.  Column 0 is CHAR(1), Column 1 is NVARCHAR(240), and Column 2 is NVARCHAR(240).

However, when I get to the "Select Source Tables and Views" page of the wizard and hit the "Edit" key under "Mapping", it shows a size for all three columns of "50".  Sure enough, when I perform the import, it truncates everything to 50 characters.

How do I get more than 50 characters to import?

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.

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.

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

Read Binary Data which is nothing but a Zip file and unzip through SSIS 2005 SP2

Hi ALL, I need some help in developing a task. I have a source database which is Oracle and it has a ZIP file stored inside the database in Binary format. When I move this data into the sql server 2005 database I get the data as binary data. Now the task begins with SSIS, I need to read the binary data which gives us a zip file and then unzip this zip file and read the XML data which is present inside the Zip file. I beleive some one might have already developed this task can you share the solution with us. Note: As this has to be moved into production I dont have permission to use third party tools like Cozy roc or install winrar.exe and simpy calling this exe from the execute process task in SSIS.  Raju

Filter out logically deleted data from dbf file using SSIS?

Hi Guys, while loading dbf file to sql server using SSIS,How can i filter out the records that are logically deleted in dbf file?    ~Technology Analyst~

How to import data from xml file into a dataset


I want to import data from a xml file into a dataset
this is my xml file  


<?xml version="1.0" encoding="UTF-8" standalone="no"?>


  <Person Personid="10">



    <Birthday>1360 </Birthday>














when I use this code
I have these tables in my dataset
But I want to have FIVE (more) tables for Fax,Mob

data type problem in import data from excel file


Dear All,

I am importing the data from excel file using following code.

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(strFolderFileName) & ";Extended Properties=HTML Import;"
                conn = New OleDbConnection(connstr)
                Dim strSQL As String = "SELECT * FROM [" & strWorkSheetName & "]"

                Dim cmd As New OleDbCommand(strSQL, conn)

                Dim da As New OleDbDataAdapter(cmd)

Now the problem is if a coulmn vlaue start with a number value like "15" then the other string value like "W15" in that column is ignored in the datatable.

eg. The excel column value     Column1


Data conversion failed --flat file connection manager


Hello All,

Can any one help me in this .

[Flat File Source [16]] Error: Data conversion failed. The data conversion for column "acct" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Flat File Source [16]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "acct" (373)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "acct" (373)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED.  Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.  There may be error messages posted before this with more information on why the thread was cancelled.


can any one help me in this error :


[Lookup [465]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Lookup" (465)" failed because error code 0xC020901E occurred, and the error row disposit

Is there any Data Flow Task in SSIS to merge 3 different recordsets (from 3 different tables) and se

I have a requirement, that has 3 different Execute SQL Task that returns 3 different recordsets which differ from each other.  I want to know whether these 3 recordsets can be sent as Input to a Script Component.  I know that Script Component accepts only 1 Input... But is there any way, to have this done

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

Text was truncated with "nvarchar(max)" data type and size by using SSIS Import and Export Wizar



I am using SSIS Import and export Wizar to load data from Excel file, I have set the datatype as "nvarchar(max); those data are not biger than 1000 bits but was failed to transfer as the error says:

[Source - TreatmentPlans$ [1]] Error: The "output column "SchoolPlan" (57)" failed because truncation occurred, and the truncation row disposition on "output column "SchoolPlan" (57)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Create Excel file dynamically in SSIS package and fill it by resultset data returned by Stored proce


Hello Every One,

I have a scenario in which i am executing a Stored Procedure which returned a result set.

I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].

Point is that the excel file should have unique name so i want to give name as name_date_time.

So every time when package run new file is created with unique name.


Can any one suggest me who to create Excel file and fill result set data into Excel file.

And then finally send that Excel file ?







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