File contents and String functions in SSIS not working as expected

Question

Post reply

File contents and String functions in SSIS not working as expected

hamsa_apj

SSC Eights!

Points: 825
More actions
June 7, 2012 at 11:36 pm

#275640

Hi All,
Iam very new to SSIS and hope any one will help me to resolve the below issue.
In my SSIS Package (2005 version), .dat files are read from the directory using the "File connection" connection manager.
File Connection Properties
Input: .dat file
Row delimiter :CR-LF
output: DATA_STR (DATA data type)
output column width : 7000
Sample contents in .dat file:
Contents in the .dat file are
1234.56.7678.777778899999999 1 XXXXXXXX1003 YYY SAMPLE00400511112924543187NEW CCCCCCC9778 CCCCCCC9778 DC
Then using "Split columns" Data flow component we split the data from file and map to the database columns as below
Derived Column name Expression Data Type
transSequenceLTRIM(SUBSTRING(DATA_STR, 1,34) string[DT_STR]
transNum LTRIM(SUBSTRING(DATA_STR, 35,8) string[DT_STR]
partIdLTRIM(SUBSTRING(DATA_STR, 108,30) string[DT_STR]
itemIdLTRIM(SUBSTRING(DATA_STR, 132,30) string[DT_STR]
part DescLTRIM(SUBSTRING(DATA_STR, 162,4) string[DT_STR]
With this derived column , database columns are mapped,In tht case,below values got mapped.
transSequence (nvarchar(34)) = " 1234.56.7678.777778899999999"
transNum (nvarchar(8)) = " 1"
partId (nvarchar(30)) = " CCCCCCC 977"
itemId (nvarchar(30)) = ""
part Desc (nvarchar(4)) = ""
when i try to execute the SSIS package with same .dat file once again / multiple times, values ara mapped to database columns as expected. It shown below
transSequence (nvarchar(34)) = "1234.56.7678.777778899999999"
transNum (nvarchar(8)) = "1"
partId (nvarchar(30)) = "CCCCCCC9778"
itemId (nvarchar(30)) = "CCCCCCC9778"
part Desc (nvarchar(4)) = "DC"
Am not sure why the file contents are not read properly and String functions(LTRIM and SUBSTRING) are worked as expected.
Inconsistently am facing the above issue.Am not able to predict the root cause of the issue. And also no clue to debug the issue.
Please do needful. Thanks in Advance.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

The expressions should work, however don't seem to use the correct positions.

LTRIM(SUBSTRING(DATA_STR, 108,30)

This one will take data from position 108 till 138

LTRIM(SUBSTRING(DATA_STR, 132,30)

Thhis one will take data from position 132 till 162, so there is some overlap with the previous one.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

hamsa_apj SSC Eights! Points: 825 More actions · Answer 2

Thanks for your response. .If the string positions are wrong.. It should face this issue always.But am facing this issue inconsistently.. Please advise.