Import file characters into a various tables

  • Hello,

    I have a CSV file with 6 reoccuring rows. The rows are not delimted.

    The structure of the file is as follows:

    APP1XH6JXY3SBC201509010920152015090109201505127651

    APP2XH6JXY3SBCLNN0 TSME R024MZZZN05127651

    APP4XH6JXY3SBCDC SOLUTIONS LTD MEGA HOUSE 103 DARWIN ROAD

    APP5XH6JXY3SBCI20150901092015INTERNETRR35 V20150901092021INTERNETRR35 I201509010

    APP6XH6JXY3SBC000INTERNET1604INTERNET1604000100010001000100010001000100010001000120150901092015

    Each row will be imported into a seperate tables. So tb_APP1, tb_APP2, tb_APP3, tb_APP5, tb_APP6, tb_APP9.

    For APP1 my table design is as follows:

    RecordID, ApplicationID, Input_Date, Input_Time, Amend_Date, Amend_Time

    I would like the script to work as follows.

    1. For APP1 i would like the script to work as follows: Count the first 4 characters then insert into a APP1 table.

    2. Count the next 10 characters then insert into table APP1.

    3. Count the next 6 characters then insert into APP1.

    4. Count the next 6 characters then insert APP1.

    5. Count the next 6 characters then insert APP1

    After the import has finished for APP1, i would like the task to start on APP2.

    So a count will start to import the first four characters then the next 10 characters.

    Please can you explain which data flow transformation i could use to perform this.

    Regards

  • I'd probably go with a Conditional split transformation which will have subsequent derived column transformations and destinations for each table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I'd be tempted to use an asynchronous Script Component Transformation with five (or however many tables you have) outputs.

    --Edit: changed 'asymmetric' to 'asynchronous', oops 🙂

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hi Can anyone point me to some examples of these solutions?

  • Try this: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-conditional-split/

    Do you need more guidance?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • dbman (10/22/2015)


    Hi Can anyone point me to some examples of these solutions?

    Have a look here for the general principle.

    Your outputs would, of course, each contain different columns, depending on the target table.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hello,

    I wanted to test with the first column. Tested, but all the rows seem to be importing into Record_ID.

    In my package, my external column name is Autoscore. My Output Column is also called Autoscore.

    In my conditional split my output name is: Record_ID. The condition i have wrote is: SUBSTRING(Autoscore,1,4) == Autoscore.

    The first column in the table is called Record_ID. Where am i going wrong?

    Thanks

  • Anyone ideas on this solution?

    I am trying to import the following row from a CSV file into 5 seperate columns.

    APP1XH6JXY3SBC201509010920152015090109201505127651

    My Flat File Connection Manager Editor Column is called AuoScore and looks like this:

    APP1XH6JXY3SBC201509010920152015090109201505127651

    Currently have a flat file source with External column: Autoscore. My output column is Autoscore.

    My conditional split is as follows:

    Output Name:

    Record_ID SUBSTRING(AutoScore,1,4) == AutoScore

    Application_ID SUBSTRING(AutoScore,5,10) == AutoScore

    Input_Date SUBSTRING(AutoScore,8,11) == AutoScore

    Input_Time SUBSTRING(AutoScore,6,12) == AutoScore

    Amend_Time SUBSTRING(AutoScore,8,13) == AutoScore

    After that task i now have a Derived Column

    Derived Column Name: Expression

    Record_ID SUBSTRING(AutoScore,1,4)

    Application_ID SUBSTRING(AutoScore,5,10)

    Input_Date SUBSTRING(AutoScore,8,11)

    Input_Time SUBSTRING(AutoScore,6,12)

    Amend_Time SUBSTRING(AutoScore,8,13)

    I have ran the task and it imports fine, however the data: APP1XH6JXY3SBC201509010920152015090109201505127651

    is being imported directly into only the Record_ID column in the database table.

  • Check the mappings on your OLEDB destination.

    Each of the derived columns should map to its target column.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Hello,

    I have actually got the data importing into APP1 table, however im struggling to see how APP2 and other APP lines can be split to begin a new import into an APP2 table.

    My conditional Split has only one column named Autoscore.

    Regards

  • Anyone any ideas on this solution?

  • dbman (10/29/2015)


    Hello,

    I have actually got the data importing into APP1 table, however im struggling to see how APP2 and other APP lines can be split to begin a new import into an APP2 table.

    My conditional Split has only one column named Autoscore.

    Regards

    Your conditional split should have 6 outputs each with a single column.

    For each of those outputs, you need to create a Derived Column transformation to divide the single column into the needed columns depending on the row by using substring.

    You'll have one output per derived column with N columns depending on the definition on the row. Those outputs will go into a "destination".

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • My file is not delimted. When i go to Conditional Split Transformation Editor i under the columns folder is: Autoscore (which is the file).

    I have 5 Outputs which relates to APP1 row. These are:

    RecordID, Application_ID, Input_Date, Input_Time and Amend_Date

    Each with a substring:

    SUBSTRING(AutoScore,1,4) == AutoScore

    SUBSTRING(AutoScore,5,10) == AutoScore

    SUBSTRING(AutoScore,15,8) == AutoScore

    SUBSTRING(AutoScore,6,12) == AutoScore

    SUBSTRING(AutoScore,8,13) == AutoScore

    How can i begin to import APP2 Row?

  • APP1 data should be directed to the APP1 output, and so on for APP2 ...

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • SUBSTRING(AutoScore,1,4) == "APP1" --> APP1 output

    SUBSTRING(AutoScore,1,4) == "APP2" --> APP2 output

    ...................

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply