More Problems With 32 Bit Package on x64 SQL Server

Hello,

I have gone through some documentation on how to execute a 32 bit package on SQL Server 2005 x64, and I'm still running into problems when I attempt to execute such a package from a Job.

Critical Points:

-The package was imported into an instance of Integration Services 2005 from the file system to a subdirectory of the MSDB folder. From Management Studio, Integration Services, the path to the package looks like this: Stored Packages\MSDB\CPS\CPS_Collections. 'CPS_Collections' is the name of the package.

-This package has a connection to an Access 2000 database using the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. I understand that there is not a 64 bit version of this provider. The Access database is stored on a different machine than the one that hosts the SQL Server 2005 x64 instance.

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

-When I attempt to run the Job, the Job fails. Here is a part of the error message from the Job history:

The process could not be created for step 1 of job 0x9B318B226174A24B8BD63CE8F4814864 (reason: The system cannot find the file specified).

Does this point to a problem of where the Access datbase is located? Could it be that the account that runs SQL Agent does not have access rights to the directory? Is there a way to run an operating system command as someone other than SQL Agent? Is there something wrong with the command line that I am using?

Please share if you have any ideas on this.

Thank you for your help!

cdun2

On a 64-bit server, the SSIS job step calls the 64-bit version of DTEXEC. You need to call the 32-bit version (located in C:\Program Files (x86)\Microsoft Sql Server\90\DTS on most installs). To do this, you need to use a CmdExec job step.|||Hi,

Since the error description said : The system cannot find the file specified, maybe you set the location of Access database into a mapping drive or something, try using this format \\ip_address_of_the_machine\

Best regards,

Hery|||

cdun2 wrote:

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

You can use DTEXECUI to construct the command arguments, but you still need to supply the executable name for the command line. I.e. your full command line should look like

"C:\Program Files (x86)\Microsoft Sql Server\90\DTS\Binn\DtExec.exe" /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

|||

Thanks for the help, and sorry for the late response. I'll take your responses and look into this further. I can say that the location to the Access database is not expressed as a mapped drive.

More Info:

We have setup a replication in SQL2000:

We have DTS package automatically pouring data into the publishing database(source tables). During this process, we want to temporary disable certain triggers. However, the command

Alter table 'tbl' disable trigger 'abc' errored out. The error message said:

''Cannot alter the table 'tbl' because it is being published for replication."

I've digged more into this and found although it's not allowed to disable a triggers,

the SQLServer do allow delete the trigger and recreate them.

Is there any way to disable the trigger directly?

Thanks in advance,

Don

BTW:

I've used the following sql directly, however the trigger still fires.

UPDATE
sysobjects
SET
status = status|2048
WHERE
type = 'TR'
AND
parent_obj = OBJECT_ID (@.table_name)

The only other way around now is to create stored procedures that dynamically create the trigger. Because our trigger is normmally larger than 8000 bytes. We have to create one stored procedure per trigger. This option is not acceptable because not only it takes quite a time, but also a maintainance nightmare.

More Info: We are using transactional publication.

And trying the sql command in query analyzer quickly give us an error on published article table.

alter table tbl disable trigger abc

|||have you tried adding NOT FOR REPLICATION in your trigger?|||

We did try added not for replication in my trigger. and it does not work.

This issue is for loading data for the master table.

Besides, we did not publish triggers.

|||Disabling the trigger is not the issue. An ALTER TABLE command of ANY kind is not supported in 2000 against a replicated table. The only option in this case is to drop the trigger and recreate it, which is allowed in 2000 against a replicated table.|||

We kown this and we've found a better way to create the triggers. Nobody like this approach through.

If dropping trigger is allowed, there's no reason to not allow disable trigger.

It looks to me it's kind of lack of insight of microsoft. they have sp_repladdcolumn...

they should have a sp_replenabletrigger...

If you look at me post, I tried to modify sysobject tables trigger flag to change it directly.

However, it does not work so my guesses microsoft have more tables involved in this.

I was expecting somebody from microsoft could give me a hint or give me a sp_replenabletrigger.

|||

sp_repladdcolumn and sp_repldropcolumn were added as very specific patches to very specific problems. The issue was quite simply not having enough time to redesign a major portion of the replication engine to allow an ALTER TABLE statement. There are no hints, no work arounds, and no bypasses. There isn't any code like you're looking for either. The reason for that is quite simple. For the last 6 or so years, all development efforts were focused on SQL Server 2005, so if SQL Server 2005 already had a feature that would address and eliminate this entire issue, why waste the time and resources to graft this into SQL Server 2000?

I saw the message in the post. You are more than welcome to hack the system tables if you choose to. I'm certainly not going to hand anyone code to do so. Hacking the system tables is completely unsupported. If you blow up the system, you will not get any support from Microsoft in fixing it.

You have exactly two options:

1. Stay with SQL Server 2000 and write the code as a drop/create trigger

2. Upgrade to SQL Server 2005 and use an ALTER TABLE to disable the trigger

|||

Thanks, Michael.

That's a clear message.

We do encounter other problems with recreating triggers. if we using cmdshell to create the trigger though.

For example, sp-a is the one to create the trigger.

if sp-b calls sp-a,

sp-b do this:

Begin transaction

exec sp-a

Commit Transaction

calling sp-b will stuck inthe command shell out.That's another unpleasant finding yesterday.

Every developer want to upgrade to sql2005. Unfortunately upgrade to sql2005 require massive re-testing of all existing applciations,

This could be a valid reason but it's not enough for the move to sql2005.

I have to considering other solution other than replication now since we are also tight on schedule.

Thanks again for the response.

Don

sql

More fun with DTS Packages

Hi guys,

