Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Trying to populate multple tables from a single source data file.

Posted on 2013-01-11
7
Medium Priority
?
265 Views
Last Modified: 2016-02-11
I am trying to populate a bunch of tables from data that is contained in a single text file.  Not all target tables have the same number of columns.  The text file has rows of data, with the first piece of data telling which file type (which target table) the subsequent data in the row is for.  

See attached file for some sample data.

I tried just reading the source file into a single table, with the intent of parsing it and with t-sql and sending the rows of information to the appropriate target tables.  But I couldn't get the import wizard to read the text file.  It complained that the data was being truncated.  I went in and adjust the field lenghts (it thought there were 3 fields) to the maximum, 255.   But to no avail, it still would read it.

Thanks for your help!

Cipirano

Attached file has some data, and below are a few rows:

REC_TYPE_1|1|3.0
REC_TYPE_2|1|20120927 08:49:00|101|234223444|19750101|M|Brian|ODYSSEY|0513782||BEACH,BRIAN||||JOSEPH|ODYSSEY|025676||||||||||||SATURN MAIN theater|0513782|28497469||101
REC_TYPE_3||||||||1|13|ADV|||||||||||BEACH|||||||||ENGLISH
REC_TYPE_4|234227744|19990101|M|BRIAN||||BEACH,BRIAN
REC_TYPE_5|FOOD||3|||||JOSEPH|ODYSSEY|025676||0|||0|0.0||||1||||JOSEPH|ODYSSEY|025676|||||||||||||||||||||||||||||||||N|||| N/A|No upcoming days.|||||1||ADV||||||||0|102||111
REC_TYPE_6||||JOSEPH|ODYSSEY|025676|||Prelude
REC_TYPE_6||||JOSEPH|ODYSSEY|025676|||Hotel
REC_TYPE_6||||JOSEPH|ODYSSEY|025676|||Plant
REC_TYPE_7|||||
0
Comment
Question by:cipriano555
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38769216
You can do this with the Conditional Split Transformation in SSIS:
Conditional Split Transformation
http://msdn.microsoft.com/en-us/library/ms137886(v=sql.90).aspx
0
 

Author Comment

by:cipriano555
ID: 38770128
I'll try that, sounds promising...guess if I can get it to take the pipe as the delimiter it seems to be perfect.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38770226
guess if I can get it to take the pipe as the delimiter it seems to be perfect.
Absolutely.  You can use any delimiter you like.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38770234
The trick is to come up with a key to be able to join all these rows after the fact.  There are a couple of ways to do that:
1. Use a key common to all the records.
2. If there is no common key, create a surrogate and add it to all the outputs.  This could be as simple as an incrementing value (1, 2, 3, ...)
0
 

Author Comment

by:cipriano555
ID: 38779603
It doesn't read the records in correctly.  I specified | as the delimiter.  REC_TYPE_1 as you see above has 3 fields.  So, it appears to think all records have 3 fields and so, for example, REC_TYPE_3 has tons of |'s in its third field. I hate to do a manual fix but in desperation I inserted a first row that has the maximum number of fields anticipated.  It looks like this:

header|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

But now I think it is not recogizing the start of a new row correctly.  And there are still columns which contain bunches of |s. So it is not always using | as a field delimiter.

Please see attached image.

I think my kludge (puting in a rows of ||||| at top)  is not really helping.
SSIS-Not-Recognizing-Next-Record.JPG
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38780368
You need to read up on the Conditional Split Transformation.  You are clearly not using it and you will not be able to import your file that way.

If you need a tutorial on how to use it, I will see if I can find something.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 38781353
1. First Take the Whole file data to temp table in database and iterate each row as you require.

Take each column value in variables and insert to your desire table based on condition.

As Above said you can use Conditional split. Or Condition in Direction.

If @Variable == 1 then This way
else second way...
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question