Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

Packages run faster on BIDS and slower on SQL Server

Hi,
I am surprised to see that in Business Inteligence Studio debugging mode, my packages take shorter. And when I run it through Agent on the SQL Server where data actually resides, it take around 70% longer time.

The package is now very closed to data and database engine itself, in BIDS it wasnt.

Anybody knows why this happen ? Do I need to tune up something ?

Any input in this will sincerely be appreciated.When you're using SQL Agent, are you using the SSIS subsystem or the command subsystem (DTExec)? Do you have the SSIS service started ?

Package wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

Package taking 100% of the CPU when it is opened for editing in visual studio

I have a package that I was able to edit a week before. But now it is consuming all CPU memory (100%) and not letting me to edit the package (When I try to edit that it says Visual Studio Is busy even after an hour waiting).

Even though I have not changed anything, the package is behaving like this.

I would appreciate any reply on this?

Thanks in advance

What has changed between two runs? Did you install anything on your machine? What is the packaged doing -- any dependancy on external resources?

Thanks,

Bob

|||Hello Bob,
Thanks for your interest.

No change between two runs and I have not installed/Uninstalled any thing.
All other packages are working fine but only that package is taking lot of time.
Today I able to open the package and even able to select the components(Transformations) but It takes lot of time to respond.Around 5 minutes it takes if I switch from one dataflow to another.

|||

You have to figure out what is causing this delay. Copy your package into an temporary file and start cutting it piece by piece.

There is not much help we can offer you based on the amount of information you provided. Try to nail down your issue and come back with a more specific question.

Thanks,

Bob

|||I have the same issue, was there ever a resolution to this?|||

Hello,

I found the problem after waiting thrice for around 30 minutes to get some control on to edit the package. In first two attempts I had to manually stop the visual studio as it was taking longer than 30 minutes. But in third attempt I could edit the package after 40 minutes of waiting and even then it was taking 2 minutes for a single click.

The problem was all my source connections were replaced By Destination connections. L

Description:

In the package I had two connections one pointing to a source database and another pointing to a destination database (this has different schema than source database).

So now all source DB connections are replaced by destination DB connections.ie Package was referring to a destination database connection but fetching the data from source database connection.

The following figure illustrates it best.

Sorry could insert a picture into the postL

Interesting thing here is the package is not giving any design time errors about referred view not present in the database it is referring to.

Due to some restrictions, I have not run the package to check whether it executes or not. But I did not get why this replacement has been done by itself.

Now I got everything corrected except a dataflow.

Another problem:

For the dataflow I left uncorrected, again the package is behaving in the same manner (consuming all CPU) when I try to fetch the data from a view present in the source database.

But this is not package specific. Whenever I tried to refer to this view from any other package then also same problem occurring. And this problem was not there in the week before run.

PACKAGE START / PACKAGEEND In SSIS

This is a repeat listing - third time - of this problem.

Here's the deal:
If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.

When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.

In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.

If this is the case, can someone explain why they aren't getting logged?

I've seen other people have run across the same issue...This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.|||I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?

Thanks!|||I haven't heard anything about SP2. Sure would be nice if they someday finished the application they rolled out.|||This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.I didn't know that. Gives me a nice "after the fact" sense of smugness that I have so far been able to avoid using the pesky thing entirely.


I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?

Got any links? I would like to keep up even if I still come to the conclusion that it is more hassle than it is worth.|||Here ya go:

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en|||To any and all interested parties:

I eventually got a response from the Mothsership herself...see below:

I believe the reason the package in the linked thread was working after SP2 is that it was using an Execute Package task, while you are using a script task. The problems you're seeing don't seem to have anything to do with your custom logger - I was able to reproduce the issue using a simple package with a SQL Logger configured.

The PackageStart and PackageEnd events are special, in that they are always fired, regardless of filtering. However, it looks like executing a package through the script task stops the event from actually being propogated up. I'm unable to determine the cause right now, but I will log an internal bug for further investigation.

As a workaround, could you instead fire a custom event from inside the script task, right before you execute the child package?

This response came directly from someone at Microsoft...

The squeaky wheel does indeed get the grease! I guess we can keep our eyes hopefully peeled for a HotFix maybe...or at least getting this addressed in a future Service Pack.|||...I'm unable to determine the cause right now, but I will log an internal bug for further investigation. One step closer to ass-completeness.|||Man...you crack me up seriously!!!!

Kudos, Blindman ;)

Package Runs in 1 minutes in Visual Studio but takes 5+ (sometimes hanges) as Job

I have an SSIS package that when run from Visual Studio takes 1 minute or less to complete. When I schedule this package to run as a SQL Server job it takes 5+ and sometimes hangs complaining about buffers.

The server is a 4 way 3ghz Xeon (dual core) with 8GB ram and this was the only package running.

When I look in the log I see that the package is running and processing data, although very very very very very slowly.

Has anyone else experienced this?

Thanks for your help

Marcus

You need to identify what the differences are between the two executions. It could be network latency, utilisation of the box by other processes, the user that you're running it as....anything.

What happens when you execute the package on the Xeon using dtexec.exe rather than thru SQL Server Agent. Do you get the same?

-Jamie

|||

I don't klnow what is going on with my system. The above package has ran find since I posted this message. I'm now having issues with stored procedure that is being ran from a job.

I can run the SP from Management Studio and it completes. The CPU does spike above 90% but it completes.

When I execute the same SP from job I get the following error and ALL connections to the database are killed.

Msg 64, Sev 16, State 1: TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01]
Msg 64, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]
Msg 10004, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]

I have taken the SQL out of the SP and tried to execute it directly from within the job and it still fails wth the same issue.

I have no idea what is going on or where to start looking.

Here is a copy of the SP that I'm trying to run from a job.... Help... I'm glad it's a Friday

CREATE PROCEDURE [dbo].[sproc_LoadData_FactMessageOpen] AS

SET NOCOUNT ON
TRUNCATE TABLE [dbo].[FactMessageOpen]

INSERT INTO [dbo].[FactMessageOpen] (

[DateID],[TimeID],[MailingID],[ProfileID],[MessageFormatID],[TransactionDateTime])
SELECT
(SELECT DateID FROM dbo.DimDate WHERE DateAK = CAST(CONVERT(varchar(10),X.TransactionDateTime,101) AS smalldatetime)) AS DateID,
(SELECT TimeID FROM DimTime WHERE Hour12 = LTRIM(SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))-2,2)) AND Minute = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+1,2) AND NoonFlag = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+3,2)) AS TimeID,
(SELECT MailingID FROM dbo.DimMailing WHERE MailingAK = X.MailingAK) AS MailingID,
(SELECT ProfileID FROM dbo.DimProfile WHERE ProfileAK = X.ProfileAK) AS ProfileID,
(SELECT MessageFormatID FROM dbo.DimMessageFormat WHERE MessageFormatAK IS NULL) AS MessageFormatID,
X.TransactionDateTime
FROM
(
SELECT
ER.TEMPLATE_ID AS MailingAK,
ER.EMAIL_ADDR_ID AS ProfileAK,
MIN(ER.ACTION_DT) AS TransactionDateTime
FROM
TEST.MDB.EMAIL_RESPONSE ER
JOIN
TEST.MDB.ACTION_SUB_ACTION_LOOKUP AL
ON
ER.ACT_SUB_ID = AL.ACT_SUB_ID
WHERE
ACTION_CD = 'G'
GROUP BY
ER.TEMPLATE_ID,
ER.EMAIL_ADDR_ID
) AS X
ORDER BY
DateID,
TimeID

|||Please share your connection strings... Could it be that the user running the job (the user assigned to the SQL Server service account) doesn't have access to the remote server/database?|||90% cpu utilization seems like too much. I'm getting the impression that you haven't examined the query execution in SQL Server Profiler. Correct?|||

Hi

I actually ran the query through SQL Profiler and it didn't come back with any recommendations. I alos looked at the explain plan and every node is using an index.

does the query look poorley written?

thanks

Package 'Microsoft SQL Management Studio Package' failed to load

i am working on sql server 2005. i removed dotnet framework 2.0 from my
system and again i installed it . Now i am getting a message Package 'Microsoft SQL Management Studio Package' failed to load , if i click
on ok it is closing.
so please give a solution and it is a trail version of 180 from
microsoft technologies

Quote:

Originally Posted by chandrasekharallu

i am working on sql server 2005. i removed dotnet framework 2.0 from my
system and again i installed it . Now i am getting a message Package 'Microsoft SQL Management Studio Package' failed to load , if i click
on ok it is closing.
so please give a solution and it is a trail version of 180 from
microsoft technologies

It wasn't a wise decision to uninstall .NET 2.0, as SQL Server is deeply integrated with .NET 2.0.

Try reinstalling SQL Server, but I'm not sure if it will correctly identify your trial period.

Package instantiation

If i open a package inside Visual Studio 2005, what does this action mean? In another word, does it mean that, the package gets instantiated (i.e. a package object is created ) ?

If so, the package object should resides in the memory while the package is in the open mode.

In general, the answers to your questions are yes and yes. It is not clear, though, why you ask this. Is there something you have trouble with or just trying to understand the moving parts?

Thanks,

Bob

|||

Hi,

I want to monitor SSIS packages running on system,I tried it with using RunningPackages class but it gives me running packages when I call method of RunninPackages class,

Can I monitor SSIS package execution continuously ? from where I get running package object

Thanks,

Omkar.

|||The question is related to the multiple instances in the memory. If i have opened the package inside IDE and I may want to execute the same package using other approaches such as invoked from C#.NET application or dtutil etc. Therefore each individuate application has its own package instance inside the process address space. And there should be mulitple loading for pakcage.... Please comments if this is wrong.|||

Well, it depends on what your package does. Your package may have some external dependencies that could cause contention. You will have different instances of the package object in different processes, but they still may share some resources/services.

Thanks,

Bob

Package hanging on OLE DB Command with large result sets

I have an SSIS package (SQL 2005 SP2 and Visual Studio SP1) that does the following:

OLE DB Source --> Conditional Split --> OLE DB Command #1 --> OLE DB Command #2

The source reads from database A. Each row is variable-width and up to several KB wide, including two ntext columns.

Command #1 executes a stored proc in db A, using a bunch of inputs and two output parameters.

Cmd #2 executes an update in db B, using the two output params from cmd #1 as inputs.

When the rowset size is small, around 500, everything works fine.

However, when the rowset size is larger, around 5000, SSIS hangs when trying to execute cmd #2. The profiler shows that none of the cmd #2 updates are ever executed. No error messages are produced, and the connection never times out -- it just hangs forever.

If I replace the cmd #2 updates with a simple select, everything works fine. If I replace it with a stored proc that does an update, it hangs.

The work-around I came up with was to create a new table in db B, and do inserts into the table, but unless I'm missing something, this still seems like a bug...

Do you have the RetainSameConnection property set to true on the OLE DB connection manager you are using? Try that if not.

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.sql

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.

Package fails when run under SQL Server Agent

Hi,

I have a SSIS package that I developed using Business Intelligence Development Studio. I imported it into our SQL Server database and it runs fine (when I connect to Integration Services within SQL Server Management Studio, and run the package). But when I created a SQL Server Agent job comprising 1 step, to run this package, it fails. The failing step attempts to read data from a MS Access 2000 DB into SQL Server 2005.

I added logging to the package, and got the following error logged to the Event Viewer:
"The AcquireConnection method call to the connection manager "CurrentYearPCStats" failed with error code 0xC0202009."

I then created a new Operating System (CmdExec) step, calling DTEXEC with the command line copied from that generated by the SSIS step. The command line is:
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTEXEC.EXE" /SQL "\AFAReduction\AFA Reduction Integration" /SERVER <ourservername> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

This failed with the same error in the Event Viewer, but for some reason also produced a meaningful error in the SQL Server Agent job's View History:
Description: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "The Microsoft Jet database engine cannot open the file '\\<anotherservername>\PCStatslive\CurrData.mdb'. It is already opened exclusively by another user, or you need permission to view its data."

Please note that the SQL Server Agent runs using user id sqlservice, by default, and I have amended this user's Windows account to make it a Domain Admin.

Please let me know why I might get this error, when the package runs OK on its own.

Many thanks,
Keith.

The jobs running with the sql agent usually run with the agent's service account identity (local system or Network service).

This user must have permissions to open your Access DB files (NTFS security etc included).

When you run it from SQL mgmt studio it is running using your own credentials...

Hope this helps.

|||In addition to Zoran's advice, take a look at this KB article which describes common problems with running in Agent, and how to troubleshoot them:
http://support.microsoft.com/kb/918760|||

i have a similar kind of situation As a inidividual package or executing through the command line the package runs fine but when i try to execute it through the Agent it fails.

Executed as user: SEMASTER\Administrator. The package execution failed. The step failed.

Any help

Thanks,

Jasmine

|||

Hi again,

Thanks for your replies. You could've knocked me down with a feather - I came in this morning and the SQL Agent job that runs at 6 AM had succeeded! Tried manually and its all working fine! I've changed nothing, I have the Job History to prove that the last test I did yesterday failed, and everything since 6 AM today has succeeded.

The only thing I can guess is that there was some problem with the Access database itself, which cleared overnight. I'll keep an eye on this.

Thanks again,

Keith.

Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manually
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
Thanks
Two possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package execution failed but no error (SQL2005)

I made an Integration Services Package which runs well if I start it manuall
y
(also from SQL server Management Studio).
Now I have created a new job for the SQL server agent which need to run my
package.
After starting the job, it ends after a few seconds:
"The package execution failed" is the only message there is.
I assume it has something to do with security but for the test I am logged
in as a domain administrator which has all rights to all databases.
Can someone give some clues about how to solve this?
ThanksTwo possibilities come to my mind:
1- SQL Server run under its own account; so you should check the permission
for this account on both Windows and SQL-Server side (for example, does this
account has an explicit access to the database?)
2- You are using things like a network drive (Z:\Repertory\...) instead of
an UNC file name (\\Server\Repertory\...) which are specific to the login
account and unknown to other accounts.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Frans" <Frans@.discussions.microsoft.com> wrote in message
news:6EBD9795-92BA-435C-BCC7-CFEBDC6EAF2F@.microsoft.com...
>I made an Integration Services Package which runs well if I start it
>manually
> (also from SQL server Management Studio).
> Now I have created a new job for the SQL server agent which need to run my
> package.
> After starting the job, it ends after a few seconds:
> "The package execution failed" is the only message there is.
> I assume it has something to do with security but for the test I am logged
> in as a domain administrator which has all rights to all databases.
> Can someone give some clues about how to solve this?
> Thanks

Package executing error

I wrote package using Integration Services and tried execute it on Visual Studio 2003 (Visual Basic). Package was loaded, but execute method has failed without any error messages. When I tried to run it on Visual Studio 2005, execution was successful.

Codes:

Dim App As DTSLib.IDTSApplication90 = New DTSLib.Application

Dim p As DTSLib.IDTSPackage90 = App.LoadPackage("d:\temp\Package.dtsx", True, Nothing)

p.Execute()

What can be the reason of the trouble?

Thanks,

Alexander

Only one .NET runtime can live in one process. Visual Studio 2003 uses .NET 1.1, and SSIS requires .NET 2.0 - so SSIS fails if the process has already loaded .NET 1.1.

You can use config file to change the .NET version used by applications developed with .NET 1.1 and force them to use .NET 2.0, but it will break Visual Studio debugging.

So it is highly recommended to use Visual Studio 2005. In Visual Studio 2005 you can also use managed API for SSIS - in Microsoft.SqlServer.ManagedDTS assembly, which is more convinient than using interop from DTSLib.

Package designer- feature pack

after installing the Microsoft SQL Server 2000 DTS Designer Com feature pack and then restarting my management studio, I still cannot see or edit my dts packages. I also tried editing them in the integration services consule but no luck there. I see them under the msdb and have no option to edit them.

Has anyone got this utility to work?

Thanks,
David

From Management Studio, connect to the server where you have your .dts packages saved, go to Management->Legacy->Data Transformation Service, when you see the list of dts packages, right click on one and choose Open. It works for me fine.

You can not use Integration Service designer to open or edit your .dts packages, that's expected.

Thanks

wenyang

|||Im guessing that your opening the packages stored on a sql 2000 server and not a 2005. I tried moving them to my 2005 server and opening them, but am not seeing them.

Thanks for the response.|||

Yes, I was connecting to a sql2000 instance from a sql2005 server, and everything worked fine that way. I now also tried in your way, I agree there maybe an issue here connecting to a .dts package saved on a sql2005 server, which I am observing right now. However, here is a work around for you to at least make your scenario work

At your box where Sql2005 is installed

1. Connect to a sql2000 instance first to see that the .dts package openned without a problem (that's what I got).

2. In that legacy designer UI, choose save your .dts package as "Structured Storage File" (instead of saving it to a sql server) to the local box.

3. Then in SqlServer2005 Management Studio, go to Management | Legacy | Data Transformation Service, right click and choose "Open package file" pointing to that .dts file.

That works for me and hope the same for you. If not, please let me know what you get and I'll follow up.

Thanks

Wenyang

|||

thanks much. I just got done going through the same scenario..

Cheers.