Solved

Create DTS package to import CSV file

Posted on 2008-10-15
4
1,339 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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