November 16, 2015 at 8:14 am
Hello,
I have developed an SSIS package whereby a file is split into a number of tables. I have a conditional split, derived column and data conversion task.
The file is structured as follows:
APP4VK5KBN2KLRSMITHLY CONSTRUCTION LIMITED INTERNATIONAL HOUSE BIRMINGHAM INTERNATIONAL BUSINE SOUTHAMPTON
I have a derived column for each field and use substring to seperate each field in the database import. Thus
Record_ID4 SUBSTRING(AutoScore,1,4)
Application_ID SUBSTRING(AutoScore,5,10)
Organisation_Name SUBSTRING(AutoScore,15,40)
Organisation_Address_Line1 SUBSTRING(AutoScore,55,40)
Organisation_Address_Line2 SUBSTRING(AutoScore,95,40)
Organisation_Address_Line3 SUBSTRING(AutoScore,135,40)
When i start my import the field Record_ID, Application_ID, Organisation_Name all import into my tables with no problem. However as soon as there is a space after Organisation_Name the data does not import. The table column for Organisation_Address_Line1, Organisation_Address_Line2, Organisation_Address_Line3 are just blank.
Is there anything i can do to assist in the data importing into the table in my task? I am not sure what else to do apart from using SUBSTRING.
Regards
November 16, 2015 at 8:49 am
dbman (11/16/2015)
Hello,I have developed an SSIS package whereby a file is split into a number of tables. I have a conditional split, derived column and data conversion task.
The file is structured as follows:
APP4VK5KBN2KLRSMITHLY CONSTRUCTION LIMITED INTERNATIONAL HOUSE BIRMINGHAM INTERNATIONAL BUSINE SOUTHAMPTON
I have a derived column for each field and use substring to seperate each field in the database import. Thus
Record_ID4 SUBSTRING(AutoScore,1,4)
Application_ID SUBSTRING(AutoScore,5,10)
Organisation_Name SUBSTRING(AutoScore,15,40)
Organisation_Address_Line1 SUBSTRING(AutoScore,55,40)
Organisation_Address_Line2 SUBSTRING(AutoScore,95,40)
Organisation_Address_Line3 SUBSTRING(AutoScore,135,40)
When i start my import the field Record_ID, Application_ID, Organisation_Name all import into my tables with no problem. However as soon as there is a space after Organisation_Name the data does not import. The table column for Organisation_Address_Line1, Organisation_Address_Line2, Organisation_Address_Line3 are just blank.
Is there anything i can do to assist in the data importing into the table in my task? I am not sure what else to do apart from using SUBSTRING.
Regards
Please check that the length of the AutoScore column is not the default length (50). If it is, increase it 🙂
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.
November 16, 2015 at 9:08 am
Hello,
Thanks for reply. I have tried that previously on all columns and its still not importing.
Regards
November 16, 2015 at 9:16 am
dbman (11/16/2015)
Hello,Thanks for reply. I have tried that previously on all columns and its still not importing.
Regards
Not all columns. I am talking specifically about the single column in your source file which you are splitting. Have you checked that it is working OK using a data viewer? This will be defined in your source file connection manager. Check Properties / Advanced / OutputColumnWidth.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply