Post reply

Applying the same data type across multiple input columns in the data conversion transformation editor

  • January 3, 2008 at 5:52 am

    #119515

    Hope someone can help me with this as I'm sure there's an easier way of doing it!

    I've got a pipe delimited flat file that I'm trying to populate a sql 2005 table from using SSIS.

    Unfortunately, part 1 is there is no header row in the flat file, although I do have the structure in another document, and secondly with it being a flat file, it will not pull through the data types or size of each field.

    As a result I'm having to use a data conversion task. The flat file contains almost a thousand columns of varying data types. I can't see a quick way around applying the data conversions individually, but what would help is if I could select multiple input columns and apply the same data type conversion to all. I haven't found a way of doing this. It only seems to let me set the data type for each input column one at a time! Very laborious when I have almost a thousand input columns! Am I missing some functionality here please?

  • January 16, 2008 at 7:24 am

    #768641

    Well there's disappointing! My first ever post on here and no replies! Sob!

  • January 16, 2008 at 1:06 pm

    #768916

    Well, this reply may be disappointing as well, but have you tried the "Suggest Types..." option in the "Advanced" panel of the connection manager?

    Peter

  • January 16, 2008 at 1:26 pm

    #768931

    Well if the "Suggest Types..." doesn't give you what you want, you can edit multiple columns at once on the Columns panel of the flat file connection manager. Select all the columns you want to give the same type (using the CTRL-key) and then modify the datatype properties. Do this before you create the Flat File Source in your dataflow otherwise the output columns may differ from the external columns.

    Hope this helps.

    Peter

  • January 17, 2008 at 8:27 am

    #769242

    Thanks Peter. Will try both approaches out.

  • January 18, 2008 at 8:48 am

    #769719

    You may be able to copy and paste the file spec and create a script to build a table with the required structure.

    Then upload the file into a single column table (maybe with an identity column too) and substring out the values based on the pipes. Its ugly, but it might save you manually setting 1000 columns, plus you can see what youre writing, rather than having 1000 invisible boxes to deal with inside an ssis task.

  • January 18, 2008 at 9:13 am

    #769748

    Stuff like this I'd import the entire thing into varchar columns in a staging table. then you can process it inside SQL, looking for the correct datatypes. (IsNumeric, etc.)

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 7 posts - 1 through 6 (of 6 total)

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