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


Post New Web Links

Load multiple record length text file into table using SSIS

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

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


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

Upload Text File data to SQL table Using SSIS

  
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

How to load a new table with the value of a variable from SSIS package?

  
Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

  
Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

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.

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,

SSIS FOR each loop to load 1 file after other into database for particaular folder

  
HI I am using ssis 2008,i have folder in which i have 10 excel file.i wont to load them one after other in data  base I have created a Dataflow task that load one file into database.i have inserted this dataflow task into For each loop and in For each loop i have give that folder path and *.XLSX  and in variable mapping i have created one variable  which i have passed in Database destination connecion string (properties->expression) but still i get error "cannot acquire connection from connection manager" please guide me or provide me with some reference so that i cud load file from folder into database one after other

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

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

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

  
Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

Reading each record from a text file and sending email

  
Hi All, I have a text file (CSV) which has only three fields (Name, Email, SendYN). I want to read each record from this text file and send email to those records where SendYN field has Y. I had created Flat File connection and a Flat File source in a data flow which uses Flat file connection. Now I stuck how to move further. Which control I need to add and where to identify whether SenderYN = Y and how to send email? I am new to SSIS. Thanks.

Unable to load data from txt file with text delimiter specified, if the data has the same character

  

Have come across a strange scenario and seeking your ideas/thoughts.

I'm using a ssis package to load a txt file. The txt file is text delimited by double quote (") and column delimited by tilde (~). It works fine in most scenarios and strips the enclosing "" from the columns -no problems there. However, in a specific scenario where the last column has double quote in the data as well being the text delimiter, the package throws the following exception indicating that it's missing a column delimiter

 The column delimiter for column "COL_NAME" was not found

This behaviour was not observed when double quotes were present as data in any other column in the file. Was able to workaround by disabled text delimiter only for the last column, but that isn't a perm solution. Also observed that if the " was replaced by escape sequence /" the file loaded successfully. Wondering if this a known issue ?


http://bennyaustin.wordpress.com

How to save a multiple user control to a few single record text?

  
I have a list with people pecker control that admits multiple users.
I like to have another list with one record for each user in the control that loaded before.
Is there any way in InfoPath or in a workflow to use something like FOR EACH to take one by one user in a control and write/update records after that?
Thank in advance
Ramiro

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]
blog.ganeshzone.net

Populate ASP Table from Text File

  

Hey Guys, new developer here. I'm trying to populate a ASP table dynamically. This is what I'm trying to do.

I have table with 1 row and 4 cells. I want to import information from a text file into the various cells. Each line of the text file is what I want to go into each cell.

For example if the text file reads:


Date

Time

Path

User


I want each line to go into each cell. Is this possible, or will I need to create a SQL Server table and read from that? THANKS!


How to load a text file into a div

  

Good evening ASP Programmers,

I have several text files, myText1.txt, mytext2.txt, ..., stored in a folder TxtFiles.  When the the page loads I'd like to display one of the files in a div, <div id="txtDisplay" class="txtFormat"></div>.  I don't know how to do this!   I relatively new at ASP and am floundering; any assistance would be greatly appreciated.  I'm one of those VB guys, so VB suggestions would be especially helpful. 


SSIS importing fixed length file to sql.

  

Im trying to import fixed length files to sql. data in it s not importing correct im getting the values incorrect.

it takes the rowdelimiter as also an character and calculating it wrongly. here s my code. if u can plz give some alternate sample code

private

 

void CreateSourceColumns()

{

 

// get the actuall connection manger instance

RuntimeWrapper.

IDTSConnectionManagerFlatFile90 flatFileConnection =

connectionManager.InnerObject

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