Create DTS package to import CSV file

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
allanmarkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
James MurrellProduct SpecialistCommented:
0
 
Anthony PerkinsCommented:
>>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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Mark WillsTopic AdvisorCommented:
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
 
allanmarkAuthor Commented:
Many thanks! Sorry 4 taking so long!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.