Flat File Source Alternatives

[Loomah]

Hi
2 or 3 years ago I created my first SSIS package in SQL2008, upgrading SQL2000 DTS packages. I knew little of ETL at the time (I'm not much better now!) When I was doing this everything fell at the first hurdle - ie importing some data from a csv file. There were numerous errors and when one was 'fixed' another appeared. None of these happened with DTS.

Eventually I found the solution (or at least one that met my needs) in the shape of the Delimited File Source (

https://ssisdfs.codeplex.com/).

I'm now looking at upgrading from 2008 to (probably) 2014 but currently working on 2012. The DFS doesn't convert to 2012 and I can't find an updated version. Although the Flat File Source in SSDT has supposedly been updated it still throws out the same errors I was getting (the data comes from a live sytem which can change historically so a full download is done everyday)

Has anybody come up with a newer version of this DFS task?

The errors I get are to do with truncation, conversion, missing line terminators and the list goes on...

Cheers!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fredericofonseca]

have you considered changing the source code for that tool yourself and make it work with 2012-2014?

for my side of things I normally either load the files with those issues as a raw file to sql server and then do the processing there or I use .net (c# code within SSIS or standalone or powershell).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Loomah]

Frederico
Thanks for the response, changing the source code seems a great idea.

Unfortunately I wouldn't know where to begin!! I'm not actually a developer and my knowledge of SSIS/SSDT is limited. I've looked at the code on CodePlex and fall at the first hurdle - the only language I actually understand is VBA (6!) - certainly not C# and I'm still actually a bit lost with the concepts of .Net. Even if I had that knowledge I would still be struggling with what code I actually need and what I would need to change to make allowances for the later version(s) of SQL.

I'm now at a junction in this process: my remit is to answer the question "Can it be migrated - NO reengineering?" the answer is no in this case. That's the route I have to take but the route I want to follow is the one where I actually understand what you are suggesting!!

To that end how would you load files that I simply can't get into the relevant table? I currently have 20k rows of a 540k row data set falling by the wayside with one error or another (setting the error handling to ignore and redirecting errors to nowhere - this needs a little more thought from me but it gives me the magnitude of the issue).

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fredericofonseca]

Loomah - prepare a small file with max of 100 records - some records with good data and others that cause the issues you have - if you have different type of issues make sure that all are represented on that file.

then send me that file by email to myname at the domain on my signature and I will have a look.

on the email please mention what is the issue with each individual line.
Also supply a create table statement for the table you are loading the data into.

It is possible that in order to get the tool working only the project references need changing to reflect the newer versions of SSIS - that I may be able to do and supply with the modified code which you can then build yourself.

alternatively try and contact the developer of the tool and ask for an upgraded version based on the issues you have and do not appear to be fixed on 2012/2014

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Loomah]

Frederico
I haven't been ignoring your help, I just don't have the time to look properly at the moment.

My role, as previously mentioned, is to answer the question can this be migrated form 2008 to 2012/2014 with minimal effort. The current answer is no but I want to get an answer as, when the bosses realise how many processes rely on the CodePlex DFS, something will need to be done.

I have a couple of issues with sending you sample data (outside the fact I shouldn't really be spending more time on this):-
1. it's hightly confidential and I'm not sure if replacing the actual data with garbage will then cause the same problems
2. this is the embarassing one, I don't really know how to get the full failing record out of the dataflow!! I can' get the errors out but not the records using redirect etc etc!!

Also I have noticed in SSDT that if I go 'Tools>Choose Toolbox Items...' there are no SSIS items shown as there are in BIDS - is this normal? I'm sure I have seen somewhere that SSDT will pick up 2008 components (I think this was on some SQL MVP's site) but I've seen somewhere else that 2008 components don't work in 2012+. Not sure where that leaves me.

Anyway I'll try to work out how to get some dummy data to you if there are no other options. Either way I really appreciate the time you are giving me on this.
Thanks



;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[fredericofonseca]

no bother and I do understand the confidential bit.

as for identifying the invalid records. it is always tricky but one way that helps is to set the properties of the dataflow to a buffer size of 1 row and run it - when it fails it will have processed X number of rows so if you go to that line on the file the erroing row will be around it.

regarding SSDT - items are still here just shown differently. and others can be added in a similar way as that of 2008.
Note though that the tab name is no longer called toolbox but SSIS toolbox.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Loomah]

Frederico
Many thanks for all your help!

I still can't get to the bottom of the mystery of why there isn't a newer version of the Delimited File Reader out there in the wild but hey-ho! Instead I have been loking at an alternative (which I wish I'd thought of 3 years ago!) What I am trying is converting the CSV to TXT, an idea that came to me out of the blue this morning based on the old manual Access process!

So what I have done, if anybody is watching, is add a Script Task before the data flow to convert (crudely) the csv to txt using:

Dim oFSO : oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile("\\Server\Folder\FileName.[b]csv[/b]", "\\Server\Folder\FileName.[b]txt[/b]", True)

Seems to work to me but could I be over simplifying this and overlooking something?

The only issue I have come up against (apart from forgetting to set in input column properties) is the precision of one datetime column seems to change but only the 000s of seconds!

Anyway thanks again for your help! I'm still a bit lost as to how to add/remove tools from the SSIS Toolbox bit that will hopefully work itself out (it doesn't like the original delimited file reader even when the dll is in the correct folder!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​