How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to run package with variables using dtexec?

Hi,

I am running my package in this way:

exec xp_cmdshell 'dtexec /SQL "\SBLoadExcelDBLog" /SERVER test /USER **** /PASSWORD ***** /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"

/Set \Package.Variables[User::varExcelFileName].Properties[Value];"aaab"

/Set \Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"

/Set \Package.Variables[User::varExcelFileFullPath].Value;"D:\testshare\aaab.xls"

/Set \Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"

/Set \Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '

I got errors:

Started: 3:49:51 PM

Progress: 2006-01-30 15:49:52.34

Source: Extract AdHoc Data from Excel

Validating: 0% complete

End Progress

Error: 2006-01-30 15:49:52.46

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-01-30 15:49:52.46

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

Progress: 2006-01-30 15:49:52.51

Source: Extract AdHoc Data from Excel

Validating: 25% complete

End Progress

Error: 2006-01-30 15:49:52.51

Code: 0xC004700C

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: One or more component failed validation.

End Error

Error: 2006-01-30 15:49:52.51

Code: 0xC0024107

Source: Extract AdHoc Data from Excel

Description: There were errors during task validation.

End Error

DTExec: The package execution returned DTSER_FAILURE (1).

Started: 3:49:51 PM

Finished: 3:49:52 PM

Elapsed: 0.703 seconds

Finally I found the problem:

Everything is OK But:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

|||

Is there a reason for using "...[varibale name].Properties.[Value]" for the first variable and "...[varibale name].Value" for the others?

Dick Campbell

|||

No, they are basically same but different ways to access to the value of the variable.

Guangming

|||

Thanks,

I thought that it just might be a mistake. I was also curious to know, if they are both valid, whether there is any difference in the functionality.

Dick Campbell

How to retrieve sa password on Win98?

Hi, we have a problem on a server of our customer running SQL Server
2000. That PC is connected to the Internet, and probably someone has
changed the sa password.
In fact, trying to connect to the sql server using "SQL Server
Enterprise Manager" results in this error message:
"A connection could not be established to SERVERNAME - Login failed for sa."
Query Analyzer instead gives: "Unable to connect to server. Server Msg
18456, Level 16, State 1. Login failed for user sa".
Searching on the net I have found a bunch of good ways to follow, even
if they all require to join into Windows as Administrator user (and
thus, using Windows Authentication mode).
However I can't proceed because the server that runs SQL Server is
Windows 98, thus Windows Authentication Mode is not available.
Can you help please?
TIA,
Paolo
Paolo wrote:

> Hi, we have a problem on a server of our customer running SQL Server
> 2000.
Hrm... sorry, I meant SQL Server 7 ;)
|||I was not aware that it is possible to install SQL Server on Windows 98.
Are you talking about MSDE? or perhaps the Developer Edition of SQL Server?
Since you did not take the appropriate steps to secure your "server" and you
already looked into other options but they will not work for you perhaps the
only option you have left is to contact Microsoft Support.
I do have one final idea: Shut down the "server" copy the database files,
and attach them to a new server. At least you will be able to access your
data.
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23EtmbfCkEHA.3160@.TK2MSFTNGP10.phx.gbl...
> Hi, we have a problem on a server of our customer running SQL Server
> 2000. That PC is connected to the Internet, and probably someone has
> changed the sa password.
> In fact, trying to connect to the sql server using "SQL Server
> Enterprise Manager" results in this error message:
> "A connection could not be established to SERVERNAME - Login failed for
sa."
> Query Analyzer instead gives: "Unable to connect to server. Server Msg
> 18456, Level 16, State 1. Login failed for user sa".
> Searching on the net I have found a bunch of good ways to follow, even
> if they all require to join into Windows as Administrator user (and
> thus, using Windows Authentication mode).
> However I can't proceed because the server that runs SQL Server is
> Windows 98, thus Windows Authentication Mode is not available.
> Can you help please?
> TIA,
> Paolo
|||Keith Kratochvil wrote:

> I do have one final idea: Shut down the "server" copy the database files,
> and attach them to a new server. At least you will be able to access your
> data.
>
Thanks for the suggestion, it worked!
I copied a pristine master database from a vanilla sql server
installation to the "locked" sql server, then the databases containing
datas has been attached using sp_attach_db.
Finally I have specified a non blank password for user sa...
Regards,
Paolo
|||I am glad that you were able to figure things out!
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23a6VLoOkEHA.3844@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Keith Kratochvil wrote:
files,[vbcol=seagreen]
your
> Thanks for the suggestion, it worked!
> I copied a pristine master database from a vanilla sql server
> installation to the "locked" sql server, then the databases containing
> datas has been attached using sp_attach_db.
> Finally I have specified a non blank password for user sa...
> Regards,
> Paolo

How to retrieve sa password on Win98?

