Move data from Excel to SQL Server via ASP

[midwesthills]

I'm trying to import data from an Excel spreadsheet, and write it into
a SQL Server database. I know how to read the data from Excel and how
to write the data to SQL Server. The part I'm struggling with is
moving the data from Excel to the DB. I'd like to just open a
recordset from Excel, then write that same recordset to the DB-- just
not sure how that's done. I've tried it a couple different ways with
no luck. Could someone please give me some guidance on this? Thank
you in advance for the help!

[David Morgan]

If the Excel file will always be the same structure, (and after some
preliminary checks to make sure it is etc.), set-up a DTS package and then
execute it when desired from ASP.

If you save the DTS package as a job, (from memory... )... you will be able
to execute it using sp_execute_job.

From my system where I do this:

objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
adCmdStoredProc


CREATE Procedure usp_ImportPayslipsDTS
As
set nocount on
EXEC msdb.dbo.sp_start_job @job_name = 'Payslips Import'
return




"midwesthills" <midwesthills@hotmail.com> wrote in message
news:1f28b412.0408020548.1388b938@posting.google.c om...

> I'm trying to import data from an Excel spreadsheet, and write it into
> a SQL Server database. I know how to read the data from Excel and how
> to write the data to SQL Server. The part I'm struggling with is
> moving the data from Excel to the DB. I'd like to just open a
> recordset from Excel, then write that same recordset to the DB-- just
> not sure how that's done. I've tried it a couple different ways with
> no luck. Could someone please give me some guidance on this? Thank
> you in advance for the help!

[midwesthills]

For of all, thanks for the advice. I'm not too fimiliar with DTS
packages, but was able to create one using the GUI provided in
Enterprise Manager.

I'm not sure what you meant by saving the package as a job. I tried
saving it as a package in SQL Server and as a VB file, but can't call
it from a stored procudure using the EXEC command. What am I missing?
Could you please elaborate? Thanks again!!!



"David Morgan" <david@davidmorgan.me.uk> wrote in message news:<uWtR97NeEHA.3864@TK2MSFTNGP10.phx.gbl>...

> If the Excel file will always be the same structure, (and after some
> preliminary checks to make sure it is etc.), set-up a DTS package and then
> execute it when desired from ASP.
>
> If you save the DTS package as a job, (from memory... )... you will be able
> to execute it using sp_execute_job.
>
> From my system where I do this:
>
> objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
> adCmdStoredProc
>
>
> CREATE Procedure usp_ImportPayslipsDTS
> As
> set nocount on
> EXEC msdb.dbo.sp_start_job @job_name = 'Payslips Import'
> return
>
>
>
>
> "midwesthills" <midwesthills@hotmail.com> wrote in message
> news:1f28b412.0408020548.1388b938@posting.google.c om...

> > I'm trying to import data from an Excel spreadsheet, and write it into
> > a SQL Server database. I know how to read the data from Excel and how
> > to write the data to SQL Server. The part I'm struggling with is
> > moving the data from Excel to the DB. I'd like to just open a
> > recordset from Excel, then write that same recordset to the DB-- just
> > not sure how that's done. I've tried it a couple different ways with
> > no luck. Could someone please give me some guidance on this? Thank
> > you in advance for the help!

[Bob Barrows [MVP]]

midwesthills wrote:

> For of all, thanks for the advice. I'm not too fimiliar with DTS
> packages, but was able to create one using the GUI provided in
> Enterprise Manager.
>
> I'm not sure what you meant by saving the package as a job. I tried
> saving it as a package in SQL Server and as a VB file, but can't call
> it from a stored procudure using the EXEC command. What am I missing?
> Could you please elaborate? Thanks again!!!
>

Go to [url]www.sqldts.com[/url] where you will find various articles describing how to
execute packages from stored procedures, asp, etc.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[David Morgan]

Schedule DTS Package for Later Execution - Any time, will remove it later.
Save DTS Package - SQL Server

Give the package a name etc.

Go to Management \ SQL Server Agent \ Jobs

Double click your job that appears there, switch to Schedules and delete the
schedule.

Then you can use the sp_start_job proc as below.



