July 20, 2009 at 7:39 am
Hi,
I have just built my first DTS package which transfers data from one SQL 2005 database table to another SQL 2005 database table on another server. The table layouts are identical in everyway.
When I execute the package using the debug mode in SQL Server business intelligence development studio the 110,000 rows transfer perfectly.
My issue is when I try and setup a SQL job which I have not done before it say it ran and executed. When I check the destination table there is no data so I must have done something wrong or be missing a key setting when building the job.
I have Googled and tried altering the configuration but seem to be getting know where. If anyone could please point me in the right direction or ask me questions about my setup and help me track a solution I would be most grateful.
Thanks in advance.
July 20, 2009 at 7:43 am
Did you look at the job step history to see if it showed any errors?
My first thought was permissions issue. Doe the user that SQL server runs under have permissions on that server? Also, if your SQL Servers are running under Network Service then you can only use SQL Server authentication and never trusted.
CEWII
July 20, 2009 at 8:03 am
Hi,
I just setup logs and tried again. This is the result:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:50:50 PM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 2:50:50 PM
Finished: 2:50:52 PM
Elapsed: 2.032 seconds
As for user permissions i am logged in as SA on the SQL server that is executing the .dtsx file. I think they are ok as i can run the package manually and the data does go.
Could it be something in the step area, there are some tabs that you can configure and maybe there is something in there you need to do when you setup a new job?
July 20, 2009 at 12:30 pm
Questions:
1. What is used for the connection manager for the package that points at the remote server, do you specify a username/password or is it set to integrated security?
2. What is used for the connection manager for the package that points at the local server, do you specify a username/password or is it set to integrated security?
3. What user does your SQL Server log into the machine it runs on, is it a domain account or a built-in account like NETWORK SERVICE. This is NOT the user you use to get into SQL but the user IT uses to start its services.
4. Have you used any expressions that might have turned off the dataflow at run-time?
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply