Import SSIS file with columns at different positions each day

Asked

Viewed 399 times

-1

I have the following situation: I make a Flat File (Simple File) import from a *.CSV file to a table in the database. Using SSIS in Visual Studio. However, each day the file I receive has the order of the columns changed. Thus, the "email" field, for example, may end up receiving information from the "phone". How do I make sure that regardless of the order that comes, the import identifies the correct columns?

Exemplo: 
Ordem das colunas no Dia 01: Email, Nome, Telefone
Ordem das colunas no Dia 02: Telefone, Email, Nome
sql sql-server visual-studio visual-studio-2017 ssis

  • The text file (CSV format) has initial line with the headers that identify the contents of each column?

  • Yes. The first line is the header.

1 answer

0

If column order changes, SSIS maintains the structure of the information associated with that column. Check in the advanced Flat File Source options

Example Day 01:

Col1, Col2, Col3 A B C AA BB CC

Example Day 02:

Col2, Col1, Col3 B A C BB AA CC

Then you need to dynamically map the columns through a Script Task because SSIS does not have anything predefined for this dynamic mapping.

  • What do you mean by "dynamically mapping"? Could I be a little more lay? I’m starting with the tool.

  • I did a test playing from a *.CSV to a *.TXT. The text file receives the contents of the column of the same name read in CSV. Then I understood that the problem is in the source flat file. Looking at advanced options, there is not much, I did not find something that suggests "dynamic column". Exemplo do campo 'Nome': Delimitador de Coluna é ';' e tipo é cadeia de caracteres [DT_WSTR] . Opção TextQualified=True. ColumnType=Delimitado. What particular option should I look for? @Tiagomeireles

  • @Catharinaoliveira what he meant by dynamic mapping was, through a task script, to develop a script that places the values of the column that says "Name" in the destination column "Name" and so on. So, regardless of the order of the columns, you can always have the correct information to be put in the right place. That is, you put a . CSV as Source and a . TXT as right Destination? And regardless of the order, the information appears correct? Name information is sent to the Name column, regardless of the order?

  • Hi @Tiagomeireles. I understand, but for this he has to at least read correctly. And it is not correct. If I change the place column, its content goes to the wrong place. It doesn’t follow that example of yours above.

  • @Catharinaoliveira Hello, I get it. There is a plugin for SSIS that is Cozyroc Data Flow Task Plus (It’s free!) that supports mapping that I mentioned of dynamic columns. If you cannot with this plugin you will have to use a task script :\

  • Aaa is right James. Thank you very much!! I will test. Hug!

Show 1 more comment

Browser other questions tagged sql sql-server visual-studio visual-studio-2017 ssis

You are not signed in. Login or sign up in order to post.