Auto assign Column Data Types for Flat file Source Import

Question

Post reply

Auto assign Column Data Types for Flat file Source Import

Daniel Taylor-446457

Mr or Mrs. 500

Points: 502
More actions
May 13, 2018 at 6:58 pm

#338043

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

Viewing 15 posts - 1 through 14 (of 14 total)

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

SQL Surfer '66 SSCertifiable Points: 5161 More actions · Answer 1

SQL Surfer '66

SSCertifiable

Points: 5161

May 14, 2018 at 3:11 am

#1990320

As brilliant as simple. Thank you for sharing.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Simple, effective and well written. Good stuff.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

m_swetz Ten Centuries Points: 1069 More actions · Answer 3

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: 82 More actions · Answer 4

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

latkinson SSCommitted Points: 1674 More actions · Answer 5

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: 99130 More actions · Answer 6

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. 🙂

Thom~

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

jbowers Old Hand Points: 369 More actions · Answer 7

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 More actions · Answer 8

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

GSquared SSC Guru Points: 260824 More actions · Answer 9

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.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

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

ArunJindal Newbie Points: 1 More actions · Answer 10

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 Newbie Points: 1 More actions · Answer 11

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?

rferreira.dba Old Hand Points: 301 More actions · Answer 12

I am sure I am missing something here, especially with the comments raving this as a great solution. So, pardon my stupidity: What is the advantage here, if you have to actually create the table manually first in the database? I mean, especially when you have not 7, but 300 columns, you would have to manually (dare I say painstakingly) create the "CREATE TABLE ... all 300 columns and their datatype definitions..." statement first to create the table! As I read it, when you say "and the CREATE TABLE script was provided for the destination", I am assuming that the create table script is a basic one that created the table in the database with column definitions of char(50)? And therein lies your issue? But, if that is the case, and if you have to create a brand new CREATE TABLE script, with all the 300 column definitions, isn't that the same as editing the original script(s) provided with the proper column datatype definitions needed?

Again, I am sure I am missing something here. Please clarify where I am being stupid. Thank you in advance, Raphael.

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 13

I agree that this is a great solution and some fine "out of the box" thinking for SSIS.

My problem with it all though is, you already know what the definition of the data table is. You also already know that the file matches the definition of the destination table.

Why not just use BULK INSERT and call it done? Why does it have to be done in SSIS?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rferreira.dba Old Hand Points: 301 More actions · Answer 14

I do see what you mean now... One can use "Suggest Column Types" in the Flat File Connection Manager, but, trusting the results... Yes, I can now say I see the interesting idea behind your process. It's one of those things that SSIS should allow you to, once connected to the destination table, click on a button reading "Refresh types per target", and be done with it. But it seems Microsoft has abandoned SSIS for good and is now placing all their bets on Azure Data Factory... (Not sure I am buying that idea yet though (ADT)... Maybe, once I can fully test and try it in the real word.)

Yes. Indeed. Elegant solution. Thanks! R.