Problem scheduling DTS packages

I am getting the following error whenever I schedule a new DTS package:
SQL Server does not exist or access denied
This is new, all of my older scheduled packages run without any problem.
This just started occuring during the past week and is occuring on all of my
SQL Servers (7 and 2000).
Any ideas?John
Well , Perhaps you are created the DTS package on your computer and when
you execute the package it runs under account of your computer (SQL Agent
service)
By default, the owner of that service is LocalSystem (with has many
Administrator-style permission, but *NO* access to any network resources).
So, change the account of that service to a Domain Admin account (or other
suitable account with permissions on both computers (server and yours)
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> I am getting the following error whenever I schedule a new DTS package:
> SQL Server does not exist or access denied
> This is new, all of my older scheduled packages run without any problem.
> This just started occuring during the past week and is occuring on all of
my
> SQL Servers (7 and 2000).
> Any ideas?
>|||I'm just curious why it was working, I haven't changed anything and now it
does not. The network guys applied some security patches about the time
this started.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eERZIZVnDHA.3612@.TK2MSFTNGP11.phx.gbl...
> John
> Well , Perhaps you are created the DTS package on your computer and when
> you execute the package it runs under account of your computer (SQL Agent
> service)
> By default, the owner of that service is LocalSystem (with has many
> Administrator-style permission, but *NO* access to any network resources).
> So, change the account of that service to a Domain Admin account (or other
> suitable account with permissions on both computers (server and yours)
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I am getting the following error whenever I schedule a new DTS package:
> >
> > SQL Server does not exist or access denied
> >
> > This is new, all of my older scheduled packages run without any problem.
> > This just started occuring during the past week and is occuring on all
of
> my
> > SQL Servers (7 and 2000).
> >
> > Any ideas?
> >
> >
>

Problem scheduling a package

Hi,
I have a package saved in the msdb database. When I right click on it and execute, it works fine through the dts utility program.

When I schedule it, it always errors out with the message "The command line parameters are invalid".

The package is password protected. The command line in the dts utility reads: /DTS "\MSDB\<package name>" /SERVER aawork2/DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I have to enter my password to view this.

In the job step, if I click the command line, it reads: /DTS "/MSDB/<package name>" /SERVER aawork2/DECRYPT <password> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

I suspect its something to do with the password protection and the fact the job runs it under the service account.

Thanks in advance for any help.

Asim.
No, the problem (most likely) is that the package path has forward slashes instead of backward slashes. Try changing them to back slashses. Also put a space between the server name and the /DECRYPT option. These were bugs that should be fixed by RTM.

Thanks,
Matt|||Matt, it works. Thanks! This was very helpful.

Asim.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);Nobody is doing this?|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running SSIS from C# program.

I am able to run it from the file system but when I try running it from SQL Server I get an error message that it cannot load package. I am using the following code.

Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

Nobody is doing this?
|||What is the error message exactly? Can you see the package in the MSDB database when looking in Management Studio?

Have you read through this? http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.loadfromsqlserver.aspx|||The specified package could not be loaded from the SQL Server Database /r/n

I tried both this
Package package = app.LoadFromSqlServer("trans_hist", Properties.Resources.dbServerName, Properties.Resources.userId, Properties.Resources.password, null);

and this
Package package = app.LoadFromSqlServer("\\MSDB\\Maintenance Plans\\trans_hist", "ppntt240", "id", "password", null);

I also tries adding .dtsx to the SSIS name.
|||I think you may want:

"\\Maintenance Plans\\trans_hist"|||That was it, thank you.

Problem running package with 'larger' amount of data

Dear,

I created a package getting data from files and database sources, doing some transformations, retrieving dimension id's and then inserting it into a fact table.

Running this package with a limited amount of data (about a couple of 100.000 records) does not result in any errors and everything goes fine.

Now running the same package (still in debug mode) with more data (about 2.000.000 rows) doesn't result in any errors as well, but it just stops running. In fact, it doesn't really stop, but it doesn't continue as well. If I've only been waiting for some minutes or hours, I could think it's still processing, but I waited for about a day and it still is 'processing' the same step.

Any ideas on how to dig further into this in order to find the problem? Or is this a known problem?

Thanks for your ideas,

Jievie

Look at the BuffersSpooled perf counter. If it's really up there, you're thrashing the disk.

Also, just look task manager under the performance tab. Look at Physical Memory available. Chances are, you're running up against memory limitations.

Depending on what your pipeline contains, you should be able to optimize out the bottlenecks as well as eliminate any applications running that may be competing for resources.

This white paper is helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

K

|||

Dear Jievie,

Could it be possible that the package tries to update a row locked by an other application?

Succes,

Paul

|||

Dear KirkHaselden and Paul,

In the meanwhile, I did some checks you both proposed me to do and can tell you the following:

I can't imagine another application is locking any rows or tables, as my sources are some flat files and two tables that are only used by my SSIS package. I also checked the system views (for the tables), no locks are listed...|||

There are three things you're not telling us ... how much memory you have, what transformations you are performing, and how big your rows are.

You are indicating you are memory constrained, but we don't know how much is involved.

The transformations are important - some transformations such as sort, or lookup, or aggregate, can require a lot of memory. Others, such as data conversion are very lightweight.

You can see the size of your rows by double clicking on the paths between transformations and looking at the metadata tab there - it will tell you what data types you are using for each row, and from there you can start to get the size of the row. (Remember folks, "row" is not a useful metric - we've seen rows from a few bytes to several megabytes being passed through SSIS.)

Once you get us this additional information, we should be able to help more. And do read the performance whitepaper - it will give you many useful suggestions.

thanks

Donald

Problem running package

Hi,

I am trying to run simple import export package and if fails saying that pacage execution failed.

then i try using File system and the error say that it is not 32 bit driver and execution failed.

I get this error also

The return value was unknown. The process exit code was -1073741795. The step failed.

thanks

What does your package import/export? If you use 32-bit data providers, you will need to run your package in 32-bit mode.

Thanks.

Problem Related to Package execution in SSIS

Hi,

I have the hierarchy of packages..

there are three levels..

first package (say L1) calls two child packages.(say L2_1 , L2_2)

the parameters are passed to L2_1 from L1 and it executes completely and successfully.

next step is L1 calls L2_2 without any parameters.. and package fails with error as execution of L2_2 failed.

when I executed L2_2 individually it successfully executes.

L2_2 itself calls 5 other packages (at level 3)..

Am not able to identify the possible reason for failure..

I suggest you turn on package logging in each package (right click on the control flow and select logging) and then come back here with specific error messages once captured.|||

Thanks for you help phil...

now I can see the errors due to which my package is failing..

sql

Problem of Parent-Child package with ASP.Net

I have an ASP.NET application that calls a SSIS package. The SSIS package internally calls some other child packages. I observed that sometimes some child pacakges are not even called by the parent. and the behaviour is very indeterminate. sometimes they work fine. sometimes they don't. There is no clue available for this behaviour when the pacakges are not executed. (One general observation is that the memory consumption is very high. but that is the case always.)

I have enabled logging on all child packages. The log is not updated at all when the child packages failed to execute. i.e. the package execution does not start.

Could somebody explain why this is happening? any suggestions/ similar experiences?

Regards

Saurabh

One more observation: if i run the same parent package as a stand-alone. (i.e. not through the asp.net application) it always executes fine. it calls all the child packages very well always. what is different in the asp.net context?

Problem looping DTS and BCP within a Stored procedure

All,

This is the scenerio

SP=Stored Procedure

I call SP1 which calls a DTS package.

DTS package calls SP2 and SP2 calls SP3 and SP 4 using an IF condition

SP3 has 3 BCP OUT commands after which it calls SP1 again

Now, the problem is that if I follow all the steps, then only 1 of the BCP command executes and the control exits out of SP3 and returns control to SP1.

If I ignore SP1 and DTS and run SP2 independently, then all BCP steps in SP3 are executed and control returns to SP2

I ma not sure if using DTS and BCP in SQL stored procedures might cause any problems

Appreciate your helpIt seems like your DTS is executing under a different security context than when you execute your SP3 from QA. Are you checking for errors after each BCP in your SP3? If you're calling your BCP using xp_cmdshell, - consider doing INSERT @.temp_tbl exec master.dbo.xp_cmdshell [....].

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.

Problem inserting data into onw of my tables

i created an SSIS package to look for data in a table on another system and compare it with the table i have in system 2, if there are any changes to system 1 then it must apply them to system 2

My Problem:

It scans through my table and finds all the correct records to insert, but when it has to insert the new data into System 2 table i keep on getting violation and contraint errors because of the primary key and foreign key constraints. how can i get around this, or does anyone have an alternative solution for me.

Total Specification Requirements:

i have 2 systems both running SQL Server. everytime data gets updates in system 1, the same change needs to be made in system 2. The databases and tables are identical.

Any Help would be graetlty appreciated

Kind Regards

Carel Greaves

Carel,

There is nothing magic in SSIS that helps you with data issues like PK/FK violations. With the few details you are providing about your package I just can think in a few tips:

Know your data. If data is being duplicate by the inserts, you need to know why. Same for the FK. I don't know how you are performing the inserts; but is you are doing it in a data flow; at the very least you could enable error redirection in your OLE Destination component and capture the row(s) that generate the exceptions; so you have a better understanding of the situation. Perhaps the 'check if row exists' logic is not accurate. See this for some approaches: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael

More info on my package.

I have a database called HISDirectory, inside HISDirectory is a Table called HISMemberData

I have two Servers with the same database. One server is situated in Australia and the other in SA.

Everytime a new member is added to HISMemberData in SA, it must take that same value and insert it into the HISMemberData Table in Australia.

the table only has 4 fields. ID, MemberID, HealthID, DateSend.

There is a KEY on MemberID and on HealthID

If i use the

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

VALEUS (123, 123, 123)

then it works fine, but i can't do a:

INSERT INTO HISMemberData (MemberID, HealthID, DateSend)

SELECT * FROM tmpTableWithCorrectValues

I have tried that method from the guys link you sent me before but it crashes with the same problem every way that i try.

Even if i could just get a SQL query to help me with the population of this table. I have created a SSIS package to import the table from the other side into a new table and then try to populate the table locally, but i just seem too stupid to get it done.

Please Help

Kind Regards

Carel Greaves

|||

Ypou should be able to solve this using the approach described in the link I gave you. You need to make sure the lookup componnet is properly set up; this is: The join conditions should uniquely identify if the row exists in the target table and the error out put should set to redirect the errors ( a row not found in the lookup is treated as an error); then the error out put are you inserts and the other output are your discard or update rows.

Make sure the PK and FK definitions are identical in both tables.