Executing SSIS Package - SQL 2005 Express

Hi,

I have created SSIS Package using DTS vizard in SQL 2005 Express. Help me out to execute the Package.

Thanks

Even I am also trying for the Same. Update me if you get a solution.sql

Executing SSIS from stored procedure - need to get return value into ASP.NET

I'm executing an SSIS package using the following stored procedure

ALTER PROC [dbo].[SSISRunBuildSCCDW]ASBEGINDECLARE @.ServerNameVARCHAR(30), @.ReturnValueint, @.Cmdvarchar(1000)SET @.ReturnValue = -1SET @.ServerName ='myserver'SET @.Cmd ='DTExec /SER ' + @.ServerName +' ' +' /SQL ' +'\BuildSCCDW '--Location of the package stored in the mdb --' /CONF "\\ConfigFilePath.dtsConfig" ' +--' /SET \Package.Variables[ImportUserID].Value; ' +--' /U "LoginName" /P "password" 'EXECUTE @.ReturnValue = master..xp_cmdshell @.Cmd, NO_OUTPUTRETURN @.ReturnValue--SELECT @.ReturnValue [Result]END

I'm then using a tableadapter to execute this from my ASP.NET page using the following code,

Protected Sub ExecutePackage()Dim ExecuteAdapterAs New SCC_DAL.RunSSISTableAdapters.SSISRunBuildSCCDWTableAdapter() ExecuteAdapter.SetCommandTimeOut(0)Dim strResultAs String strResult = ExecuteAdapter.Execute() lblResult.Text = strResultEnd Sub

If I remove 'NO_OUTPUT' from my stored procedure and run it the results contain a field named 'output' with all the steps from my package. Then below this is my return value. In my code I can only return the first step of the package results - which tells me nothing useful. I need to be able to return the return value (0-6) in my code.

When I have 'NO_OUTPUT' in my stored procedure and execute it I am left with just the return value. However no value is returned in my code at all although the package does run. I've tried bother RETURN @.ReturnValue and SELECT @.ReturnValue to no avail.

Can someone suggest how I can get the value 0-6 to my code?

Sorted!

Commented out RETURN @.ReturnValue and uncommented the line below it and it works. I had already tried that... bizarre.

Executing SQL Package

Hi

I have created a DTS package in SQL 2005 which will pull the data from oracle and pushes into SQLServer. I am able to execute the package from business intelligence wizard.

Is there anyway to trigger this package manually apart from the wizard?.

looking for responses.

Thanks
GaneshIt can also be executed from query analyzer.

You call it from any forntend tool also.|||Hi

Thanks for your response.

When I create package from Business intelligence wizard, It was created as Package1.dtsx. Now , How can I invoke package1.dtsx from query analyzer?.

As far as I know, In oracle, a package will contain main and a body procedures. We can invoke the main procedure from any command prompt. But I am new to SQL packages. Can you suggest me in this regard.

Thanks,
Ganesh

Executing password protected packages

Hi - I have a master package that executes a series of other packages. Each of these 'sub' packages has the security property Encryption Level set to 'EncryptSensitiveWithPassword'.

The master package has a series of file connections in the Connection Manager, one for each sub package, in which the password of the corresponding sub package is provided.

When I run the master package in BIDS (in interactive mode) it opens each of the sub packages, requests the password and gives the 'Document contains one or more lines of extremely long text' dialog box.

Is there any way to suppress the repeated password requests (seeing as it has already been provided in the Connection Manager) and warnings about long lines of text when executing the master package?

Thanks . . . Ed

There is no way to suppress the warnings about long lines of text - that is an annoying VS thing. Hopefully it will disappear in some upcoming service pack.

I don't know anything about the password requests thing I'm afraid.

-Jamie

|||

Hey Jamie,

What's up buddy, long time no talk to...

How have you handled this encryption piece before when working working with many packages, ever use the encrypt sensitive by password, etc...?

|||

Hiya Jason,

No, we never use encryption. We store passwords in .dtsconfig files.

I've just re-read Ed's initial post and realised why the prompt for a password is appearing. Simply its because the package has a password on it so whenever it opens up, you need to provide that password.

Ed, how are you supplying the password for each package within the connection manager?

-Jamie

|||

Ah, that makes sense, thanks...

I would recommend you do this Ed, just to eliminate the annoyances of having to enter passwords all the time.

|||

Hi Jamie

Apologies, my original post was badly worded . . . the password for the package is provided in the Execute Package task properties (the PackagePassword property).

