?
Solved

Create DTS package to import CSV file

Posted on 2008-10-15
4
Medium Priority
?
1,344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 320 total points
ID: 22718886
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 340 total points
ID: 22727261
>>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 340 total points
ID: 22788195
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
ID: 31506222
Many thanks! Sorry 4 taking so long!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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