Solved

Application to read text file periodically and import it to Sql 2008r2 table

Posted on 2014-02-10
5
453 Views
Last Modified: 2016-02-10
I  am having a cobol application in novell s/w.  which periodically exports live data into a text file in mapped drive which can be read by windows os. My job is  to read this text file in the mapped drive and import into sql server in windows os.

I think the same can be achieved thro'  integration services.  Can expert suggest which one of the following is better

               1.  handling it thro integration services
                2.  Writing  a separate vb.net application to achieve the desired result

The application should handle , data transportation errors and any other import errors gracefully and rollback the import in case of any error and thro up errmsg to the user.
0
Comment
Question by:venkataramanaiahsr
  • 2
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 400 total points
ID: 39849304
I think integration services is better option
you can handle data transportation errors  also you can coolect the bad data rows separately in log file and process the correct data only.
You can mail the log file from SSIS package
also you cna schedule the package to run perodicaly
0
 

Author Comment

by:venkataramanaiahsr
ID: 39849816
The output fromcobol application is in .sdf file which  can be opened in in notepad. the delimiter for the various columns is ~ . but when I try to create file connector in ssis, it is taking everything as single column.  what might be the issue
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39849890
you have to use a delimited file and select the delimiter as ~, in the file source.
0
 

Author Comment

by:venkataramanaiahsr
ID: 39851037
In the source file , the columns fields are separated by delimiter as ~ .  still every thing comes in one column when opened in ssis thro file connector.

But if the same file is opened in excel with delimiter as ~ , it comes in different columns
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 100 total points
ID: 39851577
watch this video
http://www.youtube.com/watch?v=1pOlNR-Zqdk

it will have more information regarding how to read a delimited file in SSIS.
Although he is showing about comma delimited, you can change it to ~ with ease.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question