Is this property ignored when the package is run in BIDS in interactive mode? Am I misunderstanding something?

Thanks . . . Ed

|||

Ah right. Yes, that makes more sense :)

Running this in BIDS is just a peculiarity I guess. BIDS opens up a package, notices it has a password, and asks for the password. it'd be nice if BIDS were clever enough to say "Ah, this package is being executed from elsewhere, perhaps the password is supplied there".

I suspect that this would be very difficult to do though.

-Jamie

Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!

Executing Package Question

Hi,

Simplification of setup:
- SQL Server Box with Enterprise Edition of SQL Server 2005
- Workstation with the client tools (Managment Studio, Business Intelligence Studio, etc) SQL Server 2005 installed running Windows XP

1. Am I right in saying that I need to install the SSIS service in order to run this package on my workstation from the command line, the Execute Package Utility tool, or through Management Studio?
2. I can however execute the package on my workstation in the Business Intelligence Studio without the SSIS service installed, correct?
3. Assuming I need to install the SSIS service on my workstation, if I execute the package via the command line or the Execute Package Utility utility, will it run on my local machine or on the server (i.e. which gets the load)?
4. Does Service Pack 1 address these issues?

Thanks a lot for any help you could provide!

John

1 - Yes

2 - Not sure, maybe, but I would just install it all, some stuff will not work unless it is all there correctly.

3 - On your workstation. SSIS is a client-side hosted process, so it runs under the context of the excution host, which means the same machine and security context as teh user running dtexec, BIDS, or when scheduled it use the same rules as for the SQL Server Agent job context, normally the service account, or maybe the proxy account if set.

4 - What issues?

|||

Hi there,

As far as my experience goes the following :

1. you need to have the developer edition op SQL SERVER 2005 to be able to run your packages from the command line With the regular enterprise edition you can only run them under devenv

3. You can only install SQL SERVER 2005 Integration services on a server not on a workstation

Greetings,

Paul

|||Thanks for the info!

The issues I mean are only being able to run an SSIS package via the command line from a server with SSIS installed. With DTS2000, I could use a command on any box that would specify the SQL Server the DTS package resided on. Is this lost functionality with 2005?|||

John, DTS could be installed as a client redistributable, and required a CAL only. SSIS is a licensed as part of the server and has a slightly more involved install, it is not redistributable. You can install SSIS only, you don't need tools or SQL Server etc, but you need to license the machine as a SQL Server.

Paul, sorry but wrong on both counts. Executing packages from the command line is available in all SQL Server supporting SSIS editions, Dev, Std and Ent editions. (The three editions that include SSIS). You can run SSIS on a workstation, the Developer edition is just for that scenario, and *all* components can be installed. People often install SQL Server workstation components only, and forget the SSIS service which causes issues. Just search this forum!

|||Darren,

Thank you for the information. As I understand it, I can't execute an SSIS package via the command line utility unless I'm doing so on a Server OS (i.e. not XP) and have SSIS running.

That being said, is there a work around method that would allow me to run an SSIS package from the command line utility without having a Server OS (i.e. XP) and without SSIS running? You mentioned something about a "client redistributable" and a "CAL", but I'm not sure what you mean. Is what I want to do possible? I'm basically trying to preserve the functionality and processes we had in place before we upgraded from 2000 to 2005.

Thanks again!

John|||Does anyone have a work around for this?

Being able to call an SSIS package via the command line from a workstation that is executed on the server. (This is what I do with DTS2000.)

Thanks!

John|||

For the DTS 2000, unless you're doing a remote execution or starting a job, you're actually loading that DTS package from the server to your workstation and then running it on your workstation. DTExec I believe works the same way on SQL 2005, i.e. if run on your desktop, that's where the package will execute.

|||I believe I found a suitable solution to my scenario. I can use the sqlcmd.exe at the command line to execute a stored procedure on the SQL Server running SSIS with the packages. I just need to set up a SQL Agent with the SSIS package as a step without a schedule. The built-in stored procedure for executing a SQL Agent should take care of it. Thoughts? Thanks!

John|||

Larry is correct. DTS is no different from SSIS in the execution location and behaviour of command line tools, the only difference for you to be aware of is the is the licensing. You still needed to install DTS on the workstation to get DTSRUN, as you need to install SSIS to get DTEXEC.

To execute packages on a server, the job method works well. It is asynch, and gives no progress, but it absracts you away from the workstation and also the user security context of that which I see as a good thing.

|||

If you're trying to execute an SSIS package via the job scheduler but don't want to use the built in job type for some reason, you could just set up a OS Command and use DTExec to execute it and you are correct that the job would need no schedule.

You can start the job with sp_start_job assuming the user has permissions to that and the job or set up a stored procedure with an EXECUTE AS to elevate priviledges. Just be careful what accounts you give the ability to execute that stored procedure.

|||Great - thanks for the confirmation!

I have a follow-up question though. With DTS2000, I was able to execute a command and it would return witha success/failure code when the package would end. My programs would wait for that message until continuing with other tasks. However, now with SSIS I get a message returned right away from the -Q "exec ..." statement, not when the SQL Agent and in turn SSIS package has succeeded/failed. Is there anyway to get this information returned? Thanks again for all the help!|||Anyone have a solution to this? Thanks!

Executing Package From Web Service

Hi,

I'm having another go at attempting to call an SSIS package from a web service. The Web Service is set up on the same server as the sql server and SSIS package deployment. When i attempt to run the pacage from the web service it starts and then inmediately fails. I can see this in both the Integration Service Logs and the event viewer.

If i click on the package itself and run it using the DTExecUI, it runs without a problem. No logs are output by the package when called by the web service.

Can anyone suggest anything that i could to see if i can diagnose where the problem stems from.

Many thanks in advance,

Grant

Are any exceptions being thrown within the WS code?

Are you passing IDTSEvents or an implementation of (DefaultEvenst for example) into the load and execute methods?

Do you get any error events?

You really need to have decent error handling and make good use of the events from those methods to find out what is going on.

Without an error message we are in the dark out here.

|||hi, i had the same problem, The reason for this execution error is that you are using the NT AUTHORITY\NETWORK SERVICE user, this is one who executes the package.

I suggest you to verify the permissions given to this user into the DB tables and also read this articles:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=607891&SiteID=17
http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx

regards|||Hi Darren,

You are indeed correct i do need to add error tracking of some description included in the WS, apologies for not having this information at first.

The WS does not trigger any exceptions, i have since added exception handling but nothing was highlighted. It returns the enumerator 1 for "Package Failed".

If possible, I'd like some further help on how to use the IDTSEvents, could you point me to any good URL's or help me yourself.

In the mean time , i have logged on to the PC as the account under which the WS is running. Upon trying to execute the package, it fails and looks as though it may be to do with access denied errors to the file system of a remote machine where files that require processing are stored and processed to. These directories have full control set for the account running the web service and as a result i believe they should be able to get access, but unfortunately don't.

I'll keep plugging away at it, but setting up the IDTSEvents may help if you can provide further info on this subject.

Thanks,

Grant|||Further to my last post. I've logged into the PC where the SSIS package resides with the same account that calls the web service. It seems to definitely be a permissions issue with the file system access. I have mapped a link to the UNC path of the folders to be processed and when i ran the DTExecUI for a second time it ran with no problems.

What i don't understand now is that despite the web service account having full control over the directories on the remote PC it still fails to see them. Any ideas why this is the case?

Cheers,

Grant|||

