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 🙂
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.
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 22, 2015 at 3:09 pm
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.
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.
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.
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 ...
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.
October 29, 2015 at 8:14 am
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