File without extension

Question

I have to read data from several Log Files (having row delimiter as ‘CR-LF’ and column delimiter as ‘tab’) and load the data into database. I am facing couple of issues which are as follows,
·         The log files do not have any file type\extension and have format as ‘xyz_log.yyyymmdd.abc’. Meaning the file name has many dots (.) in between and it does not have any extension like ‘.txt’, ‘.csv’, or ‘.xls’  to it. Therefore, using File System Task, I renamed the file to ‘xyz_log.yyyymmdd.abc.txt’; however I am not able to read the data from it. 

·         Since, I was not able to perform above task, I manually renamed one of the file to xyz_log.yyyymmdd.abc.txt and using Import Export Wizard, Bulk Load and BCP tried to load the data into db. However, I am getting error as “The column data for column “Header” overflowed the disk I/O buffer”. In the file I do not have a column named as Header so why I am getting this error and how to overcome it?
 
·         The strange part is when I am manually copying the data of this file into another file and calling this new file into SSIS, I am able to load the data without any error. So, is there any way I can automate this to achieve my requirement?
Could you please help?
Thanks in advance!!

Answer 1

Hello 007Sam,

I would change your approach to using the ForEach Loop with which you should be able to skim through the files regardless of the name and load into the database in a DFT.

Example: http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

---

Arthur My Blog

Answer 2

Arthur is correct. And to test even one file, the files dont need to have a proper extension. As long as its a properly formatted file, you should be able to easily import from it no matter what the extension or dots etc. And make sure you have the column names in the first data row option unchecked if there are no headers.

Answer 3

Thanks Arthur for the reply! Do you mean in the For Each Loop, I should fetch only the file name and store that in the variable? Then pass that variable in the DFT i.e. Source as Text File. My question here is when the file does not have any extension then to map that file with the source as text file. Could you please elaborate? I am new to SSIS so not much familiar with it. Thanks again!!

Answer 4

extension doesn't matters. Just change the wildcard in the foreachloop container.

Here is another example of a Foreach Loop Container:
http://microsoft-ssis.blogspot.com/2011/02/how-to-configure-foreach-loop-file.html

You can use the variable from the Foreach Loop Container to change the connection string with an expression.

 

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 5

Yes, and no need to map, it is a pure file name of any kind of file, you are probably coming from the -NUX world but even in Windows the extensions do not matter match.

Besides, SSISJoost gave you a good link.

If you still have issues (first, I suggest, you just start developing your SSIS package now) and get stuck will help you right away, just ask specifics questions for each step.

I trust you should be all set to run your package very soon.

---

Arthur My Blog

Answer 6

Hello,

I have tried like you said however in the flat file connection manager editor, when I am clicking on columns section I am getting error as follows:

"The specified header or data row delimiter "{CR}{LF}" is not found after scanning 65536 bytes of the file "T:\By\log_0101099".
Do you want to continue scanning this file?"

The first few rows in the file are exactly as below:

#Version: 1.0

#Fields: 1 2 3 -------------- 12 (i.e. 12 columns name)

Data1 Data2 -------- Data12 (i.e. 12 data columns having row delimiter as ‘CR-LF’ and column delimiter as ‘tab’)

As I do not need first 2 rows, in the General tab section, I have set header rows to skip = 2. However, when I am going to columns section I am getting above error.

Please note that on an average the file size is 500MB.

 

 

Answer 7

Sounds like your file's format is corrupt or you have chosen the wrong format. If I have problems like this I always try to open it in Excel to look for obvious format errors.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 8

With excel as source it is throwing error as "External table is not in the expected format".

Answer 9

With excel as source it is throwing error as "External table is not in the expected format".

I don't use it as Excel source. I open the application Microsoft Excel to import the textfile in the excel sheet so I can see if the format is correct. It's easy to notice in excel if there are extra columns, no enters, very long values, etc. etc.

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

 

Answer 10

I have tried that before and by naked eye everything looks fine.

As I said before, when I am copying the data to other file and calling that in SSIS it does not throw any of the above error posted. Can we automate this using SSIS?

Answer 11

Hi ,

The above mentioned replies are actually all that you need to do to resolve this issue.

Let me streamline your steps as

1. Create a single file connection and set the settings so as to skip the first 2 rows .

2. Make sure the Row delimiter and Column delimiter is set correctly

3. Preview the data . If the preview works fine without the erros that thats abt it . The connection os properly set.

 If you still get the error then probably check the file if it contains any spurious characters(basically Eof ). You can do this by opening the file in Text pad. It shows the junk characters

4. Set the For Each loop connection as mentioned in the above posts

5. Execute the package.

 

---

Sridhar

Answer 12

Strange! Well let's see if there is a workaround.

Is copying and renaming workable? That can be done by the File System Task.

Or are you copying the data itself in for example notepad?

 

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 13

Copying and renaming is not workable. When I am manually copying data from 1 file to another file it is workable.

Answer 14

Still think there is some wrong that can be fixed, but why not try a workaround with a Script Task with C# code. Something like:

using System.IO;

public void Main() 
{
  string readText = File.ReadAllText(path1);
  File.WriteAllText(path2, readText );
}

Replace the path1 and path2 with variables

---

Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Answer 15

I have made 1 more break through which might be helpful to suggest some workaround.

I am opening the file in textpad and deleting the top 2 rows manually and save it. When I call this file in SSIS I am not getting any error and it is readable.

However, instead of deleting top 2 rows if I do skip top 2 rows it gives error as

"The specified header or data row delimiter "{CR}{LF}" is not found after scanning 65536 bytes of the file "T:\By\log_0101099".
Do you want to continue scanning this file?" 

Note: When I delete top 2 rows and open in SSIS, row delimiter is changed to LF from CR-LF and column delimiter is tab.

Hope there is some workaround for this atleast!!

 

Answer 16

You can use a hex editor to have better understanding of the file structure.

Related link:

Free Hex Editor Neo is award-winning large files optimized freeware editor for everyone who works with ASCII, hex, decimal, float, double and binary data.

Freeware Hex Editor Neo allows you to view, modify, analyze your hexadecimal data and binary files, edit, exchange data with other applications through the clipboard, insert new data and delete existing data, as well as perform other editing actions.

Make patches with just two mouse clicks; manipulate your EXE, DLL, DAT, AVI, MP3, JPG files with unlimited undo/redo. Taste the visual operation history with branching.

This hex and binary code data editing software utility for Windows includes the following basic functionality: Unlimited Undo/Redo; Find; Replace; Visual History Save and Load; Patch Creation; Clipboard Operations; Bytes, Words, Double Words, Quad Words, Floats and Doubles Edit Mode

http://www.hhdsoftware.com/free-hex-editor

 

---

Kalman Toth, Admin/Prog, SSAS, SSIS, SSRS; SQL 2008 GRAND SLAM