DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this '
Thanks In Advance
JeffYou would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this '
> Thanks In Advance
> Jeff
>|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas '
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas '
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>> Thanks Allan
>> My Excel sheet contains different sheets and I just want to read one
>> sheet to load into table. Thoughts/Ideas '
>> Thanks
>>
>> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
>> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>> You would probably want to have a look at these two articles then.
>> Looping, Importing and Archiving
>> (http://www.sqldts.com/default.aspx?246)
>> How to loop through a global variable Rowset
>> (http://www.sqldts.com/default.aspx?298)
>> --
>>
>> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
>> www.SQLDTS.com - The site for all your DTS needs.
>> www.SQLIS.com - You thought DTS was good. here we show you the new
>> stuff.
>> www.konesans.com - Consultancy from the people who know
>>
>> "MS User" <sqlman@.sql.com> wrote in message
>> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>> SQL 2000
>> I got a table with file names and path for the Excel files to be loaded
>> into a sql table.
>> My plan is to use DTS and global variable to assign the path and file
>> name and use that as the source for the data-pump.
>> Any thoughts or sample code to achieve this '
>> Thanks In Advance
>> Jeff
>>
>>
>>
>

DTS - Excel file path from a table

SQL 2000
I got a table with file names and path for the Excel files to be loaded into
a sql table.
My plan is to use DTS and global variable to assign the path and file name
and use that as the source for the data-pump.
Any thoughts or sample code to achieve this ?
Thanks In Advance
Jeff
You would probably want to have a look at these two articles then.
Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
> SQL 2000
> I got a table with file names and path for the Excel files to be loaded
> into a sql table.
> My plan is to use DTS and global variable to assign the path and file name
> and use that as the source for the data-pump.
> Any thoughts or sample code to achieve this ?
> Thanks In Advance
> Jeff
>
|||Thanks Allan
My Excel sheet contains different sheets and I just want to read one sheet
to load into table. Thoughts/Ideas ?
Thanks
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
> You would probably want to have a look at these two articles then.
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:u$ZIoQ74EHA.924@.TK2MSFTNGP14.phx.gbl...
>
|||The worksheets/named ranges within a workbook will be exposed to DTS as
table objects.
So this will give you the ability to change the DataSource property of the
Excel workbook and the SourceObjectName of the DataPump task.

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
"MS User" <sqlman@.sql.com> wrote in message
news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks Allan
> My Excel sheet contains different sheets and I just want to read one sheet
> to load into table. Thoughts/Ideas ?
> Thanks
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eAuNYX74EHA.828@.TK2MSFTNGP14.phx.gbl...
>
|||Thanks Allan. That did it.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:%23B%23e8JA5EHA.828@.TK2MSFTNGP14.phx.gbl...
> The worksheets/named ranges within a workbook will be exposed to DTS as
> table objects.
> So this will give you the ability to change the DataSource property of the
> Excel workbook and the SourceObjectName of the DataPump task.
>
> --
>
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> www.konesans.com - Consultancy from the people who know
>
> "MS User" <sqlman@.sql.com> wrote in message
> news:O%23LVGP84EHA.2540@.TK2MSFTNGP09.phx.gbl...
>

dtexec Return Success BUT Not Run!

I've created a Maintenance Plan in Microsoft SQL Server Management Studio (Sql Server 2005 SP2 + Windows Updates) and currently trying to execute it via the dtexec command line program.

The problem is that it reports that it executed the Maintenance Plan but it never actually executes the Plan. The data isn't updated and the CPU and hard drive I/O reports nothing happening.

I've checked the argument lists but I don't think I've missed anything...?

On the Sql Machine itself, I run the command line as follows:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\V2Admin>cd \
C:\>dtexec /SQL "\Maintenance Plans\GPI Update" /Server V2SQL\VC2 /User sa /Pass
word XxXxXxX
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:29:43 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:29:43 AM
Finished: 11:29:44 AM
Elapsed: 0.375 seconds

C:\>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>I don't understand why you are running using DTEXEC, have you tried to schedule the package that is created using SSIS?|||We are running DTExec as we want chain together and run other processes outside as Sql Server as well, but want to use the History tracker of Sql Server's Maintenance Plans.

The Sql Server Maintenance Plan tasks are kept with Sql Server and the external processes are kept with their relevant tools.

We need to run both together which is why we need to call DtExec.

At the worst case scenario, we can simply run SqlCmd and execute the desired functionality, but that is asking for problems as there would then be two locations to maintain the Maintenance Tasks Sub-Plans.

Not a smart move in any operations manual!
|||

Schedule a maintenance plan and look at the arguments to dtexec in the agent job step. You will find that the subplan that contains your job steps is enabled from that command line. If you just run a maintenance plan without so enabling the subplans, all are disabled, and nothing runs, as you have confirmed in your scenario.

jkh