Once you have the data imported to your staging area in SQL Server, going down the sp route is the way I would go - for performance reasons.

SSIS is great...

Excellent. There are several ways - but the one I would choose is as follows:

1) It should go without saying, but use a copy of your prod database ...

2) Create...

Do you have the descriptions already completed in soft format? A CSV file containing CourseID, Description would be an ideal starting point ...

If not, are you just looking for a...

Now that you have used the wizard to create the basic package, you are in a position to edit and refine it ... You can definitely make it do exactly...

Hmmm, you really need an example for this?

Something along the following lines (untested & I'm getting tired):

INSERT INTO DestTable(Field1, Fieldn, SeqNo)

SELECT S.Field1, S.Fieldn, 1

FROM SourceTable S

WHERE [Source record not in...

You could have a package variable - ProcName, for example - which is set as a parameter by the stored proc as part of an Execute SQL task. Or you...

What's the error message?

If I ever...

So a sequence number of 8 means the record was INSERTED once and updated seven times (1 + 7 = 8)?

Is the sequence number arbitrary? If so, why not make it an IDENTITY(1,1) field in the destination table and then SQL Server will take care of the numbering for you.

Backup/Restore? What exactly do you mean by "full load"?

After the data is in the intermediate table, can you just fire off seven INSERT commands?

INSERT INTO table1(,,,,,)

SELECT .... from intTable where ProdPrefix = '01'

INSERT INTO table2(,,,,,)

etc etc

Should be faster....

Hi Peter

There are probably several ways of using...

Add a Derived Column transformation to break up column two as you need.

The expression for column 1 will be something like this:

SUBSTRING([PersonName/FatherName/RationCardNo],1,FINDSTRING([PersonName/FatherName/RationCardNo],",",1) - 1)

That will extract all the text from...

As I suggested, you could create a new 'staging' table in your SQL Server database - just a table of text fields wide enough to accommodate your data - and...