White Space delimited file
-
Hello everybody,
It's my first time in this forum, so I hope that I'll be a helpful member as much as I can. However, at the moment I'm stuck with the following problem:
Im using a flat file with white space delimited columns, but ssis doesnt give 'white space' among column delimiters. So, can any body help?
just another bit, the file im using is an IIS log file and consists of rows starts with # such as
#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2006-11-29 01:13:12
#Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
how can i filter such rows?
thanks heaps in advance,
momo
-
Just did a quick play around with SSIS.
In the Flat File Connection Manager, skipped the first 4 rows as Header rows.
Under Columns (select from left-hand side), set the Column Delimiter to a single space (i.e. select that drop-down box & hit the space bar).
I set all the input columns from the file (copy of one of our IIS logs) as DT_STR with length of 100 except for the last field (sc_win32_status) which was length 200.
Then I ran it through a Conditional Split with the condition SUBSTRING(eventdate,1,1) == "#"
I created a table in SQL with each field as above (varchar(100) except for sc_win32_status).
Connected the Conditional Split Default Output to the input of a SQL Server destination and ran it.
Quick & dirty and the table can certainly be optimised a LOT (for e.g. the last three fields could be int datatype for a start, and the first two fields could be merged into a single datetime column). Have a play around. Best way to learn.
Scott DuncanMARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare -
Given that you are working with an IIS server (Microsoft Internet Information Services 6.0) what are the chances that you can save yourself some pain and configure IIS to log directly to SQl Server?
Joe
-
This may be an even quicker fix.
Set the column delimiter to the normal (default) comma.
Run the package.
Edit the flat file to replace all the commas to a single-space (" ")
Edit the Flat File Connection manager, changing the column delimiter from the comma to a single-space.
Press the reset columns button.
Save the Flat file connection.
It'd be better if SSIS gave a space delimited column option. Are you listening Microsoft?
-
I found if you want to get rid of white space at the end of a column when you have variable length, then put in a Derived Column transformation and use the TRIM command.
e.g. TRIM(Code1)
The use this column to append to your flat file.
So if you have rows like this (There is two spaces after the 1751 and 1000 if you look carefully!)
[font="System"]A,2016-04-06,1751 ,1000 ,A,.00
and you want this
A,2016-04-06,1751,1000,A,.00[/font]
do the above.
-
That doesn't sound fun but then again if it were you wouldn't be posting it here. 😛
Onward.
I've dealt with similar source files. An ugly but effective way to handle them is to import each row as a single field then use the Derived Column transformation to split out your fields.
Depending on the complexity of your logic to split the data it might make sense to dump it into a staging table then use T-SQL to handle chopping up the data into the destination.
-
Try out what Scott has suggested. I have not tried it myself, but his theory seems good. I've had to deal with some pretty crazy files over the years. If things get too crazy, I usually resort to adding a Script Task and using C# to clean up the file so SSIS can easily read it in. I do that now for a file that is around 80 MB and it only takes a couple of minutes to read it, apply rules, and create a SSIS friendly file.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply