SSIS - Import from Flat File

  • Hi Everyone.

    Is there anyone who knows how to import a Single data type from a flat file, into a table in SQL server using SSIS? The single data type, is a 32 bit floating point value, which ports to the Real data type in SQL

    I have tried using the 4 byte singned/unsigned data type mapping, but this does not work.

    I am a newbie as far as SSIS is concerned but it seems to be a very poerful utility, and I'm sure that it should be able to handle this.

    Many Thanks

    Charles

    🙂

  • You cannot change datatypes just via mapping. You will need to use a script component or a SQL Transform or component where you can explicitly apply one of the conversion functions.

    Alternatively, if you can get it into a package variable, you can use package expressions to convert it.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • maybe there is a problem with "." and "," - last week i was geting some strange errors during import a numeric data.

    my column has dot instead of comma ;/// my solution was to import this as string and then replace "." with "," and then convert to numeric.

  • You want to use the Data Conversion task.

  • Hi Barry

    Thanks for your comment. I appreciate it

    I will look into SQL Transform.

    Regards

    Charles

  • Hi Jack

    Thanks for the information. I appreciate your trouble.

    Where can I find more information regardding this.

    Regards.

    Charles

  • Look up the Data Conversion Task in SQL Server Books on Line (BOL).

  • I think Jack has the thing (Data Conversion Task) that I was trying to remember. You can always do it in scripts also, put that's probably the harder way to go (slower too, I'd imagine).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I'm busy investigating this option at the moment, but as with most things, it's never as simple as it sounds. Perhaps it's just my lack of experience with this type of thing. At least when I get it right I'll have learnt something. This is an amazing oppertunity for this technology. The data from an entire DOS application system, to be ported into SQL. The records are all fixed width, with no CR/LF characters. The problem is not with the character based data, but rather with the numeric data,(2, 4 and 8 byte values), which to for the most part have been embedded inside character strings.

    My research thus far, has led me towards the "Business Intelligence Development Studio", which seems to be exactly the same as my .NET IDE, but I'm missing a "Intergration Services" template, which I am trying to locate.

    What fun !!!

  • Business Intelligence Development Studio, usually referred to as BIDS, is a slimmed down version of Visual Studio that comes with the SQL Server install (client components) which is used for SSIS, SSRS, and SSAS development. If you already have VS.NET installed it just adds the Business Intelligence project templates to your install. If you don't have the Business Intelligence Projects project type in VS then you need to run the SQL Server install, just choose client components.

  • I assume then that you have been using the Import/Export wizard up until now?

    Depending on how comfortable you are with T-SQL vs. SSIS, you might be be better off using SSIS to dump the import data into a Staging table and then working with that table in SQL to convert/transform to data into the target table. If the source data is a 4-byte floating point, then you should be able to just map it to a Real in the staging table.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Hi There

    Thanks for the advice, I will certainly give it a bash. I have been using the import tool, and it seems like such a powerfull and useful tool to import many different types of data. It's just a pity that it does not work in this particular instance.

Viewing 12 posts - 1 through 11 (of 11 total)

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