Solved

Create DTS package to import CSV file

Posted on 2008-10-15
4
1,336 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
ID: 22718886
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 85 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 85 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 always on 31 58
SQL Stored Proc - Performance Enhancement 15 54
backup job space check 4 43
Amazon RDS migrate to SQL Server 3 24
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

840 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