Prev: Problem with SOAP Toolkit and web service authorization
Next: RSExecutionLog Error: DTSER_FAILURE (1). Options
From: Michael Coles on 8 Feb 2009 14:02
Assuming you absolutely cannot use an SSIS package scheduled as a SQ:L Agent
job (my first choice), I would personally use SQL CLR. I would avoid
xp_cmdshell at all costs -- there's a reason it's disabled by default.

--

========
Michael Coles
"Pro T-SQL 2008 Programmer's Guide"
http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X


"jaylou" <jaylou(a)discussions.microsoft.com> wrote in message
news:F8B9097F-F70A-4A27-B101-EF3B065CD926(a)microsoft.com...
> Do you have another way to import an Excel spreadsheet into a SQL table in
> a
> srored Proc? I would love an alternative.
> The data I have to report off of comes from an application that I can not
> access directly, but they have an export feature.
>
> Thanks,
> Joe
>
> "Michael Coles" wrote:
>
>> You might consider using an alternative to xp_cmdshell. Be aware that
>> enabling it has security implications.
>>
>> --
>>
>> ========
>> Michael Coles
>> "Pro T-SQL 2008 Programmer's Guide"
>> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
>>
>>
>> "jaylou" <jaylou(a)discussions.microsoft.com> wrote in message
>> news:C2C8CDE1-C6AE-427F-B047-EC110A945D04(a)microsoft.com...
>> > Hi all,
>> >
>> > In SQL Server 2008 64-Bit I am trying to create a stored Proc that
>> > calls
>> > an
>> > SSIS packacage to import an Excel spreadsheet using both
>> > "EXEC xp_cmdshell 'dtexec /f
>> > "\\ewrsql18\NetLocFiles\Packages\LOCTrans.dtsx"'
>> > And
>> > "EXEC xp_cmdshell 'dtexec /sq "LOCTrans" /ser ewrsql18'
>> >
>> > I can execute the package with no problems but the TSQL seems to choke
>> > when
>> > it calls the package with this error:
>> >
>> > Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel
>> > Connection Manager is not supported in the 64-bit version of SSIS, as
>> > no
>> > OLE
>> > DB provider is available.
>> >
>> > Does anyone have a solution for this?
>> >
>> > TIA,
>> > Joe
>>
>>
>>


From: jaylou on 9 Feb 2009 10:03
Thanks Michael,
I will look into that.

Joe


"Michael Coles" wrote:

> Assuming you absolutely cannot use an SSIS package scheduled as a SQ:L Agent
> job (my first choice), I would personally use SQL CLR. I would avoid
> xp_cmdshell at all costs -- there's a reason it's disabled by default.
>
> --
>
> ========
> Michael Coles
> "Pro T-SQL 2008 Programmer's Guide"
> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
>
>
> "jaylou" <jaylou(a)discussions.microsoft.com> wrote in message
> news:F8B9097F-F70A-4A27-B101-EF3B065CD926(a)microsoft.com...
> > Do you have another way to import an Excel spreadsheet into a SQL table in
> > a
> > srored Proc? I would love an alternative.
> > The data I have to report off of comes from an application that I can not
> > access directly, but they have an export feature.
> >
> > Thanks,
> > Joe
> >
> > "Michael Coles" wrote:
> >
> >> You might consider using an alternative to xp_cmdshell. Be aware that
> >> enabling it has security implications.
> >>
> >> --
> >>
> >> ========
> >> Michael Coles
> >> "Pro T-SQL 2008 Programmer's Guide"
> >> http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X
> >>
> >>
> >> "jaylou" <jaylou(a)discussions.microsoft.com> wrote in message
> >> news:C2C8CDE1-C6AE-427F-B047-EC110A945D04(a)microsoft.com...
> >> > Hi all,
> >> >
> >> > In SQL Server 2008 64-Bit I am trying to create a stored Proc that
> >> > calls
> >> > an
> >> > SSIS packacage to import an Excel spreadsheet using both
> >> > "EXEC xp_cmdshell 'dtexec /f
> >> > "\\ewrsql18\NetLocFiles\Packages\LOCTrans.dtsx"'
> >> > And
> >> > "EXEC xp_cmdshell 'dtexec /sq "LOCTrans" /ser ewrsql18'
> >> >
> >> > I can execute the package with no problems but the TSQL seems to choke
> >> > when
> >> > it calls the package with this error:
> >> >
> >> > Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel
> >> > Connection Manager is not supported in the 64-bit version of SSIS, as
> >> > no
> >> > OLE
> >> > DB provider is available.
> >> >
> >> > Does anyone have a solution for this?
> >> >
> >> > TIA,
> >> > Joe
> >>
> >>
> >>
>
>
>
First  |  Prev  | 
Pages: 1 2
Prev: Problem with SOAP Toolkit and web service authorization
Next: RSExecutionLog Error: DTSER_FAILURE (1). Options