Handling Package Events Programmatically
(http://msdn2.microsoft.com/es-es/library/ms135967.aspx)

Loading and Running a Local Package Programmatically
(http://msdn2.microsoft.com/en-us/library/ms136090.aspx)

I woudl expect you to get an error event with a similar message to what you have seen running interactively. That is a good test method by the way.

Are you referencing UNC paths or trying to use a mapped network drive from the package normally? Mapped drives are generally a bad idea when used unattended, just use a UNC. Obviously permissions need to be correct, but I cannot suggest anything other than standard Windows permissions troubleshooting.

|||hi Darren,

Thanks for the links, i'll be sure to read through them. I think i have my problem resolved. It seems that i had to share the top level folder (where all sub folders relate to the SSIS files etc) so that it could be seen via the network. I was initially trying to use the C$ default admin share which wouldn't allow access via the web service account.

It seems to be working with the network share. I've also had to set up anonymous access on the web service to stop the constant prompting for user name and password. Is there a better way to secure the web service when it is called from another ASPNET page?

Many thanks for your help,

Grant

Executing package from C# on 64bit platform

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

sql

Executing package from C#

Hi

I have been successfully executing a series of IS packages from C# using the following syntax

Microsoft.SqlServer.Dts.Runtime.Package package;
package = app.LoadPackage(ISPackagePath + @."\myPackage.dtsx", null);
package.ImportConfigurationFile(ISPackagePath + @."\MyConfiguration.dtsConfig");
result = package.Execute();
package.Dispose();

My client has just moved their SQL boxes to a 64bit platform and this process no longer runs.

Now im also running some packages in a SQL Agent job on the SQL box and im aware of the issue of an SSIS job step defaulting to using the 64bit version of DTExec.exe. Ive updated those job steps to be operating system (cmdExec) type steps calling the 32bit version of DTExec.exe, and everything is fine there.

Is there anyway to force the Microsoft.SqlServer.Dts.Runtime.Package object in my C# code to use the 32bit version of DTEexec.exe? This code is running on the same 64bit SQL server box as the SQL agent job. Or will Microsoft.SqlServer.Dts.Runtime.Package object always default to using the 64 bit version if it is executed on a 64bit box ?

I would just call sp_start_job from C# and implement the IS packages in another job but unfortunatly my code cannot be given correct permissions to see the SQL agent jobs.

Any ideas?
Thanks

In a mixed platform environment you have to compile your code with AnyCPU instead of 32bits so just open your solutions in VS2005 and rebuild your code with AnyCPU and it will run in 64bits box. There maybe some configuration relevant to SSIS but in most mixed platform build with AnyCPU should fix it. I develop C# .NET applications with Oracle 9i/10g both 64bits with AnyCPU without any problems. Hope this helps.

|||

Caddre wrote:

I want to know about the performance using oracle packages in c#.net

Bhavin.

Bhavin_82@.hotmail.com

executing package DTExec

I created a package and stored in SQL server.

I am running it as follows

master.dbo.xp_cmdshell dtexec /sq "ABC DEF GHI" /ser "Prod"

ABC DEF GHI --> is my package name.

When i run this from a job, it runs without error

but when i run the above sql i am getting error as

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '/'.

Am i missing some thing, please advice. Is that the problem between spaces in the package name?

I missed the single quotes

master.dbo.xp_cmdshell 'dtexec /sq "ABC DEF GHI" /ser "Prod"'

executing package as job

Hi,

I am having trouble executing a package as a job in SQL Server 2005. I can run the package fine manually from sql server but when I create a job for that package and run the job I get an error that says

The package execution failed. The step failed.

I am completely lost at this point. Any help would be greatly appreciated.

Thanks
Brian

This article describes what can go wrong and how to troubleshoot it:
http://support.microsoft.com/kb/918760

Please check it out.

executing dts package through asp.net creates tables but no data!

hey out there,

just started playing with this so bare with me...

i've used the data export wizard to create a dts package in sql server 2000. the package takes selected tables, creates an access database and then dumps all the data into the tables.

it works fine when i run the package in enterprise manager, but when i call it from asp.net (vb.net) the access database gets created, the tables are created but it fails to dump the data!

this is my code:

1Public Sub executeDts()23Dim oPkgAs DTS.Package24 oPkg =New DTS.Package25Dim oStepAs DTS.Step6 Dim sMessageAs New StringBuilder789 oPkg.LoadFromSQLServer("serverNameHere","userNameHere","passwordHere", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, , , ,"packageNameHere")1011For Each oStepIn oPkg.Steps1213 sMessage.Append("<p> Step [" & oStep.Name &"] ")1415If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_FailureThen1617 sMessage.Append(" failed<br>")1819Else2021 sMessage.Append(" succeeded<br>")2223End If2425 sMessage.Append("Task """ & oPkg.Tasks.Item(oStep.TaskName).Description &"""</p>")2627Next2829 Response.Write("sMessage = " & sMessage.ToString &"<br/>")3031 oPkg.Execute()3233 oPkg.UnInitialize()3435 oPkg =Nothing3637 End Sub38

am i missing a step?! it seems very odd that the tables are created but the insert fails...

any advice anyone can offer would be great!

cheers,

jake

hi jake the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

|||

hi jake

the code is perfect but there is one tiny little error

just put the DTS.Execute() statement before theFor loop block

this should solve your problem

take care brother

happy coding

sql

Executing DTS package from VB.NET

Hello,

I have a web application that I kick off a DTS package. I tested the package and it works fine. However, when running the package through ASP.NET, I get the following error:

Type mismatch. (mscorlib (80004005): Missing parameter does not have a default value. Parameter name: parameters)\n

I've traced it to a dynamic task step. I have this as the first step in the workflow, but for some reason, it is executing last. Which is definitely not the place where I want it. Is there anyway I can force it, or why is it doing that?

Any ideas?

BrianHey,

It seems the error is because the steps aren't being executed in the right order. The steps are being sorted by name so to speak, as the execute SQL's are being fired first, then the dynamic task, then the pump tasks, etc. Is there anyway around that, either in the package or through code?

Brian|||The tasks in a DTS package will attempt to execute all at once unless you've established a workflow. With a workflow, the next task won't execute until the previous task has completed. Try this MSDN article and see if you can locate what you're missing:DTS Package Workflow.

Terri

executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.

Executing DTS Package - Error not able to find the package.

I am running Sqlserver 2005 and IS.
Here is the SP I am running.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: Name
-- Create date:
-- Description:
-- ========================================
=====
ALTER PROCEDURE [dbo].[CallRiskDataDTS]
-- Add the parameters for the stored procedure here
@.loaddate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.DTSPackageObject int
DECLARE @.HRESULT int
DECLARE @.property varchar(255)
DECLARE @.return int
DECLARE @.ErrDescrip varchar(255)
DECLARE @.ErrSource varchar(30)
DECLARE @.ErrHelpId int
DECLARE @.ErrHFile varchar(255)
DECLARE @.ErrMsg varchar(255)
Declare @.FileFilter varchar(8)
DECLARE @.sDTSPackagePath varchar(1000)
DECLARE @.sDTSSpecialUser varchar(50)
if not IsDate(@.loaddate) = 0
Begin
select @.loaddate = getdate()
End
select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(mm,@.loaddate)))),'') +
convert(varchar(2), Datepart(mm,@.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(dd,@.loaddate)))),'') +
convert(varchar(2), Datepart(dd,@.loaddate))
SELECT @.ErrMsg = 'Error running DTS package'
-- Create a DTS Package object
EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR (@.ErrMsg,11,1)
RETURN
END
EXEC @.HRESULT = sp_OAMethod
@.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolData
Upload",
Nothing)', NULL --, @.flags=256,
@.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
@.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
@.PackagePassword =''
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
OUTPUT,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrDescrip,11,1)
RETURN
END
-- Set the FailOnError property to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Set
to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
@.FileFilter --Set to true
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
-- Call the EXECute method
EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
--remove the object from memory
EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
I am getting the following message
Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
{}.{}') does not exist. I want to set this up so I can execute is from an
ASP.NET page. I am unable to find the cause of it.
In my SQL Management Studio, connect to Integration services, I see the
package in STORED PACKAGES , MSDB tree.
Any help is greatly appreciated.
ThanksFor that matter I am not able execute any package, I am getting the same
error. Thanks
Raj.
"Raj25" wrote:

> I am running Sqlserver 2005 and IS.
> Here is the SP I am running.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
> -- ========================================
=====
> -- Author: Name
> -- Create date:
> -- Description:
> -- ========================================
=====
> ALTER PROCEDURE [dbo].[CallRiskDataDTS]
> -- Add the parameters for the stored procedure here
> @.loaddate datetime
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @.DTSPackageObject int
> DECLARE @.HRESULT int
> DECLARE @.property varchar(255)
> DECLARE @.return int
> DECLARE @.ErrDescrip varchar(255)
> DECLARE @.ErrSource varchar(30)
> DECLARE @.ErrHelpId int
> DECLARE @.ErrHFile varchar(255)
> DECLARE @.ErrMsg varchar(255)
> Declare @.FileFilter varchar(8)
> DECLARE @.sDTSPackagePath varchar(1000)
> DECLARE @.sDTSSpecialUser varchar(50)
> if not IsDate(@.loaddate) = 0
> Begin
> select @.loaddate = getdate()
> End
> select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(mm,@.loaddate)))),'') +
> convert(varchar(2), Datepart(mm,@.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(dd,@.loaddate)))),'') +
> convert(varchar(2), Datepart(dd,@.loaddate))
> SELECT @.ErrMsg = 'Error running DTS package'
> -- Create a DTS Package object
> EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR (@.ErrMsg,11,1)
> RETURN
> END
> EXEC @.HRESULT = sp_OAMethod
> @.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolDa
taUpload",
> Nothing)', NULL --, @.flags=256,
> @.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
> @.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
> @.PackagePassword =''
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
> OUTPUT,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrDescrip,11,1)
> RETURN
> END
> -- Set the FailOnError property to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Se
t
> to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
> @.FileFilter --Set to true
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> -- Call the EXECute method
> EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> --remove the object from memory
> EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
> END
>
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
>
> I am getting the following message
> Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
> The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
> {}.{}') does not exist. I want to set this up so I can execute it from a
n
> ASP.NET page. I am unable to find the cause of it.
> In my SQL Management Studio, connect to Integration services, I see the
> package in STORED PACKAGES , MSDB tree.
> Any help is greatly appreciated.
> Thanks
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

Executing DTS from within SSIS

Hi All,

I have a scenario where I am executing bunch of DTS packages from within an SSIS package. These packages are saved as structured files and everything works fine. However I am concerned about one thing which is whether these SSIS/DTS packges will run fine on my production server where I dont have any SQL SERVER 2000 /2005 installed , it just has SSIS installed on it. Appreciate all help.

Thanks

I did a little research on MSDN and other websites and I think it should not be any problem executing the DTS packages from within SSIS packages on a server having just Integration Services Installed on it.

http://msdn2.microsoft.com/en-us/library/ms137907.aspx

Thanks

|||

Installing SSIS gives basic DTS suport for free, so it will already be there. Notable exceptions are the DTS AS Processing Task and Data Mining Task.

Installing Run-time Support for SQL Server 2000 DTS Packages

You can run DTS packages and SSIS packages on the same computer.

When you select Integration Services for installation, Setup also installs support for DTS packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Support in the runtime is enhanced to enable DTS packages to access SQL Server 2005 data sources.

SQL Server 2005 Integration Services Backward Compatibility
(http://msdn2.microsoft.com/en-us/library/ms143706.aspx#)

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /Esql

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell 'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /E

executing DTS - DSN error...

Perhaps this has already been answered. If so, I apologize. I'm a
newbie at this stuff. I have a DTS package to import some Visual
Foxpro data. Yes, I said Visual Foxpro. :) The DTS package works
beautifully when I rightclick on the package and choose "execute." And
it works beautifully when I run dtsrun... at the DOS prompt. But it
doesn't work when I try to execute it with code in Query Analyzer.
I've tried exec master..xp_cmdshell 'dtsrun ...' And I've tried
scheduling the package as a Job, disabling the Job and then using
msdb..sp_start_job to run it. Neither of those work.

I get a "DSN not found and no default driver specified."

My DSN stuff looks OK to me, but perhaps some of that is wrong. What
does the DSN have to look like in order for the code in Query Analyzer
to find it? If the DSN is screwed up, why would it work OK when
executed from some places, but not in others? What am I missing?
Any help would be much appreciated. :) thanks...
-emilyAlso, I should clarify. Everything is local. Everything's on my
laptop on my coffee table. No network. SQLServer is local, Foxpro
data is local.|||OK, false alarm, I figured this out myself. Well, I didn't really
figure it out, but I did get it to work. If I set up the FoxPro DSN as
a System DSN, then it works. User DSN doesn't. I have no idea why.
I'll continue to play around with it. Next time I won't post here
until I'm absolutely sure that I've tried absolutely everything. :)|||emily (ehart624@.hotmail.com) writes:
> OK, false alarm, I figured this out myself. Well, I didn't really
> figure it out, but I did get it to work. If I set up the FoxPro DSN as
> a System DSN, then it works. User DSN doesn't. I have no idea why.
> I'll continue to play around with it. Next time I won't post here
> until I'm absolutely sure that I've tried absolutely everything. :)

I have an idea. If SQL Server runs as Local System it is not likely
to find User DSN for your user. Thus it must be a system DSN.

Recall that when you run from QA, it's SQL Server that runs the
package.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Executing App with DTS, trouble with scheduling package

Hello,

I've created a test application in windows ( vb.net ). When executing this app with the DTS package it seems to work fine ( by right clicking on the package and selecting Execute Package ). It finishes successfully and does what it supposed to. But when I try to schedule the package with the SQL Server Enterprise Manager in Management > SQL Server Agent > Jobs it Fails every time. I schedule it by right clicking on the package and selecting the Schedule Package option. Does any one have an idea why the schedule keeps on failing? Thanks in advance.

Daniel B1) Locate the job (under SQL Agent)
2) Right click on the job
3) Pick View Job History...
4) Check the Show Job Details box
5) Read the error message(s)

-PatP|||This is the error message i get.

The job failed. The Job was invoked by User REPEATSEAT\dberezniak. The last step to run was step 1 (DTS Execute App).

This doesn't tell me much. It blows up when trying to execute the app.
( The application is test and does work on its own and within the package. )|||Did you click on the "Show Job Details" box to be sure that it is checked? That message sounds like what you'd get if that box wasn't checked.

-PatPsql