Runtime error 713

Dear Gurus,

I have developed a package with VB6 & crystal report10. While installing it in Client machines i get error as "Run time Error 713".
I searched for it but only found that it is " Appication - Defined or Object define error 713"..Help me to solve this asap..

Thanx in Advance,
RevolutionWhen creating the Package did you add all the required dlls?
Also search here for your solution
http://support.businessobjects.com/

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System:tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

Running Time of Package to be logged in the Email Notification

Hi,

I want to include the running time (Start Time and End Time) of the Package in my script task that sends out an email after job completion.

How do I get the start time and end time?

thanks a lot

cherriesh

There is a system variable called StartTime that will give you the start time. For the Endtime; you could use an execute sql task to get the time from the DB (e.g. Select getdate()) and put that value in a SSIS variable.|||You can also use the ContainerStartTime system variable, which should reflect the start time of the current task. The difference between StartTime (which is at the package level) and ContainerStartTime of your SendMail Task will give you the runtime up to that task.|||

is this how i retrieve the system variable?

Dts.Variables("System:tartTime").Value.ToString

Because I'm having an error:

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

cheriesh

|||

Make sure you have either added it to the ReadOnlyVariables property in the Script property pages, or lock it in the script using the VariableDispenser (the better option).

Here's a post that explains why using the VariableDispenser is better:

http://blogs.conchango.com/jamiethomson/archive/2007/08/28/Beware-of-variable-usage-in-script-tasks.aspx

Code Snippet

Dim var As Variables

Dts.VariableDispenser.LockOneForRead("StartTime", var)

MsgBox(var(0).Value)

var.Unlock()

Running the package through C# UI

one more guidance needed with regard to the Quotation marks

path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

jobCommand = new SqlCommand(@."xp_cmdshell 'dtexec /f "'path '" /Set \package.Variables[User::ArchiveFileType].Properties[Value];""Excel""'", cconn);

How Do i set the path's value in c# the quotation marks are wrong again. cos i want to set value of path according to selection in the UI.

Select("deviceUniqueId = '" + deviceUniqueId + "'"); is an e.g of Doing in C# but when i implement it through text it does not recognize

Thanks

Jas

You would normally use \ to escape a ". Remove the as that means you I cannot use the escape char, which I think you need to deal with teh quotes. Just escape the slashes as well.

string var = "This has a quite in, \", see and \\ is really a single slash?";

|||

Hi!

I know that \ is an escape character but what actually happens here is

string path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

i need to change this value accordingly.when i try to execute this statement

jobCommand = new SqlCommand(@."xp_cmdshell 'dtexec /f '" + path + "''", cconn);

i am getting this result - @."xp_cmdshell 'dtexec /f 'D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx''"

which is correctly getting the value of path but when i say

jobCommand.ExecuteNonQuery(); It fails because there is a single qoute which is set before the path value 'D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx' i think is the problem

Message @."Incorrect syntax near '\'."

What should i do i really need to get the value some thing like this

@."xp_cmdshell 'dtexec /f \"D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx\"'"

Thanks,

Jasmine

|||

If you know about escape characters that is good, but you are specifying @. which means escape sequences are not processed. Your previous version had doubel quotes which had not beeen escaped, so double them or use traditional escape syntax without the @.. You are trying to mix the two methods, you cannot us \ when you have @. in front as I tried to explain above.

Try -

string path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\"'", cconn);

The evaluated command below looks good for SQL and also the DTEXEC path itself.

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx"'

|||

Hi!

Thanks For your reply!

but this is very urgent please help!!!!!!!

I need one more help in this issue. what if i do not need any \ "

for e.g: i am trying to set conn string and varaible value

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\" /Conn \"" + Packconn + "\" \"" + connect + "\" '",cconn);

i am getting some value like this :

CommandText "xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\" /Conn \"SE413695\\AASQL2005.TestDB;\" \"Provider=SQLNCLI.1;Data Source=SE413695\\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;\" '" string

I do not need the highlighted escape characters in it. what should i do?

i need some thing like this :

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn SE413695\AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'

Thanks,

Jas

Running the package through C# UI

one more guidance needed with regard to the Quotation marks

path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

jobCommand = new SqlCommand(@."xp_cmdshell 'dtexec /f "'path '" /Set \package.Variables[User::ArchiveFileType].Properties[Value];""Excel""'", cconn);

