Solved

Create DTS package to import CSV file

Posted on 2008-10-15
4
1,325 Views
Last Modified: 2013-11-30
Greetings all

I need to create a DTS package to run on an Sql2000 server. The package must import a CSV file (BranchId, Year, Month, Amount) into a table. I need to check that the CSV data is valid, and write out a new CSV file of errors.

I have never done this before.

Any thoughts?


In advance, thanks!!!
   allanmark
0
Comment
Question by:allanmark
4 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 80 total points
Comment Utility
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 85 total points
Comment Utility
>>I need to check that the CSV data is valid<<
Define valid.

In the meantime this should get you started:
Importing A Text File Using The DTS Wizard
http://www.sqldts.com/276.aspx
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 85 total points
Comment Utility
Definitely use the import export wizard as a template then at the end save as a DTS package so you can save it and use it later...

or

can also go with a script which you can run and play with - something like :

select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\PathName;','select * from MyCSVFile.CSV')

if that displays OK, then you are more than half way there - you could use the "openrowset" part just like a regular table, meaning you can start playing with individual columns and rows. In your dts package that could be a simple SQL step before you do the export...

Best to load it into a staging table which is just a temp table so you can further manipulate and update with error status / messages and then export that as a csv...


0
 

Author Closing Comment

by:allanmark
Comment Utility
Many thanks! Sorry 4 taking so long!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now