[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS Package exporting to CSV, Excel, 64 Bit Environment, runs as deployed, but not in a SSMS Job

Posted on 2012-09-10
15
Medium Priority
?
1,640 Views
Last Modified: 2012-09-16
I created and deployed two packages in SSIS, one that eports to an Excel 2010 spreadsheet, and one that exports to a CSV file.  The packages run fine when connected to Integration Services in SSMS (directly from the deployed packages).  But when placed in a job, I get errors.  I have checked the "run in 32 bit . . . " checkbox.  

The command line looks like this:

/SQL "\xxxxxxxx\DTS Packages\Packagexxx" /SERVER xxxxxxxx  /CONNECTION DestinationConnectionFlatFile;"P:\XXX Accounting\DPU_SSIS\DPU_csv.csv" /CONNECTION "DPU_csv.csv";"P:\XXX Accounting\DPU_SSIS\DPU_csv.csv" /CONNECTION SourceConnectionOLEDB;"\"Data Source=xxxxxxx;Initial Catalog=VCCdm;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=false;\"" /X86  /CHECKPOINTING OFF /REPORTING E

The error I get when I run the CSV package from a job is this:

Executed as user: XXXXXXX\xxxxxxx. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  9:05:49 AM  Error: 2012-09-07 09:05:51.11     Code: 0xC020200E     Source: Data Flow Task 1 Destination - DPU_csvTest1_csv [64]     Description: Cannot open the datafile "P:\XXX Accounting\DPU_SSIS\DPU_csv.csv".  End Error  Error: 2012-09-07 09:05:51.12     Code: 0xC004701A     Source: Data Flow Task 1 SSIS.Pipeline     Description: component "Destination - DPU_csvTest1_csv" (64) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:05:49 AM  Finished: 9:05:51 AM  Elapsed:  2 seconds.  The package execution failed.  The step failed.

I get a similar error for the Excel export, but as I have read, the hangup is more on the syntax for the Excel version.  I want to resolve them one at a time, so I am starting with the CSV export.


I have found numerous articles online as to how this problem can be solved.  The article I found that appears to give a solution does not have the detail I need to make it happen.  The solution I found in a forum ends with his comment:

"OK, based on suggestions on another thread I moved my connection string to a package configuration file.  You must also un-check the connection on the job step.   After much troubleshooting trying many different ways to produce this data in a useable format it is now working."

Article Link:
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/d61dfd57-6e4e-4b76-a895-84080250d0f2

I have never created a "package configuration file", and have no idea how to implement it.

Any help I can get on this is much appreciated.

Thanks - Hubbs
0
Comment
Question by:Hubbsjp21
  • 8
  • 6
15 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 38383434
If you haven't already, make sure the user the package is running as has the necessary permissions on the files directory. Then make sure that the file location is mapped as the P drive on the machine running the job (or you can use the fully qualified domain name).
0
 

Author Comment

by:Hubbsjp21
ID: 38383474
Jared_S

I created everything, and am running everything.  If it runs fine from SSMS from my machine when being connected to Integration Services, is there reason to believe permissions or mapping could be an issue?

Thanks,

Hubbs
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38383628
If your executing from your machine, drive mapping wouldn't be an issue (I've ran into a similar problem before, but have developed on my machine and deployed to a server).

Based on my experience, permissions could still be an issue since SSMS will use the SQL agent to run the job.

I hope that's all it is. Troubleshooting those errors can really kill your productivity.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Hubbsjp21
ID: 38383706
Yah.  It is rediculous how much troubleshooting I have already had to do so far using all these incompatable Microsoft scenarios.  I am pretty sure that permissions is not the issue.  Did you by any chance look at the forum string from my link?
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38383769
I did - it looks like the symptom they're solving is similar, but I think your causes are different.

Try to search your first error code... 0xC020200E
0
 

Author Comment

by:Hubbsjp21
ID: 38383922
Okay.  I see what you are saying now.  I have never asked our systems admin to give the SQL Server Agent permissions to a folder on the network.  What exactly am I asking for?  Sorry for being so ignorant here.
0
 
LVL 12

Accepted Solution

by:
Jared_S earned 2000 total points
ID: 38384023
The agent runs under NT AUTHORITY\System or NT AUTHORITY\NetworkService depending on how it is configured. It will need read/write access to your directory.

(You can check out more details here:
http://msdn.microsoft.com/en-US/library/ms191543%28v=sql.90%29
)

You can probably find some success by running through these steps:

1) set up credentialing in SMSS for the SQL Server Agent (under Security) with your credentials or the credentials of a network user with appropriate access

2) created a proxy account for SSIS Package Execution (under SQL Server Agent, Proxies, SSIS Package...) that uses the credentials you just created in step 1

3) Edit the job steps to run as the proxy account

I run a few jobs right now with the second method.
0
 

Author Comment

by:Hubbsjp21
ID: 38384129
I created the Proxy using my own credentials, used it in the step, and seem to still have the same problem with the same error.  What part does the "DTExec: The package execution returned DTSER_FAILURE" play in all this?

Error: "Started:  11:35:34 AM  Error: 2012-09-10 11:35:35.63     Code: 0xC020200E     Source: Data Flow Task 1 Destination - DPU_csvTest1_csv [64]     Description: Cannot open the datafile "P:\XXX Xxxxx\DPU_SSIS\DPU_csv.csv".  End Error  Error: 2012-09-10 11:35:35.63     Code: 0xC004701A     Source: Data Flow Task 1 SSIS.Pipeline     Description: component "Destination - DPU_csvTest1_csv" (64) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:35:34 AM  Finished: 11:35:35 AM  Elapsed:  1.172 seconds.  The package execution failed.  The step failed."
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38384549
You do have a second error in there (Code: 0xC004701A), but you may see that go away once this first one is resolved.

If the proxy is set up properly, then you might take a look at the protection level in your package.

Try setting it to DontSaveSensitive if you haven't already.
scheduled-job.JPG
credentialing.JPG
proxy.JPG
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38384581
I hate to ask something very simple, but ... does P:\XXX Xxxxx\DPU_SSIS\DPU_csv.csv exist at the time the SSIS job is run?
0
 

Author Comment

by:Hubbsjp21
ID: 38384584
Your screenshots verify that what I have done is right.  Where do I look for the "protection" in the package itself?
0
 

Assisted Solution

by:Hubbsjp21
Hubbsjp21 earned 0 total points
ID: 38384692
No problem in asking the question.  It is created earlier in the SSIS package.  Right after my last comment, I dug around a little more and found this link:

http://blogs.msdn.com/b/jorgepc/archive/2010/11/29/cannot-open-the-datafile-lt-path-to-file-gt-error-accessing-a-flat-file-in-ssis-using-a-proxy-account.aspx

I changed my mapped drive names in the SSIS package to Server names, (X:\xxx to \\Xxxxxxxx\xxx) and Voila!, the Job worked.  So now I can schedule the CSV export.  I am hoping that this will also resolve the problem for the Excel file export.

I appreciate all the help you have given me Jared_S, how do I reward you in this question?
0
 

Author Comment

by:Hubbsjp21
ID: 38384796
So Jared_S, I was hopeful, but kinda knew that this solution would not resolve the Excel export issue.  I am going to post another question for that one.

Thanks again,

Hubbs
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38386647
I think you can just accept any of my comments as a solution and it will do it.

Thanks Hubbs. I'll look for your other post and see if I've ran into the error myself.
0
 

Author Closing Comment

by:Hubbsjp21
ID: 38402879
The proxy was necessary, but also needed to change the file path to exclude mapped drive.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

873 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