How Do i set the path's value in c# the quotation marks are wrong again. cos i want to set value of path according to selection in the UI.

Select("deviceUniqueId = '" + deviceUniqueId + "'"); is an e.g of Doing in C# but when i implement it through text it does not recognize

Thanks

Jas

You would normally use \ to escape a ". Remove the as that means you I cannot use the escape char, which I think you need to deal with teh quotes. Just escape the slashes as well.

string var = "This has a quite in, \", see and \\ is really a single slash?";

|||

Hi!

I know that \ is an escape character but what actually happens here is

string path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

i need to change this value accordingly.when i try to execute this statement

jobCommand = new SqlCommand(@."xp_cmdshell 'dtexec /f '" + path + "''", cconn);

i am getting this result - @."xp_cmdshell 'dtexec /f 'D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx''"

which is correctly getting the value of path but when i say

jobCommand.ExecuteNonQuery(); It fails because there is a single qoute which is set before the path value 'D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx' i think is the problem

Message @."Incorrect syntax near '\'."

What should i do i really need to get the value some thing like this

@."xp_cmdshell 'dtexec /f \"D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx\"'"

Thanks,

Jasmine

|||

If you know about escape characters that is good, but you are specifying @. which means escape sequences are not processed. Your previous version had doubel quotes which had not beeen escaped, so double them or use traditional escape syntax without the @.. You are trying to mix the two methods, you cannot us \ when you have @. in front as I tried to explain above.

Try -

string path = @."D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx";

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\"'", cconn);

The evaluated command below looks good for SQL and also the DTEXEC path itself.

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveTicket.dtsx"'

|||

Hi!

Thanks For your reply!

but this is very urgent please help!!!!!!!

I need one more help in this issue. what if i do not need any \ "

for e.g: i am trying to set conn string and varaible value

jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f \"" + path + "\" /Conn \"" + Packconn + "\" \"" + connect + "\" '",cconn);

i am getting some value like this :

CommandText "xp_cmdshell 'dtexec /f \"D:\\SSISProject\\Integration Services Project1\\ArchiveMainMultiTables.dtsx\" /Conn \"SE413695\\AASQL2005.TestDB;\" \"Provider=SQLNCLI.1;Data Source=SE413695\\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;\" '" string

I do not need the highlighted escape characters in it. what should i do?

i need some thing like this :

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn SE413695\AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'

Thanks,

Jas

Running the Package input file .xmla

Hi,

Is there a way to run the package input .xmla file to the command prompt instead of running it in management studio?

cherriesh

Try the ascmd command-line utility, part of Analysis Services Administrative Samples.

http://www.microsoft.com/downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e&DisplayLang=en

sql

Running SSIS Packages in Management Studio

We are using SQL Server 2005 Standard edition.We have the development studio installed on a developer’s workstation.The SSIS package is accessing network folders using UNC and the tasks in packages are accessing SQL Server with a SQL Server id.

The SSIS package runs fine on the workstation. We deploy the SSIS package as a SSIS store on SQL Server.I receive Error 18456 Severity 14 State 8 error when I run the SSIS package as a SQL Agent job.

When I run the SSIS package from the Integration Services Management Studio, I get The AcquireConnection method call to the connection manager "dboSQLabm" failed with error code 0xC0202009.

I’m sure this is a permission based problem since it runs fine on my workstation.I tried logon onto the SQL Server with my id (I’m an admin to the server).I tried running the agent with my windows id.It looks like I’m getting problem connecting to the SQL Server tables since the logs are properly written to the network drive.

In SQL DTS 2000, all we needed to do was give the SQL agent id permission to the network folder. I tried to search the forums, but none of the solutions seem to work for my situation.

Do you use config files and do not save sensitive? This should take care of this issue.

There are quite a few posts about it in this forum

Philippe

|||The problem I was getting occurred whether I used a config file or not. I had the package set to EncryptSensitiveWithUserKey. I created another thread to get production deployment suggestions. We don't want our DBAs using BIDS to edit SQL connection manager settings when they migrate SSIS packages to production.|||

Then don't let them. Use configurations and the deployment wizard.

-Jamie

running ssis package with ssis run time compoenents and sql server 2000...

Hi Jamie,

thanks.

I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs.

Thanks,

$wapnil

spattewar wrote:

I have similar concerns. Do you have any links or infromation pages that we can read through and provide it to our DBAs.

About what exactly?

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with ssis run time compoenents and sql server 2000...

running ssis package with ssis run time compoenents and sql server 2000...

Is it possible to run ssis packages that point to servers on sql server 2000
without installing sql server 2005 ?

Can we just install runtime for ssis and run the packages ?

Please explian with links if possible

thanks a lot

Simple answer. Yes, SSIS can access SQL Server 2000. And yes, you can install SSIS only without SQL Server databsae engine.

-Jamie

|||

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

|||

spattewar wrote:

Hi,

Something like how to do the installation of the SSIS package with the entire sql 2005 server engine? How to install it besides the SQL 2000 server? Are there any configurations to be done while installing SSIS or any setting in any config files to make them work toghether.

Thanks,

$wapnil

The install is fairly straight forward. I don't think there are any special switches that you need to flick under the circumstances that you talk about.

One thing, If you you are installing a named instance of the SQL Server engine then you will need to change the SSIS Service configuration. See here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/06/08/15765.aspx

-Jamie

|||

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want me to create a new thread for this then let me know.

Thanks for your time.

$wapnil

|||

spattewar wrote:

Thank you.

But If I have to run a SSIS package with SQL 2K instance? Where will be the package stored - in msdb? or it can run if from a file?

If you want to store the package in msdb then you will need a SQL 2005 instance.

Although yes, you can run from a file, and in that scenario you don't need ANY SQL Server instances. I always choose to store in files by the way.

spattewar wrote:

If you want me to create a new thread for this then let me know.

No worries. I can split the thread if necassary.

-Jamie

|||

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

|||

spattewar wrote:

This is great.

So to just summarize.

1) On the production machine that I have SQL 2000 server installed, I will install SSIS component only.

2) Develop the SSIS packages on my machine using VS 2.0 and SSIS.

3) Save the packages in files with *.dtsx and configuration files *.xml

4) Move the files to the production machine and execute the files using DTExec.

IS this correct

Thanks again.

$wapnil

It sounds fine to me, yes.

-Jamie

|||thanks guys.this is exactly what I was looking for.|||

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

|||

rao_ssis_dts wrote:

This thread help me to find some answers. We have several clients and some of them are still with SQL 2000. I was asked to develop ETL which needs to work on SQL 2005 and SQL 2000. Are SSIS runtime components are downloadable with free of charge or is it possible that we can package with our installer (are these can be redistributable with free of charge). Answer to this question helps me whether I need to develop ETL using DTS or SSIS. I am not sure whether I need to post this one as a seperate thread or not.

Thanks

SSIS is not free, it is not downloadable; it comes part of SQL Server Standard, Developer, or Enterprise editions. SSIS is not redistributable either, unlike DTS. A license is required wherever SSIS may be installed.|||

Thank you.

I guess then I don't have choice, I have to write ETL using DTS.

|||

Hi Jamie ,

I am facing the same scenario as above ,with the benefit of a client that is willing to pay for CAL's for ssis ,is there any way I can bootstrap the install of SSIS into a install for an application

Thanks in advance

Cedric

running ssis package with a remote sql agent?

hi all,

thanks for the valuable information all the time!!! saved me a lot of time...

our team developes a system for text data improvement using ssis .

we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .

my question is:

what is the best way to do this?

if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?

or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.

do we need to install sqlserver on these "ssis servers"?

thanks for your replies!

To use SQL Server Agent in this scenario, all you need to do is ensure that SQL Server Agent is installed and accessible somewhere on the network. It need not be installed on your main SQL Server or the SSIS servers.|||

thanks for your reply! could you be more specific?

how do i use sql agent if it is installed on a different machine, to run the package on my computer?

i just found in kirk haselden's book a "caution" on this subject, it says:

"SQL Server Ajent service relies on the sql server service. if you intend to use sql ajent to schedule package execution, you need to have SqlServer installed on the machine as well, if you have some policies that don't allow SqlServer on the same box with integration services or otherwise govern the installation of SqlServer that prohibits you from running both servers on the same machine, you need to use the windows task scheduler or some other tool..."

can you explain this to me?

|||

That means that both services: SQL Server agent and SSIS needs to be running in the same machine where you are trying to execute he package via SS Agent. So you have 2 options, install the SS agent & SSIS in your computer or install the SSIS only and use other job scheduler tool.

May I ask why you want to schedule a job from your PC instead from a server?

|||

hi rafael, thanks, that as what i thought.

the reason to run the packages on a different machine is the load balancing- we are talking about a very heavy process, that is supposed to be working repeatedly, all the time. we don't want the SQL server performence to slow down.

what do i loose if i don't use the Agent to run these proccesses?

|||

rebecca M wrote:

what do i loose if i don't use the Agent to run these proccesses?

I would say the ability to put it under a schedule.

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

|||

Rafael Salas wrote:

I would say the ability to put it under a schedule.

and what about monitoring, logging, and such?

Rafael Salas wrote:

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

|||

rebecca M wrote:

and what about monitoring, logging, and such?

The looging information provided by SQL server agent would be about if the job was executed succesfully or not. Do not confuse with the SSIS logging that is define inside of the packages.

rebecca M wrote:

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

I *think* SQL Agent requires DB componnets and I think you would require a license on that box as well.

|||SQL Server Agent relies on the SQL Server Service. The SQL Server Service is part of the SQL Server Database Engine. Therefore, SQL Server Agent cannot operate without SQL Server Database Engine.sql

Running SSIS package using .Net code

I am running this code my question is when it runs ExecuteNonQuery() it doesn't wait for package to complete It just returns "Package succeeded' - The way you can test if you run this code 2 times one after another. ExecuteNonQuery() breaks with error -

SQLServerAgent Error: Request to run job RunSsisPkg (from User Domain\User) refused because the job is already running from a request by User Domain\User.

How can I check before return package is running and wait to complete before return?

using System;

using System.Data;

using System.Data.SqlClient;

namespace SSISRun

{

class Program

{

static void Main(string[] args)

{

SqlConnection jobConnection;

SqlCommand jobCommand;

SqlParameter jobReturnValue;

SqlParameter jobParameter;

int jobResult;

jobConnection = new SqlConnection("Data Source=(local);Initial Catalog=msdb;Integrated Security=SSPI");

jobCommand = new SqlCommand("sp_start_job", jobConnection);

jobCommand.CommandType = CommandType.StoredProcedure;

jobReturnValue = new SqlParameter("@.RETURN_VALUE", SqlDbType.Int);

jobReturnValue.Direction = ParameterDirection.ReturnValue;

jobCommand.Parameters.Add(jobReturnValue);

jobParameter = new SqlParameter("@.job_name", SqlDbType.VarChar);

jobParameter.Direction = ParameterDirection.Input;

jobCommand.Parameters.Add(jobParameter);

jobParameter.Value = "PackageName";

jobConnection.Open();

jobCommand.ExecuteNonQuery();

jobResult = (Int32)jobCommand.Parameters["@.RETURN_VALUE"].Value;

jobConnection.Close();

switch (jobResult)

{

case 0:

Console.WriteLine("Package succeeded.");

break;

default:

Console.WriteLine("Package failed.");

break;

}

}

}

}

I believe the RunningPackage class can help you.

http://technet.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.aspx|||

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

|||

Ashok Ojha wrote:

Is it true that to use Microsoft.SqlServer.Dts.Runtime I need to have SSIS services in the box? I am looking for a way to run SSIS package without having SSIS service in the box.

The other option using web service is really not working I have one post on that.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1949366&SiteID=1

Thank you - Ashok

In SQL Server 2005 you cannot because we were telling developers to run data automation with DTS packages, Replication was threatened but you can do it in 2000 and it works like a charm.

|||

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

|||

jwelch wrote:

I don't believe you have to have the SSIS Service running to run packages, but you do have to have a license for SSIS on the computer where you will be running the packages, and you will need to install the SSIS components.

In 2000 the the DTS runtime is in the package it is not the same, it is one of the changes in the product I have used DTS from 7.0

|||

Thank you. I am using SSIS so it's sql 2005. What I understand that I can install sql server 2005 and SSIS services in server "A" with license and my server "B" which runs ETL can have this .net process which is using "Microsoft.SqlServer.Dts.Runtime" dll can run .dtsx files in Server "A" without having SSIS service/license in server "B". right?

- Ashok

|||

Ashok, Server B requires a license as well to get the Microsoft.SqlServer.Dts.Runtime dll.

The SSIS components are not redistributable like they were in DTS.

|||

Hi Phil,

How the SSIS components license and SSIS Services license works. I have to recheck installation of SSIS is it while installing SSIS I can pick only SSIS components or Service.

Thank you - Ashok

|||Just install the SSIS components. I think it's just one checkbox. You should not need the service, if you can ignore that.

Running SSIS package from SQL Job

I'm trying to run a SSIS package (dtsx) from inside an sql job (SQL Server agent). This works fine if the user running (run as) the step is a local admin on the server. If it's not, I get the error message "The package could not be loaded. The step failed". This happens even if the user has all possible serverroles such as "sysadmin" etc in SQL.

So, my question is, is there any way to load an SSIS package without being local admin on the machine? In case it is, what is needed?

regards Andreas

I'm not sure that this will solve the problem, but have a look at PROXY

and CREDENTIALS - I was able to invoke a sample package using them,

using a user that was only a member of the USERS local group.|||

GethWho wrote:

I'm not sure that this will solve the problem, but have a look at PROXY

and CREDENTIALS - I was able to invoke a sample package using them,

using a user that was only a member of the USERS local group.

here's a sample of what I did:

--code

--###################################################### describe

script

############################################################################

PRINT '>>> This script creates the LOGIN,

USER , CREDENTIALS and PROXY for INTEGRATION SERVICES SSIS PACKAGES

<<<'

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] started at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

PRINT ''

PRINT '>>> Create Objects <<<'

PRINT ''

--################################################## Check and Drop

Existing

########################################################################

--################################################## CREATE Login

###################################################################################

SET NOCOUNT ON

USE [master]

IF NOT EXISTS (select 1 from sys.syslogins WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE LOGIN [LocalMachine\TESTSSISUser] FROM WINDOWS WITH DEFAULT_DATABASE = [MyDatabase]

--################################################## CREATE User and

Grant Rights on DBS

############################################################

USE [MyDatabase]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DataReader], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DataWriter], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [DB_DDLAdmin], [LocalMachine\TESTSSISUser]

GRANT EXECUTE ON [sp_dts_addlogentry] to [LocalMachine\TESTSSISUser]

USE [msdb]

IF NOT EXISTS (select 1 from sys.sysusers WHERE [Name] = 'LocalMachine\TestSSISUser')

CREATE USER [LocalMachine\TESTSSISUser] FROM LOGIN [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsadmin], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsltduser], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [db_dtsoperator], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentOperatorRole], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentReaderRole], [LocalMachine\TESTSSISUser]

EXEC SP_ADDROLEMEMBER [SQLAgentUserRole], [LocalMachine\TESTSSISUser]

--################################################## CREATE Credential

##############################################################################

USE [master]

IF NOT EXISTS (select 1 from sys.credentials WHERE [Name] = 'TestProxy')

CREATE CREDENTIAL [TestProxy] WITH IDENTITY = 'LocalMachine\TestSSISUser', secret = 't3st'

--################################################## CREATE Proxy

###################################################################################

USE [msdb]

DECLARE

@.proxy_name SYSNAME,

@.subsystem_name SYSNAME,

@.UserName SYSNAME

SET @.proxy_name = 'TestSSISUser'

SET @.subsystem_name = 'CmdExec'

SET @.UserName = 'LocalMachine\TestSSISUser'

EXEC sp_enum_proxy_for_subsystem @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

IF @.@.ROWCOUNT = 0

EXEC SP_ADD_PROXY

@.proxy_name=@.proxy_name, @.credential_name='TestProxy'

EXEC sp_enum_login_for_proxy @.proxy_name=@.proxy_name, @.name=@.UserName

IF @.@.ROWCOUNT = 0

EXEC SP_GRANT_LOGIN_TO_PROXY

@.login_name=@.UserName,

@.proxy_name=@.proxy_name

EXEC SP_REVOKE_PROXY_FROM_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

EXEC SP_GRANT_PROXY_TO_SUBSYSTEM @.proxy_name=@.proxy_name, @.subsystem_name=@.subsystem_name

--###################################################### end script

##################################################################################

PRINT '>>> This execution on server:

['+@.@.SERVERNAME+'] ended at:

['+CONVERT(VARCHAR,CURRENT_TIMESTAMP,113)+'] <<<'

--code

.. it isn't perfect - I couldn't find a way to do an existance check

for the PROXY to run the SP_REVOKE_PROXY_FROM_SUBSYSTEM only if it

PROXY had been gtranted.|||

There will be a reason for the package failing to load, and this is what you need to find out. Use the CmdExec step type.

Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)

Running SSIS package from asp.net application failed

I have an asp.net web application and a web service (both of them are created in VS 2005 - asp.net 2.0). They are located on the same web server. In both web.config files, I have set <authentication mode="Windows"/> and <identity impersonate="true"/>. Also, configured the IIS settings to use Integrated Windows Authentication and unchecked the Anonymous access (for both). The web service is called from the web app, so I have to pass credentials for authentication to the web service. The web service loads and executes a SSIS package. The package and all the other sql objects are located in the sql server 2005 (windows server 2003 - the same server as the web server).

When run the web service from develop environment (vs. 2005), I get whatever I expected. When call it from web application, however, the package failed (no error message).

In the SSIS package, there are three connection managers –

· A: Microsoft OLE DB Provider for Analysis Services 9.0 à connectionType=OleDbConnection

· B: .Net Providers \ SqlClient Data Provider à connection type=SqlConnection

· C: Native OLE DB \ Microsoft OLE DB Provider for SQL Server à connectionType=OLEDB

After ran the web application and check the sql database, I can tell that the package was reached and when through the first two steps (clear some records in table_1 and extract some records from table_2 ) which relate to the connection manager B – ADO.Net connection. The remaining steps failed which are related to the connection managers A & C.

From SSIS package log file, found that the user credentials (domain and username) were correctly passed from web service to sql server 2005 at the first two events, but the credentials (or operator) changed from domainABC\user123 to NT AUTHORITY\NETWORK SERVICE after packageStart. Then, it complains … either the user, domainABC\serverName$, does not have access to the database, or the database does not exist.

I think the credentials are passed ok but some setting related to the Analysis services are not correct - complaining start from there. Any clues?

Please help and thank you all!

FYI: The problem was solved.

What I did are list as below:

A. We created a new web app pool and pointed the both web application and web service to this web app pool (which was configured not use default identity - Predefined: Network Service but use Configurable - created a new use name).

B. Under SQL server , added this new user and assign the certain rights to it.

C. In the web application and web service, set impersonate=false (instead of true)

D. In the SSIS package, we also have a flat file destination connection which is to write the output to .txt file. I give the read & write rights to this new use. then, it works.

Running SSIS package (.dtsx) from a ASP.Net web service

Where do I find about running an SSIS package (.dtsx) from a ASP.Net web service, particularly issues to do with permissions

cheers

Rob

Hi Rob,

There is books online entry that gives an example of how to do this.

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

Also note the comments in the Community Content at the bottom. It has some important notes about security and thread impersonation.

~Matt

Running SSIS package (.dtsx) from a ASP.Net web service

Where do I find about running an SSIS package (.dtsx) from a ASP.Net web service, particularly issues to do with permissions

cheers

Rob

Hi Rob,

There is books online entry that gives an example of how to do this.

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

Also note the comments in the Community Content at the bottom. It has some important notes about security and thread impersonation.

~Matt

sql

Running SSIS package

My boss want to close access to xp_cmdshell for any logins including sa.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.

In this situation (see above) I have two questions:

1. Is it possible to run SSIS package by SQL Job without corresponding utility?

2. Is there any replacement of the osql.exe utility in order to run file containing sql script? I mean may be SSIS can load the file and run content of this file by SQL Task?

Let's discuss.

Any ideas will be greately appreciated.

Vitaliy

Yes - to both questions.

Running SSIS package

My boss want to close access to xp_cmdshell for any logins including sa.
He afraid of possible fraud when somebody having sa login will be able to make operations on the company's network.

In this situation (see above) I have two questions:

1. Is it possible to run SSIS package by SQL Job without corresponding utility?

2. Is there any replacement of the osql.exe utility in order to run file containing sql script? I mean may be SSIS can load the file and run content of this file by SQL Task?

Let's discuss.

Any ideas will be greately appreciated.

Vitaliy

Yes - to both questions.

