Bulk Insert Issue 1

[FaStOnE]

I need to do a Bulk Insert of a comma delimited file into a predefined table. Not a problem under normal situations... but...

I have a Generic table with 500 columns defined:
( Col001, Col002, Col003 ... Col500 )

The ASCII data I need to pull from a comma delimited file will have uneven columns though!

Row1 has 216 columns
Row2 has 265 columns
...
Row9 has 350 columns

The fact is, I have no idea how many columns I will be pulling into the generic table. Is there a way to force the Bulk Insert to populate the table columns up to whatever number of columns are being pulled?

Each row does have the '\n' row terminator.

Thanks for your help!!

Rick
(Men are from Earth, Women are from Earth. DEAL with it!)

 

[fluteplr]

Have you tried doing the insert with DTS? (Data transformation services) It is pretty good at dealing with comma delimitef files.

 

[FaStOnE]

Yes.. I have imported the data into the Table via the DTS package and that actually works. It's the BULK INSERT that craps out with truncation errors. Unfortunately, I need to automate this ( call it as a Stored Procedure ) from a web based Intranet page.

Rick

 

[fluteplr]

You can call a DTS package from a stored proc, it is a bit tricky though.

The method I use is to create a job that will run the sql agent job that will run the dts package and the run the job using the following. Assuming the job is called 'Import Job1'

CREATE PROCEDURE dbo.runImportJob1 AS

exec msdb.dbo.sp_start_job @job_name = 'Import Job1'

There are some security issues with this that you will have to deal with. there are other methods there was a thead about this a few months ago I think.

Hopefully in the next version MS will just give use an sp_execdts stored proc.

 

[FaStOnE]

OK, please pardon the ignorance here.

How are you implementing(Creating) the Job(ImportJob1) to run the DTS package?

I guess I'm missing a step here that's not clicking. Sorry!

I can create the Procedure that is called to start the job, but the link from there to the actual job is confusing me.

 

[fluteplr]

Sorry should have explained that.

In enterprise manager go to dts packages, right click on the package name and select schedule package.

Schedule it for next week or something. This will create a job for sql agent. You can then go to management, sql agen,t jobs and change that job.

At this point delete the schedule.

You will then have a job with no schedule that you and execute on request.

 

[tlbroadbent]

You can also execute a DTS Package from ASP using the Microsoft DTSPackage Object Library.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q252987

http://www.asp101.com/articles/carvin/dts/default.asp

http://www.sqldts.com/article.asp?nav=1,6,207,7

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FaStOnE]

Thanks guys... that actually has done what I need it to do! Your help is much appreciated!

Rick

 

[tlbroadbent]

Can you tell us which solution you used? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[FaStOnE]

Hey guys... have an addendum to this question. After I get the job started (Submitted to "batch&quot .. is there a way to test and see if it has completed before it continues with the next statement in the Stored Procedure?

Rick