October 22, 2015 at 9:47 am
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
October 22, 2015 at 9:52 am
I'd probably go with a Conditional split transformation which will have subsequent derived column transformations and destinations for each table.
October 22, 2015 at 9:57 am
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 🙂
October 22, 2015 at 2:49 pm
Hi Can anyone point me to some examples of these solutions?
October 22, 2015 at 2:53 pm
Try this: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-conditional-split/
Do you need more guidance?
October 23, 2015 at 8:25 am
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
October 26, 2015 at 4:49 am
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.
October 26, 2015 at 4:59 am
Check the mappings on your OLEDB destination.
Each of the derived columns should map to its target column.
October 29, 2015 at 4:28 am
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
October 29, 2015 at 7:31 am
Anyone any ideas on this solution?
October 29, 2015 at 7:41 am
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".
October 29, 2015 at 7:57 am
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?
October 29, 2015 at 8:11 am
APP1 data should be directed to the APP1 output, and so on for APP2 ...
October 29, 2015 at 8:14 am
SUBSTRING(AutoScore,1,4) == "APP1" --> APP1 output
SUBSTRING(AutoScore,1,4) == "APP2" --> APP2 output
...................
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply