SSIS Transformation for flat file

Question

This is my Table in SQL Server:

Country

varchar(50) varchar(50) Int

and i have a flat file data

------------Flat file----------

Output:

Can you please suggest me the process, how to perform this task and what all tranformation should i use here

please help me out here

Welcome to Stack Overflow. i'm afraid your post could do with some improving though. Firstly, you've tagged 2008 and 2012 here, what are you using? Also, please don't post data as images; post it as text. Finally, what have you tried so far, and what is your question? Stack Overflow is a Q&A website for **specific** problems/questions; it's not a suggestion website or a free "do your work" service. Provide details of what you've tried, what problems you've encountered and any relevant errors, and then make sure you ask a specific question for your needs. Thanks. — Thom A, Jun 19 '18 at 11:54
The file and the output don't fit together, Either a file is comma separated or not and if it is, there is no way to transform "india, 5" to "india, null, 5" as stated in the output. Furthermore, considering your example: how should "test4" be handled? As country? As city? — Tyron78, Jun 19 '18 at 12:16
Hello Larnu :: m using SQL server 2012 and SQL Server Data tool for Visual studio 2012 , — Bright Junis, Jun 19 '18 at 12:48
Hello @BrightJunis . This will NOT be possible, except if you define Tables with all valid Countries, Cities, etc. ... How should any kind of transformation be able to distinguish "test4" from any country? Or is there any logic I am missing, like "If I have 1 value it's always city; If I have 2 Values it's always country and number; If I have 3 values it's always country, city and Number;..."? — Tyron78, Jun 19 '18 at 12:53
Hello @Tyron, actually i was asked this question in a interview, i was not able to solve this issue, so i thought of posting in Stackoverflow, just to get suggestions. Even i was not sure about this kind of scnario — Bright Junis, Jun 19 '18 at 12:57
@BrightJunis then I guess something as the following might be a valid answer: "The task at hand is only solveable if either a distinct file structure is defined [see previous comment] or if mapping tables are defined which allow an unambiguous identification of countries, cities and so on. However, the number might be automatically extracted in case that ALL parts of a string (comma separated), which are identified as numeric, can be handles a number." — Tyron78, Jun 19 '18 at 13:13

score 0 · Answer 1 · answered Jun 19 '18 at 13:27

0

Since this was an interview question, I'm sure a broad strategy is all that is needed.

You can solve this by making the Flat File a single-column output, and passing it to a script transformation that counts the commas, analyzes the row, and splits it into three output columns, providing NULL as needed. Then you simply pass the 3-column output to the destination table.

Finally I would tell the interview that this solution is dependent on the business being able to provide useable business rules for defining what data goes in what column when a row is missing columns.

answered Jun 19 '18 at 13:27

Tab Alleman

31,483
7
36
52

Unfortunately, this will NOT work: as described by the OP, the first column can be country or city, the file can have 1, 2 or 3 columns, the combination country + number is valid (city is not provided) - so you would need "country, NULL, number" which would NOT be possible if considering the commas only... – Tyron78 Jun 19 '18 at 13:52
@Tyron78 As I said, I would tell the interviewer it would be incumbent on the business to provide rules for identifying whether data is a city, country or number in cases where not all columns are present. This is an interview question, and not a real problem needing to be solved. – Tab Alleman Jun 19 '18 at 14:20
hey its ok guys, just chill :) many thanks for your all help – Bright Junis Jun 19 '18 at 14:55

SSIS Transformation for flat file

1 Answers1