Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SSIS Package wont add rows when it runs from the Agent

Posted on 2008-11-14
15
Medium Priority
?
77 Views
Last Modified: 2016-02-13
I'm having an issue with a SSIS package i just created.  This package is simply designed to add rows to a few tables and it runs fine.  when i run the package manually it works.  When i run it from integration services within management studio it works.  when i run it using the agent it runs succesfully but no rows are added to the tables.  What am i doing wrong?  Any help would be greatly appreciated.  Thanks in advance.
0
Comment
Question by:thecruz
  • 8
  • 7
15 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22965240
How do you have the package saved, to the File System or SQL Server?
What Package Protection Level did you choose?
You have it in a SQL Server Agent job. What account is it running in under the SSA? If you check the "View History", what is the message. Please copy and paste here. Thanks.
0
 

Author Comment

by:thecruz
ID: 22975828
Thanks for the reply.  i'm using File System and the protection level is DontSaveSensitive.  The job is running under my credentials using a proxy.  There is no error messages in the history.  it says it ran succesfully.  That is what i dont understand.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22976187
In cases like this I like to see the log. In the SSIS package, enable logging to a file. See the instructions below. Then post that file here. Please also post the complete history message. Thanks.
http://msdn.microsoft.com/en-us/library/ms138020.aspx 
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:thecruz
ID: 22977547
Is this what you are looking for?

Name: PackageStart
Operator: AME\rsmith
Source Name: Package
Source GUID: {0D4B0ACA-03C0-42DB-AF14-DE2AE8046482}
Execution GUID: {FDF8A592-4E6E-4BC1-AC51-1E722DFC21AE}
Start Time: 11/17/2008 12:34:18 PM
End Time: 11/17/2008 12:34:18 PM

Beginning of package execution.
0
 

Author Comment

by:thecruz
ID: 22977593
here is the log file
Log.log
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22977855
That log only relates to a job, ImportCSV. Is that this job?
On the View History window, expand the last run attempt and highlight the expanded row. What is the complete text in that message.
0
 

Author Comment

by:thecruz
ID: 22977955
Yes.  That is the name of package.

11/14/2008 14:09:49,ImportCSV,Unknown,1,BWTCSQLPR01,ImportCSV,One,,Executed as user: AME\RSMITH. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:09:49 PM  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  2:09:49 PM  Finished: 2:09:53 PM  Elapsed:  4.016 seconds.  The package executed successfully.  The step succeeded.,00:00:05,0,0,,,,0
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22978020
Please copy the History message. Thanks.

job-history.bmp
0
 

Author Comment

by:thecruz
ID: 22978127
sorry about that.  i'm still trying to get used to SSIS.

Date            11/14/2008 2:09:49 PM
Log            Job History (ImportCSV)

Step ID            1
Server            BWTCSQLPR01
Job Name            ImportCSV
Step Name            One
Duration            00:00:05
Sql Severity            0
Sql Message ID            0
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: AME\RSMITH. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  2:09:49 PM  DTExec: The package execution returned DTSER_SUCCESS (0).  Started:  2:09:49 PM  Finished: 2:09:53 PM  Elapsed:  4.016 seconds.  The package executed successfully.  The step succeeded.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22978159
Ok, the SSIS package running from the SSA job is obviously not completing successfully, not matter what this says. Let's look at the SSIS package. Can you attach here or show me a screen shot.
0
 

Author Comment

by:thecruz
ID: 22978257
here is a SS of the package itself
importPackage.gif
0
 

Author Comment

by:thecruz
ID: 22978272
the package is designed to loop thrue a directory and select files by name.  It takes those files and then takes the data and imports it into SQL server.  then it copies those files and stores them in a backup directory.  Finally it deletes the files.
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 2000 total points
ID: 22978306
In the loop for each one, put an Execute SQL Task to write the filename it finds to a table so we can look at it. Is the filepath using a UNC naming: \\server\directory? In 5 seconds it is probably not finding any files, in fact, probably can't access the directory? The user AME\RSMITH is you?
0
 

Author Comment

by:thecruz
ID: 22978561
Found the issue.  I used the UNC Path for all the file connection but i forgot to add it to the loops.  Thanks for the help.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22978569
You are welcome!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question