Hi, we have a problem on a server of our customer running SQL Server
2000. That PC is connected to the Internet, and probably someone has
changed the sa password.
In fact, trying to connect to the sql server using "SQL Server
Enterprise Manager" results in this error message:
"A connection could not be established to SERVERNAME - Login failed for sa."
Query Analyzer instead gives: "Unable to connect to server. Server Msg
18456, Level 16, State 1. Login failed for user sa".
Searching on the net I have found a bunch of good ways to follow, even
if they all require to join into Windows as Administrator user (and
thus, using Windows Authentication mode).
However I can't proceed because the server that runs SQL Server is
Windows 98, thus Windows Authentication Mode is not available.
Can you help please?
TIA,
PaoloPaolo wrote:
> Hi, we have a problem on a server of our customer running SQL Server
> 2000.
Hrm... sorry, I meant SQL Server 7 ;)|||I was not aware that it is possible to install SQL Server on Windows 98.
Are you talking about MSDE? or perhaps the Developer Edition of SQL Server?
Since you did not take the appropriate steps to secure your "server" and you
already looked into other options but they will not work for you perhaps the
only option you have left is to contact Microsoft Support.
I do have one final idea: Shut down the "server" copy the database files,
and attach them to a new server. At least you will be able to access your
data.
--
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23EtmbfCkEHA.3160@.TK2MSFTNGP10.phx.gbl...
> Hi, we have a problem on a server of our customer running SQL Server
> 2000. That PC is connected to the Internet, and probably someone has
> changed the sa password.
> In fact, trying to connect to the sql server using "SQL Server
> Enterprise Manager" results in this error message:
> "A connection could not be established to SERVERNAME - Login failed for
sa."
> Query Analyzer instead gives: "Unable to connect to server. Server Msg
> 18456, Level 16, State 1. Login failed for user sa".
> Searching on the net I have found a bunch of good ways to follow, even
> if they all require to join into Windows as Administrator user (and
> thus, using Windows Authentication mode).
> However I can't proceed because the server that runs SQL Server is
> Windows 98, thus Windows Authentication Mode is not available.
> Can you help please?
> TIA,
> Paolo|||Keith Kratochvil wrote:
> I do have one final idea: Shut down the "server" copy the database files,
> and attach them to a new server. At least you will be able to access your
> data.
>
Thanks for the suggestion, it worked!
I copied a pristine master database from a vanilla sql server
installation to the "locked" sql server, then the databases containing
datas has been attached using sp_attach_db.
Finally I have specified a non blank password for user sa...
Regards,
Paolo|||I am glad that you were able to figure things out!
--
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23a6VLoOkEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Keith Kratochvil wrote:
> > I do have one final idea: Shut down the "server" copy the database
files,
> > and attach them to a new server. At least you will be able to access
your
> > data.
> >
> Thanks for the suggestion, it worked!
> I copied a pristine master database from a vanilla sql server
> installation to the "locked" sql server, then the databases containing
> datas has been attached using sp_attach_db.
> Finally I have specified a non blank password for user sa...
> Regards,
> Paolo

How to retrieve sa password on Win98?

Hi, we have a problem on a server of our customer running SQL Server
2000. That PC is connected to the Internet, and probably someone has
changed the sa password.
In fact, trying to connect to the sql server using "SQL Server
Enterprise Manager" results in this error message:
"A connection could not be established to SERVERNAME - Login failed for sa."
Query Analyzer instead gives: "Unable to connect to server. Server Msg
18456, Level 16, State 1. Login failed for user sa".
Searching on the net I have found a bunch of good ways to follow, even
if they all require to join into Windows as Administrator user (and
thus, using Windows Authentication mode).
However I can't proceed because the server that runs SQL Server is
Windows 98, thus Windows Authentication Mode is not available.
Can you help please?
TIA,
PaoloPaolo wrote:

> Hi, we have a problem on a server of our customer running SQL Server
> 2000.
Hrm... sorry, I meant SQL Server 7 ;)|||I was not aware that it is possible to install SQL Server on Windows 98.
Are you talking about MSDE? or perhaps the Developer Edition of SQL Server?
Since you did not take the appropriate steps to secure your "server" and you
already looked into other options but they will not work for you perhaps the
only option you have left is to contact Microsoft Support.
I do have one final idea: Shut down the "server" copy the database files,
and attach them to a new server. At least you will be able to access your
data.
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23EtmbfCkEHA.3160@.TK2MSFTNGP10.phx.gbl...
> Hi, we have a problem on a server of our customer running SQL Server
> 2000. That PC is connected to the Internet, and probably someone has
> changed the sa password.
> In fact, trying to connect to the sql server using "SQL Server
> Enterprise Manager" results in this error message:
> "A connection could not be established to SERVERNAME - Login failed for
sa."
> Query Analyzer instead gives: "Unable to connect to server. Server Msg
> 18456, Level 16, State 1. Login failed for user sa".
> Searching on the net I have found a bunch of good ways to follow, even
> if they all require to join into Windows as Administrator user (and
> thus, using Windows Authentication mode).
> However I can't proceed because the server that runs SQL Server is
> Windows 98, thus Windows Authentication Mode is not available.
> Can you help please?
> TIA,
> Paolo|||Keith Kratochvil wrote:

> I do have one final idea: Shut down the "server" copy the database files,
> and attach them to a new server. At least you will be able to access your
> data.
>
Thanks for the suggestion, it worked!
I copied a pristine master database from a vanilla sql server
installation to the "locked" sql server, then the databases containing
datas has been attached using sp_attach_db.
Finally I have specified a non blank password for user sa...
Regards,
Paolo|||I am glad that you were able to figure things out!
Keith
"Paolo" <paolo@.red.it> wrote in message
news:%23a6VLoOkEHA.3844@.TK2MSFTNGP12.phx.gbl...
> Keith Kratochvil wrote:
>
files,[vbcol=seagreen]
your[vbcol=seagreen]
> Thanks for the suggestion, it worked!
> I copied a pristine master database from a vanilla sql server
> installation to the "locked" sql server, then the databases containing
> datas has been attached using sp_attach_db.
> Finally I have specified a non blank password for user sa...
> Regards,
> Paolo

How to retrieve database password

I previously had a web server that was running a application that used a
MSDE database. This server had a motherboard failure and I was not able to
repair it. I did get all the data off the drives so the MSDE SQL databases
did get copied. The problem is the admin password for those database is not
known to me and the previous admin does not remember. I need this password
so that I can reinstall the web application and give it the admin password
for the old databases. The tool the software company gave me to pull the
password does not work, as it pulls the information from the registry (which
is not the original as I have a new boot drive). Does anyone know how to
pull MSDE SQL database passwords without booting from the original drive?
There has to be a way to get all of the databases back since the data is
intact.
If you have the MDF files, just attach the database to a new instance
of MSDE with sp_attachDB.
If you mean a specially created user in the database or server AFAIK
which the application will use for connection and which is not known to
you, this password cannot be recovered. (If you have no access to the
stored password in the source code)
HTH, Jens Suessmeyer.
|||All I have are the old .SQL files no MDF files were created.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136994106.307693.42900@.g43g2000cwa.googlegro ups.com...
> If you have the MDF files, just attach the database to a new instance
> of MSDE with sp_attachDB.
> If you mean a specially created user in the database or server AFAIK
> which the application will use for connection and which is not known to
> you, this password cannot be recovered. (If you have no access to the
> stored password in the source code)
> HTH, Jens Suessmeyer.
>
|||If it is a SQL Server Authenticaion you should look in that Script
files (which they are supposed to be) for sp_addlogin which is used to
create the SQL Server auth. users.
HTH, jens Suessmeyer.
|||1. Go to Start | Run | type in Command.
2. Type the following command in the DOS prompt.
OSQL S <SERVERNAME> -E =
Note Be sure to replace SERVERNAME with the name of your SQL server.
Typically this is the name of your computer.
Note If this statement does not run successfully try entering it again
excluding the '=' sign.
3. Press Enter.
4. Type the line exactly as it is written below where ok is your new
password:
1> EXEC sp_password NULL, 'ok', 'sa'
5. Press Enter.
6. Type the line below:
2> Go
7. Press Enter. This will change the sa password from a value unknown to
ok (this can be changed in step 4).
=
"J Coldwater" <jams@.purdue.edu> wrote in message
news:u5y$dTsFGHA.2896@.TK2MSFTNGP10.phx.gbl...
> I previously had a web server that was running a application that used a
> MSDE database. This server had a motherboard failure and I was not able to
> repair it. I did get all the data off the drives so the MSDE SQL databases
> did get copied. The problem is the admin password for those database is
> not known to me and the previous admin does not remember. I need this
> password so that I can reinstall the web application and give it the admin
> password for the old databases. The tool the software company gave me to
> pull the password does not work, as it pulls the information from the
> registry (which is not the original as I have a new boot drive). Does
> anyone know how to pull MSDE SQL database passwords without booting from
> the original drive? There has to be a way to get all of the databases back
> since the data is intact.
>

how to retrieve a password saved by SQL server

Hi,

I am able to connect to a SQL server and have checked the "remember my password" box in the connection diaglog window. Unfortunately, I forget what the password is, and now have to use it elsewhere. How can I get it? (I tried copy and paste, but it won't work).

Thanks,

Hi,

Unfortunatly I am beginer in this but it happens for me to have a corrupted master database and I couldn't login to SQL. The solution it was that I restore the master database using the command: rebuildm.exe from the SQL CD.

If I would have a problem like you, I would make a screen shots of all the settings which I have, I will detache the database I will stop the SQL service and I will restore the master database and I will put a new password and redo all the settings.

Good luck,

Diana

|||

Diana, thanks a lot for reply. I am still hoping there might a way to extract the password from my computer (since i have no trouble connecting to the server). Does anybody have any idea?

Thanks,

June

|||MSSQL stores not passwords but password hashes. There is no way to retrieve clear password from its hash: the hash operation is irreversible.