Post reply

Import a text file into a column

  • January 10, 2012 at 8:25 am

    #253130

    Hello All,

    I am trying to import a text file which is unstructured.

    5 20120103 recon een7890987 yuioiuy

    0987657890988888790 uiioopppjjnnn 87909877666 89098u8uiopo jkloiuuyhhnnnn jklllloollkkkkkkkkkkkk

    8987675453358900klou u890987665 78765444343 78907655gh7 uiopppppppppp opppppppppppppp

    9 20120103 recon eenjiouyuy jkkllllmm

    I am just tryinmg to import into a single column in a table. That column has varchar(max) as the data type. I have also changed the output width near flat file source as 8000. Package has no errors but when I query the table it only shows

    col 0

    5

    0

    8

    9

    only the first characters of the data. Please assist in this regard. Thanks so much for help.

  • January 10, 2012 at 8:34 am

    #1431210

    Are you running the package interactively (through BIDS)? Are there any Warning messages on the Progress page?

    John

  • January 10, 2012 at 8:35 am

    #1431211

    Please check for column separator. It looks like ‘ ‘(space) character is breaking the rows in multiple columns.

  • January 10, 2012 at 8:37 am

    #1431213

    Dev (1/10/2012)

    Please check for column separator. It looks like ‘ ‘(space) character is breaking the rows in multiple columns.

    Mmmm... that doesn't explain why the second and third rows broke after the first character.

    John

  • January 10, 2012 at 8:42 am

    #1431216

    I am just not sure if the data what we are looking in SSC screen is the same he has in files. Sometimes SSC does magic tricks while showing some characters (special). So I am requesting OP to give it a shot.

  • January 11, 2012 at 7:20 am

    #1431769

    try to change the datatype from varchar(max) to nvarchar(max) and change the source datatype to ntext

    sunilreddy
    http://msbimentalist.wordpress.com 🙂

  • January 11, 2012 at 8:18 am

    #1431822

    sunilreddyat (1/11/2012)

    try to change the datatype from varchar(max) to nvarchar(max) and change the source datatype to ntext

    Is there any particular reason why you think that'll work? I can't check right now, but I don't think there is such a data type as ntext for a text file data source, is there?

    John

  • January 11, 2012 at 3:39 pm

    #1432183

    I Got this. It worked. I just changed the column data type as nvarchar(4000) and gave size as 4000 near flat file source input. Thanks a lot for all your help.

  • January 11, 2012 at 10:41 pm

    #1432261

    you can set 'ntext' data type for flat file connection manager in the 'Advance' tab.

    sunilreddy
    http://msbimentalist.wordpress.com 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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