Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 SSIS Bulk Import

Author  Topic 

gabivas
Starting Member

10 Posts

Posted - 2014-05-13 : 13:07:15
Hello everyone - I was wondering if I can get some feedback regarding a project.

Basically, we will need to bulk load data every night - few throusands records, no space or any other delimiter in the file. Data comes as a test file. We have a definition table wich specifies the start, lenght of each field that we need.

So no problem here - I can generate the format file using bcp and the table with definitions.

The issue I have is when inserting this data into a final table - each chunk of data must have an ID; I cannot find a way to link the data content with the correct ID to insert into a final table.

Maybe an example will help.

My definition table:

VarID Name Desc Start Lenght
1 AdmitDate Admit Date 110 6

In the file, the Admit Date will be somewhere like this:

xxxxxx01012014xxxxx - where xxx may be some other data.

So I am able to extract the data under a staging table using bcp in this format:

AdmitDate
01012014

But I will need to insert into production using this format

RecordID VarID VarDesc VarValue
AutoGen 1 AdmitDate 01012014



How can I do that?

Thank you for your advice.

AV
   

- Advertisement -