Well, another day, another adventure in the land of editing DTS packages. Anyone out there run into a package that will run for one sysadmin but not for the other? I have a package that can't initialize it's connections (sql server doesn't exist or login denied) for me but runs fine for my boss from his workstation. Note, permissions have been thoroughly investigated both on the database and the server plus I can run other packages on that server.

Bad or missing files somewhere perhaps? I logged onto the server (where the packages and the database live) and ran some packages. While all my other packages run OK from the server, the package that is giving me fits from my box gets an error message about

'cannot find msdb..sp_log_dtspackage_begin'.

Has this file given anyone else issues? My other packages run from the server without this error.

Any other advice besides 'don't use those evil DTS packages'?

Thanks for your help!DTS is not evil silly girl Microsoft is evil!

who does your boss authenticate to when he/she/it logs in?
who do you athenticate to when you log in?

sp_log_dtspackage_begin should be owned by dbo, is that the case on your server?

What happens if you recompile the sp? Make sure you are logged in as SA!|||Hah! Just another example of absolute power corrupting absolutely?

Well, we all get authenticated (on the network, I'm assuming) through the same box--there are only 15 of us so we have one domain controller. Supposedly we are all processed similarly.

As for the sp_, I cannot find this puppy. I'm assuming that if it exists, lets not rule out a bogus error message, it's not something MS is displaying. Searching for it doesn't turn up anything. It certainly doesn't seem to be anything anyone here wrote.

Ahhhh, to have a local DBA. Currently I'm recreating the whole @.#^$#!$ thing (it's rather robust, to say the least--30 tasks, most with individual field scripting in data transformation objects). I'm on object 4 and so far it runs fine.

Have you ever had one of these become corrupted?

Should I get a bigger stick?|||Bigger stick? Only if it's long enough to reach the the prior developer!

When I was asking about authentication I meant on the SQL box. Are you athenticated to dbo where as your boss isauthenticated to sa?

this sp was shipped from Microsoft. it should be:

CREATE PROCEDURE sp_log_dtspackage_begin
@.name sysname,
@.description NVARCHAR(1000),
@.id UNIQUEIDENTIFIER,
@.versionid UNIQUEIDENTIFIER,
@.lineagefull UNIQUEIDENTIFIER,
@.lineageshort INT,
@.starttime DATETIME,
@.computer sysname,
@.operator sysname
AS
SET NOCOUNT ON

INSERT sysdtspackagelog (
name,
description,
id,
versionid,
lineagefull,
lineageshort,
starttime,
computer,
operator
) VALUES (
@.name,
@.description,
@.id,
@.versionid,
@.lineagefull,
@.lineageshort,
@.starttime,
@.computer,
@.operator
)
RETURN 0 -- SUCCESS

GO

if you can not find it in the list of stored procedures in the msdb database I would say you have a bigger problems. You should be able to copy this from another server.

SQL Server has been a stable product since 7.0. Stable in the sence that things don't mysteriously stop working. Data can and does get corrupt but this sounds like a stored procedure was deleted! Maybe time to check security and change the sa password?

as for corruption, I like to think of Microsoft as a drug dealer, they give you a free taste of their drugs till you get hooked and then WHOMP they have you!|||Thanks for the sp. Adding it to the database's sp's didn't make a difference. However, it is either not displaying or simply not there on any of the databases on this server. Hard to tell what's going on. The packages that run, run with or without. The one that doesn't, similarly, won't run either way.

Sorry if I'm being obtuse, after all I'm just a stray VB/VBA programmer who took a job without realizing that there was no DBA for the back end here. (A situation you do not find when writing to Oracle. Probably MS is making things appear to be way too easy. I bet it's a marketing tactic, 'Use our software and you can fire those expensive DBA's. Any schmoe can ride herd on SQL Server--even applications programmers.') However, under the Server's Security, Server Login Properties I am a system administrator. I'm assuming it doesn't really get any better than that.

Note, the box I'm currently using is considered to be seriously corrupted--at least by the previous user--and when I installed the sp_ my icons for Enterprise Manager blanked out on the desktop and icon bars. Is it a sign? Should I start looking for Holy Water and a disk with SQL Server on it?|||The sp must be add to the msdb database, if it isn't already there. Please verify that the sp exists in the msdb db.

sa = god in SQL Server speak.

<soapbox>I hate companies that think they don't need a dba and that a developer should be able to handle it. IMNSHO this is one of Microsoft's weaknesses. You will rarely see Oracle installed by someone that doesn't know what's going on. This is the #1 reason why Oracle is perceived to out perform SQL Server.</soapbox>

Before you lose your sanity I would verify you are working on a stable box or not. I am wondering if your DTS problems started suddenly or were never working right to begin with? If they just started to fail around the time your predecessor left you may be dealing with another issue.|||Oh dear, I am being a bonehead. Yes, it is there in the msdb.db. Forgot about that whole thing. For this level of hand-holding I may have to fly to Texas and deliver that ride.

About the box, it was my boss's until he managed to get an upgrade so I don't think he's been sabotaging (sp?) it, however, there have been many non-kosher installs. I'll be getting a recycled box whenever someone has time to rebuild a spare.

The only remnant of the previous developer here is the big crack in my monitor case--I hear the keyboard was destroyed. Apparently this occured immediately before his giving 2 minutes notice... Guess he was short on sense of humor.

In any case, I'm slowly hacking my way through rebuilding this thing (still on object 4 though). It's a good learning experience and I obviously have much to learn since I just met up with DTS two weeks ago. The VBScript part feels very comfortable and the two packages I have built so far seem to do a nice job. It's a great idea to have this nifty visual interface. Now if they could only add some decent debugging and flesh out the reference material to actually cover the entire product it would be grand.

I'm sure I'll beat this package into submission eventually.

Thanks for your help!|||Don't worrie about the ride, I needed a good laugh today!

DTS is a VERY cool tool, mainly because it was written to work with almost any provider not just SQL Server. When I worked at Exxon we used it heavely for Oracle and DB2.

Who is the owner of the DTS package? After re-reading your original post I have to believe this is an ownership/permissions issue.|||Well, I hope I made you laugh with me, not at me--but I guess either way is OK.

DTS is a cool tool and I think I'll be pretty good at it in a few months. Looks like I'm going to get lots of practice until they drag me away to do customizations on the front end.

Currently I am shown as the owner. However, it didn't start out that way. We have a wierd (read dysfunctional) setup here in that this package is used on a server in NY. However, development occurs in Boulder. We do not have access to the same databases or servers. They emailed me a copy of the package (which was working) for modifications/bug fixes. Then the fun began because, among other things, our databases have different names. 30 objects all pointing at a connections to nowhere...

I do believe that I have changed all connection strings to point where I can get a connection to a local copy of the NY database. I've also tried adding a new connection and pointing them there. Initially I suspected that the connections changes were the problem, but it is not intuitively obvious why it will run on my boss's box if that is the case.

None of this, of course, bodes well for the idea of sending the package back to NY and having it run in their environment. Right now I have tentative plans (assuming I can manage to test my changes here) of walking someone through just adding the changes on their system so that we change the one that is running rather than replacing it. Far from an ideal scenario either way.|||What happens if your boss logs on your box and runs the package?
Do you run the package from the GUI or DTSRUN.EXE?

Given your current suspect box and diffrent connections string, I think walking a warm body through the changes would be a safe thing to do, for now.

BTW The only person I ever laugh at is myself.|||Hey, I only started programming after they gave us a GUI. I was being an opera singer when that whole DOS promt thing was in vogue. Then almost ten years ago I discovered how easy it is to make money by working on computers... Well, it is easier than trying to get people to pay you decent money (or give you health benefits) to sing.

To answer your question, I click on that there big green arrow thingy or go to Package>Execute.

I'll see if I can get my boss to log onto my box. Might be a while before I can corral him into that.|||Well, I guess they don't pay the boss the big $$$ for nothing.

It wouldn't run for him either, but having received packages for editing from NY before, he knew of one more place to look. He figured out that the path for the package log was set to 'local'. I do have a local copy of SQL Server but it isn't running. Apparently his local copy was running this morning when he tried it on his machine.

Whew! It always seems so easy when it's fixed.

Thanks for all your help--not to mention the entertainment value! File this one in the 'strange but true' category.|||Glad you found a use for your boss. Some times they are useful to have around.

School of hard knocks! Always the best teacher.

Not sure I would have come up with that one but in time all magic is revieled.sql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
Thanks and Regards,
Prasanth
You can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth
sql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth

Monitoring a folder for a file - start ssis

Hi

I want my package to monitor a directory for a file and when it detects one it will start processing the file. Any help would be appreciated

Regards

Ants

The only (and easy) idea that comes to my mind is to schedule the package to run n times a day and to include conditional logic in control flow based in the existence of files on the folder.

Rafael Salas

|||

You can use the WMI task to do this. There is a sample in BOL.

Or, you can download the fileWatcher Component. http://www.sqlis.com/default.aspx?23

|||WMI is a good option here and the WMI Watcher Task will help you to achieve the same.

I remember reading somewhere that the memory used by the package will

be released only when the package is stopped, so in your case if the

package is going to be waiting for the file (package in run state), we

need to consider the side effects of memory.

Thanks,

S Suresh

Monitor SQL Agent job in ASP.Net

Hi all,

I have a web page which calls a SQL Agent job to initiate an SSIS package. Is there any way i can monitor the job and detect once it has completed successfully? At present all i can seem to get is the return status of whether the job has started or not.

Any help would be most appreciated,

Many thanks in advance.

Grant

You could query the sysjobhistory table. When steps complete, rows get written to this table. You could probably access this via SMO as well, I haven't checked. There is an instance_id which will allow you to track your specific instance, assuming you get that as a token when starting. It may depend on how you are doing this, T-SQL or SMO, I haven't looked vbery far into this, but start with sysjobhistory.