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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Upload Text File data to SQL table Using SSIS

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server
 
I have a text File with following format  (  HED - Header Row, CL-Data (Sample 3 Rows) , TRL-Trailer Row)


HED201008240001PCMCL


CL 012352908607309000014090 2010-03-04 16:12:53.077000000 Inserted / Log ICode Action on Order Number / 33949591 / with / I-Code / IDEC/ / Memo: / cci questions about alert/


CL 012352958107309000020378 2010-04-14 10:20:03.340000000 Inserted / Log ICode Action on Order Number / 36992594 / with / I-Code / ISRB,INEW,ILKD/ / Memo: / cci to cxl, saved with benefits and gave her instructions to move account online/


CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Inserted / Log Customer Info Change Action CL 012353013807309000021681 2009-11-24 17:09:05.973000000 Updated / Log Customer Info Change Action


TRL201008240001PCMCL 0000007349



I have to Import The above Mentioned data in to Following Table



CREATE TABLE [dbo].[Log]

[Type] [varchar


View Complete Post


More Related Resource Links

SSIS - load table from source text file with multiple record lengths

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

Some problem in Data Flow while upload a "||" seperated text file

  

I am facing one problem with sql 2005 SSIS while uploading a "||" seperated text file with Data Flow in SSIS.Below I am giving the sample data of the text file

data||data||data        

data||data||data

When I uploaded it using sql 2000 DTS it was not giving and it was being uploaded into the table but when I tried with SQL 2005 it is giving the below problem.

"[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 2" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". "

 

 


Load multiple record length text file into table using SSIS

  

I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

SqlBulkCopy How to Use to Insert Data From text CSV file to Sql Table

  

hi,

I am New to using the  SqlBulkCopy Can any one tell me how to use it and how many rows maximum we can insert using the SqlBulkCopy

how to habdle the  exceptions when bulkcopy inserting


Insert Excel Data via File Upload into sql server database???

  
Hi all, I have requirement that User can Upload the Excel Sheet Data to sql server Database at once. How i do that Any Article or hint is apperciated.   Regards 

Data loading from text file

  
Hi, I am loading the data from a flat file into a sql table. The values in one of the column have spaces at the end of the value. I want to remove those spaces and load into the table. I am thinking of using derived column and use rtrim on that column, how can I use that as an expression. Thanks.sqldev

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.

Need to upload images with data, but image url's are in separate db table

  
Hello, I'm making a simple homes classifieds section and when a home ad is created, a HomeID is automatically created for it in the db.  I need the images to upload and enter the associated HomeID with each image automatically.  What I tried isn't working, because it looks like it's looking for the HomeID in the codebehind before it gets entered into the db.ASPX CODE<asp:SqlDataSource ID="SqlDataSource1" runat="server"         ConnectionString="<%$ ConnectionStrings:ConnectionString %>"         InsertCommand="INSERT INTO [Homes] ([UserName], [Street], [Details], [Contact], [Phone], , [Thumb], [Price], [Approved], [Posted]) VALUES (@UserName, @Street, @Details, @Contact, @Phone, @Email, @Thumb, @Price, @Approved, @Posted)"         SelectCommand="SELECT [HomeID], [UserName], [Street], [Details], [Contact], [Phone], , [Thumb], [Price], [Approved], [Posted] FROM [Homes]" >        <InsertParameters>            <asp:Parameter Name="UserName" Type="String" />            <asp:Parameter Name="Street" Type="Str

Saving SSIS results to Log or text file using dtexec

  
Hi, How to save the SSIS package results to log file using dtexec command............please help regaridng this...........   Thanks in advance,

Upload millions of file records to a table

  
I have six input files having millions of record. I need to upload these data to my table. How can I upload the details in the most efficient way. Any help on this will be greatly appreciated.

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

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

  
I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

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!  

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~

saving a text file in ms access data base

  
i have to read a text file in a text box then have to save it into ms access data base in column format how could i do this....

How can we insert csv file data into Sql server table based on some condition ?

  

Hello All experts there,

 

I need to insert some csv file data into sql server 2008. but there are multiple tables created in sql server and I need to check some conditions before data insertion and based on the condition value, sql script should insert the data in to specific table. in other words I need to insert data from one csv file to different tables based on some condition.

is it possible? if yes then can anyone please give me an idea on how can we do that?

 

Thanks in advance

Regards

Nipendra Garg


Can i upload file to database table using AsyncFileUpload ?

  

Hi,

can i upload my pdf or word file to oracle/SqlServer database table ?

How ? 


Categories: 
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