"midwesthills" <midwesthills@hotmail.com> wrote in message
news:1f28b412.0408030357.a4e30fc@posting.google.co m...

> For of all, thanks for the advice. I'm not too fimiliar with DTS
> packages, but was able to create one using the GUI provided in
> Enterprise Manager.
>
> I'm not sure what you meant by saving the package as a job. I tried
> saving it as a package in SQL Server and as a VB file, but can't call
> it from a stored procudure using the EXEC command. What am I missing?
> Could you please elaborate? Thanks again!!!
>
>
>
> "David Morgan" <david@davidmorgan.me.uk> wrote in message

news:<uWtR97NeEHA.3864@TK2MSFTNGP10.phx.gbl>...

> > If the Excel file will always be the same structure, (and after some
> > preliminary checks to make sure it is etc.), set-up a DTS package and

then

> > execute it when desired from ASP.
> >
> > If you save the DTS package as a job, (from memory... )... you will be

able

> > to execute it using sp_execute_job.
> >
> > From my system where I do this:
> >
> > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
> > adCmdStoredProc
> >
> >
> > CREATE Procedure usp_ImportPayslipsDTS
> > As
> > set nocount on
> > EXEC msdb.dbo.sp_start_job @job_name = 'Payslips Import'
> > return
> >
> >
> >
> >
> > "midwesthills" <midwesthills@hotmail.com> wrote in message
> > news:1f28b412.0408020548.1388b938@posting.google.c om...

> > > I'm trying to import data from an Excel spreadsheet, and write it into
> > > a SQL Server database. I know how to read the data from Excel and how
> > > to write the data to SQL Server. The part I'm struggling with is
> > > moving the data from Excel to the DB. I'd like to just open a
> > > recordset from Excel, then write that same recordset to the DB-- just
> > > not sure how that's done. I've tried it a couple different ways with
> > > no luck. Could someone please give me some guidance on this? Thank
> > > you in advance for the help!

[midwesthills]

Is this better than using DTS.Package object to step through the
package? Does either method inheriently use transactions, or do I
need to code that in as well?

Thanks! You've been a great help!!!



"David Morgan" <david@davidmorgan.me.uk> wrote in message news:<u$oJFSVeEHA.3944@tk2msftngp13.phx.gbl>...

> Schedule DTS Package for Later Execution - Any time, will remove it later.
> Save DTS Package - SQL Server
>
> Give the package a name etc.
>
> Go to Management \ SQL Server Agent \ Jobs
>
> Double click your job that appears there, switch to Schedules and delete the
> schedule.
>
> Then you can use the sp_start_job proc as below.
>
>
>
> "midwesthills" <midwesthills@hotmail.com> wrote in message
> news:1f28b412.0408030357.a4e30fc@posting.google.co m...

> > For of all, thanks for the advice. I'm not too fimiliar with DTS
> > packages, but was able to create one using the GUI provided in
> > Enterprise Manager.
> >
> > I'm not sure what you meant by saving the package as a job. I tried
> > saving it as a package in SQL Server and as a VB file, but can't call
> > it from a stored procudure using the EXEC command. What am I missing?
> > Could you please elaborate? Thanks again!!!
> >
> >
> >
> > "David Morgan" <david@davidmorgan.me.uk> wrote in message

> news:<uWtR97NeEHA.3864@TK2MSFTNGP10.phx.gbl>...

> > > If the Excel file will always be the same structure, (and after some
> > > preliminary checks to make sure it is etc.), set-up a DTS package and

> then

> > > execute it when desired from ASP.
> > >
> > > If you save the DTS package as a job, (from memory... )... you will be

> able

> > > to execute it using sp_execute_job.
> > >
> > > From my system where I do this:
> > >
> > > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
> > > adCmdStoredProc
> > >
> > >
> > > CREATE Procedure usp_ImportPayslipsDTS
> > > As
> > > set nocount on
> > > EXEC msdb.dbo.sp_start_job @job_name = 'Payslips Import'
> > > return
> > >
> > >
> > >
> > >
> > > "midwesthills" <midwesthills@hotmail.com> wrote in message
> > > news:1f28b412.0408020548.1388b938@posting.google.c om...
> > > > I'm trying to import data from an Excel spreadsheet, and write it into
> > > > a SQL Server database. I know how to read the data from Excel and how
> > > > to write the data to SQL Server. The part I'm struggling with is
> > > > moving the data from Excel to the DB. I'd like to just open a
> > > > recordset from Excel, then write that same recordset to the DB-- just
> > > > not sure how that's done. I've tried it a couple different ways with
> > > > no luck. Could someone please give me some guidance on this? Thank
> > > > you in advance for the help!