Running SSIS from Command Line w/ SQL Agent

Hi Everyone!

I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /F "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks in advance!

Anthony Akin

Anthony, the " directly after dtexec.exe looks rogue to me.

-Jamie

|||

Jamie,

Thanks for the quick reply! A little background might help! I'm running Windows Server 2003 x64 and SQL 2005. We oringinally were recieving errors because of the BIDS trying to run the 64 bit ODBC's and all the information on the web said to run it from the command line. I can run the package listed below from the command line, but I cannot use SQL Server Agent to running this command. Any thoughts or suggestions would be greatly appreciated!

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"/FILE "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks,

Anthony Akin

|||

Sorry yeah you're right. The command-line syntax looks good.

What's the error message? usually if something works from dtexec.exe but not SQL Agent it will be a permissions issue.

-Jamie

|||

Jamie,

Here is what is in the log file. Is it because I'm calling out the 32bit dtexec with a 64bit program?

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/17/2007 13:00:08,Customer_Master,Error,0,CRMPSQL,Customer_Master,(Job outcome),,The job failed. The Job was invoked by User IBTINC\Administrator. The last step to run was step 1 (CustomerMaster).,00:00:00,0,0,Anthony Akin,,,0
04/17/2007 13:00:08,Customer_Master,Error,1,CRMPSQL,Customer_Master,CustomerMaster,,Executed as user: CRMPSQL\SYSTEM. The step failed.,00:00:00,0,0,,,,0
04/17/2007 13:00:08,Customer_Master,Unknown,1,CRMPSQL,Customer_Master,CustomerMaster,,Microsoft (R) SQL Server Execute Package Utility<nl/>Version 9.00.3042.00 for 32-bit<nl/>Copyright (C) Microsoft Corp 1984-2005. All rights reserved.<nl/><nl/>Option "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" is not valid.,00:00:00,0,0,,,,0

Thanks

Anthony Akin

|||

No idea. You kinda need to get hold of the output from dtexec.exe

You should also try executing using dtexec.exe from the command-line as the same user urnning SQL Server Agent.

-Jamie

|||

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

I think you can, yeah. I'm not sure, I'm no expert on SQL Agent.

-Jamie

|||

Jamie,

I decided on build .bat files instead of messing around with SQL server agent. Thanks for the input today!

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

It may be too late to help you on this, but for future reference - you can run the command prompt as a different user by right-clicking on the short-cut (usually located under Accessories) and choosing Run As...

Running SSIS as an Agent job using a proxy

Hello,
I built a simple SSIS package using BIDS.
The package does a backup of a DB to a network share.
I imported the dtsx file into SQL Server using SQL Server WB (connected to
the SSIS server)
I created a SQL Agent job to run the SSIS package.
I have created a credential that maps to a domain account (BackupUser1). The
domain account has access to the network share: I verified it by running a
command line using the Run As and accessing the network share as that user.
Of course, I have a sql agent proxy associated to the credentials which is
assigned to run the job step that executes the SSIS package.
When the job runs it fails because of access denied to the network share.
To see what actually happened, I changed the permissions and the share (and
the NTFS permissions) to allow R/W access to 'everyone'.
I re-scheduled the job and this time it succeeds. I went over to the backup
file that was created when the SSIS package executed and discovered that the
owner (i.e. the creator) of the file is not the domain account (BackupUser1),
rather it's the data base server computer account name DBSRV$
this means that SQL Server Agent didn't impersonate the account defined by
the SQL Agent proxy.
I looked for information and wasn't able to find an answer...
A couple of things I made in the local security policy (user rights
assignment):
allowed the specified domain account the permission to log-on as a batch job
allowed the SYSTEM principal (the agent runs under LocalSystem) to replace
process level token (i.e. impersonate, at least as I understand this user
right)
Any thoughts, suggestion on why SQL Server Agent doesn’t actually use the
credentials associated with the proxy account to access the network share?
Thanks,
Asher
Hello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Asher,
As for this issue, I've found your another new thread in the following
newsgroup:
Subject: Running SSIS pakcage as an agent job using a proxy
Date: Thu, 21 Sep 2006 02:58:02 -0700
Newsgroups: microsoft.public.sqlserver.server
I've posted my reply there with some suggestion. Please feel free to
followup there if you have any further finding or questions.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.