import one csv file into multiple related tables using SSIS ( sql server 2012)

How can I import a single  csv file into multiple tables where tables have referential integrity.

For example I need to import a file with name,make,model,waranty,bodystyle into the following tables
make -id,make
model-id,model,makeid (makeid references id from make table) Id is identity
bodystyle-(id,bodystyle) Id is Identity
vehicle(id,makeid,modelid,bodystyleid...) Id is identity here however all these makeid,modelid,bodydtyleid references to Ids from above tables.

Thanks,
DB SupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
If you import the file into a "queue" table (i.e., a holding table), you'll have all the freedom to process the raw table records, first adding the needed IDs to parent tables, then to your final detail table.

You can make a job that does the SSIS in one step, then in a second step runs a stored procedure against the newly imported data, dispensing it in order.
0
DB SupportAuthor Commented:
Thank you, but is there a way to directly import into without using staging/queue tables as the requirement is not to use staging tables.
0
dsackerContract ERP Admin/ConsultantCommented:
You can BULK INSERT to load the file straight into a temporary @Table first, and distribute from there. In essence, you'll be eliminating SSIS, but this gives you the benefit of using a temporary table and meeting your company's requirements.

Or is this some homework assignment in college? (grin)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DB SupportAuthor Commented:
It is indeed an assignment.
0
dsackerContract ERP Admin/ConsultantCommented:
Research BULK INSERT. Use it as many times as you want to first add records to your parent tables, then to your detail tables.

Kinda non-standard, but hey, you have a prof to please. :)

Also ask him if a temporary memory table (DECLARE @MyTemporaryTable TABLE) is acceptable.
0
DB SupportAuthor Commented:
can I have a solution instead ? Yes we may use temporary memory table.
0
dsackerContract ERP Admin/ConsultantCommented:
How can I import a single csv file into multiple tables where tables have referential integrity.
The solution to this question was in my explanation on how to go about it. Since this is a class assignment, you really should work out the exercise yourself, else you won't learn nor appreciate what is taking place.

I will give you some additional steps, but it would be wrong to do all the work for you. If you have a heart to learn, you will appreciate and respect this very much.

Regarding BULK INSERTs, make your temporary table according to fields in your input file. The temporary table should look something like this:
DECLARE @YourTemporaryTable TABLE (
    Field1     varchar(10),
    Field2    varchar(50),
    ...
    LastField varchar(10) )

Open in new window

Obviously, you'll name @YourTemporaryTable whatever you want. I usually simply name mine @Table. Also, the Field1, Field2, should be named to signify what they really are in your input file.

Practice loading that temporary table from your BULK INSERT. The pseudo-code will look something like:
BULK INSERT @YourTemporaryTable
    FROM 'C:\YourFolder\YourFile.csv'
    WITH (
        FIELDTERMINATOR = ' | ',
        ROWTERMINATOR   = '\n'
         )

SELECT * FROM @YourTemporaryTable

Open in new window

If your delimiter is a comma, change the FIELDTERMINATOR to a comma. Make sure the number of fields in your temporary table match those in your file.

Like I said, play with it until you can successfully SELECT * from your table. Then from there, you can start populating your parent and detail tables. That should be easier to do on your own.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DB SupportAuthor Commented:
Thank you .
0
dsackerContract ERP Admin/ConsultantCommented:
I respect your willingness to take it on. Hope you come out of school ahead of the pack and land a great job. Good luck.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.