question

LadyProgrammer avatar image
LadyProgrammer asked

How to ETL a file whose extension is not like txt or csv?

I have four files: abcd.sum, pqrs.dif, mnop.mul, jklq.div. I am trying to read these file using a flat file connection to do ETL in SSIS tables, but the operation gives me error File "G:\abcd.sum" could not be opened. The file is present in the G drive. The user also has full access to the G drive and its contents. Initially when I tried to open the file, due to its extension, Windows had asked me to open file, and I opened it through Notepad. ![alt text][1] Now since the user that is running the SSIS package is a service account, is that the reason why I am getting such an error? If yes, could anyone guide me as to how to ETL a file with such extensions. [1]: /storage/temp/2461-windows.png
etlssis-2008flat-file
windows.png (13.9 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

did you try import some of these files manualy with SQL Import Wizard directly into database? Do you get any error? I tried on my PC and it is working. That means flat file data source recognize this extensions so you probably have problem with permissions. also, you need to close file in notepad when you do import. If you have file open, it is locked.
0 Likes 0 ·
LadyProgrammer avatar image
LadyProgrammer answered
The process for ETL for files is the SAME for any extension. I tried replicating it and there is nothing special that needs to be done. It was issue with the path. I tried for D drive and it worked fine.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Alvin Ramard avatar image
Alvin Ramard answered
The process for ETL for files is the same for any extension, but you have to know the file layout before you start. The so called error you're getting has nothing to do with the user. You're getting that because Windows does not know what to do with a file with a ".sum" extension.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.