[David Morgan]

No, it's no better, and all it does is kick off the process. It does not
wait for it to finish and only can only return the result of submitting the
job.

Go for the sqldts.com approach if you wish, I did, but decided that I didn't
need all of that stuff and I think permissions caused me a few probs. Not
sure if the article on the website covers permissions but I would be
surprised if the I_USR account was allowed to execute the DTS Objects.

I would imagine that both methods use transactions. I would not bother
coding it in as I am sure DTS rolls back on failure although I am not
certain.

"midwesthills" <midwesthills@hotmail.com> wrote in message
news:1f28b412.0408031159.bec18b5@posting.google.co m...

> Is this better than using DTS.Package object to step through the
> package? Does either method inheriently use transactions, or do I
> need to code that in as well?
>
> Thanks! You've been a great help!!!
>
>
>
> "David Morgan" <david@davidmorgan.me.uk> wrote in message

news:<u$oJFSVeEHA.3944@tk2msftngp13.phx.gbl>...

> > Schedule DTS Package for Later Execution - Any time, will remove it

later.

> > Save DTS Package - SQL Server
> >
> > Give the package a name etc.
> >
> > Go to Management \ SQL Server Agent \ Jobs
> >
> > Double click your job that appears there, switch to Schedules and delete

the

> > schedule.
> >
> > Then you can use the sp_start_job proc as below.
> >
> >
> >
> > "midwesthills" <midwesthills@hotmail.com> wrote in message
> > news:1f28b412.0408030357.a4e30fc@posting.google.co m...

> > > For of all, thanks for the advice. I'm not too fimiliar with DTS
> > > packages, but was able to create one using the GUI provided in
> > > Enterprise Manager.
> > >
> > > I'm not sure what you meant by saving the package as a job. I tried
> > > saving it as a package in SQL Server and as a VB file, but can't call
> > > it from a stored procudure using the EXEC command. What am I missing?
> > > Could you please elaborate? Thanks again!!!
> > >
> > >
> > >
> > > "David Morgan" <david@davidmorgan.me.uk> wrote in message

> > news:<uWtR97NeEHA.3864@TK2MSFTNGP10.phx.gbl>...

> > > > If the Excel file will always be the same structure, (and after some
> > > > preliminary checks to make sure it is etc.), set-up a DTS package

and

> > then

> > > > execute it when desired from ASP.
> > > >
> > > > If you save the DTS package as a job, (from memory... )... you will

be

> > able

> > > > to execute it using sp_execute_job.
> > > >
> > > > From my system where I do this:
> > > >
> > > > objConn.Execute "usp_ImportPayslipsDTS", , adExecuteNoRecords +
> > > > adCmdStoredProc
> > > >
> > > >
> > > > CREATE Procedure usp_ImportPayslipsDTS
> > > > As
> > > > set nocount on
> > > > EXEC msdb.dbo.sp_start_job @job_name = 'Payslips Import'
> > > > return
> > > >
> > > >
> > > >
> > > >
> > > > "midwesthills" <midwesthills@hotmail.com> wrote in message
> > > > news:1f28b412.0408020548.1388b938@posting.google.c om...
> > > > > I'm trying to import data from an Excel spreadsheet, and write it

into

> > > > > a SQL Server database. I know how to read the data from Excel and

how

> > > > > to write the data to SQL Server. The part I'm struggling with is
> > > > > moving the data from Excel to the DB. I'd like to just open a
> > > > > recordset from Excel, then write that same recordset to the DB--

just

> > > > > not sure how that's done. I've tried it a couple different ways

with

> > > > > no luck. Could someone please give me some guidance on this?

Thank

> > > > > you in advance for the help!