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
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.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy