Auto assign Column Data Types for Flat file Source Import

  • Daniel Taylor-446457


    Points: 459

    Comments posted to this topic are about the item Auto assign Column Data Types for Flat file Source Import

  • SQL Surfer '66


    Points: 5159

    As brilliant as simple. Thank you for sharing.

  • Phil Parkin

    SSC Guru

    Points: 244142

    Simple, effective and well written. Good stuff.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • m_swetz

    SSC Eights!

    Points: 989

    This is one of those great solutions that is both simple and innovative. You know you have something brilliant when you see it and say how did I not think of that.

  • jrobinson 7382

    SSC Journeyman

    Points: 78

    Thank you, Daniel.  Very well written and an excellent solution.

  • latkinson

    Ten Centuries

    Points: 1244

    Great article and something I never thought about doing. I think you might have shaved about 20 minutes off my average time to create a source from scratch.
    Thank you.

  • Thom A

    SSC Guru

    Points: 98564

    Huh, and all this time I've been using Derived Column Transformations, or relying on SQL Server to do the implicit conversion. Nice find, and well explained. 🙂


    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • jbowers

    Old Hand

    Points: 365

    This is brilliant in its simplicity. No matter how much I learn about SSIS, it's always a pleasure to run across a gem like this. Thanks for sharing!

  • m.katrobos

    SSC Veteran

    Points: 296

    Thank you for this tip.  It will save me hours every month.

  • GSquared

    SSC Guru

    Points: 260824

    Since SSIS packages are just XML files, you can also generate an XML definition for the source from the metadata of the destination table, and either dynamically or with just cut-and-paste in Notepad, you can create a data source pretty easily that way.  I've done that before.  Even automated it for a solution that had to import hundreds of dynamic file formats.

    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ArunJindal


    Points: 1

    Very effective. Well Explained.

    Could you please help me with one step further, my requirement is as follows:

    The columns of source table have datatype as CHAR (DT_STR), so the source table EXTERNAL column datatype value is DT_STR. The requirement is to read the data from the source table as Unicode characters, so we need to manually change the OUTPUT COLUMN datatype values for all the columns as DT_WSTR (Unicode). Just for information, the target tables have the dataype as DT_WSTR (Unicode). Please advise if there is any easy way to do this?

  • fooangel


    Points: 1

    Life Saver! Question: I create a master package with a variable value that determines source file names, path, target, etc. Is there a way that the initial dest to csv for auto datatype mapping can remain in the package, execute, and that the flow continues to the next piece? Meaning, can these two separate flows exist and execute consecutively or, do you have an easy way of calling another package?

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

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