Data Flow Task in SSIS

Question

Data Flow Task in SSIS

James Weatherall

SSC Enthusiast

Points: 123
More actions
March 7, 2007 at 8:25 am

#116399

Hi,
I am having problems scheduling a Sql Server job to run an SSIS package which is attempting to run a simple import from a csv file. The package runs fine in BIDS but fails as soon as I run it from Sql with the error - The package execution failed. I can schedule and run any package containing only Execute Sql task items but as soon as I add a data flow task containing a flat file connection and OLE DB Destination the package falls over. Very frustrating.
Any help much appreciated.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Leonce SSC Enthusiast Points: 198 More actions · Answer 1

Can you give a more detailled error msg?

Is the file used for the Flat File Connection available from the server? Example, if it is C:\Folder\File.csv, that file should be in C:\Folder\ on the server also.

Is it working when you use a OLE DB instead of a file as source?

Leonce

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 2

Hi

It could be a possible security issue. Have a look and see if your SQL server agent has access to the location of the file. It is a good idea to start up the SQL server agent with a user account.

Do you do your dev on the server or your workstation?

Cheers

Jacques

James Weatherall SSC Enthusiast Points: 123 More actions · Answer 3

I have a feeling it is a security issue also but I can't get to the bottom of it. I am logged into the server as myself and have set the job to be executed using my own proxy. Is there something else I have to do with the agent?

I do the development on my workstation and have tried running the packages from both file system and by moving them onto integration services.

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 4

Check the account that SSIS and SQL server agent starts with in "services".

Have you opened the SSIS package in teh Dev studio on the server and executed it from there. I might give you the same errors if you log on using the accounts the SSIS and SQL server agent uses. If SQL server agent uses a "Local system" account change it to a domain account even your own user account would suffice for testing.

James Weatherall SSC Enthusiast Points: 123 More actions · Answer 5

The account used on the server is MSSQLServer. My problem is that the server is remote, has a number of databases on there and I don't have access to it. I doubt there would be Dev Studio on there even if I had. I was under the impression that the use of a proxy would bypass the sql server agent account. Is this incorrect?

SQL Adventurar SSC Eights! Points: 973 More actions · Answer 6

I am not to fimiliar with the proxy on SQL2005.

The dev I did I would connect to every file over the network in teh SSIS packages. This eliminated any issues that could have occurred diring the live implementations.

From what you have said the issue truly sound like a security issue from the SSIS and SQL server agent side. If the package executes perfectly on your workstation there is no other explaination why it would not work. I have to admit I have had my fair share of issues where the SSIS package would work in Dev but schedule it and it will not even run. In this scenario I found that SQL server agent battles to find the package on the server and not that there was an issue with the package itself.

To try and sort out the issue at hand it does become increasingly difficuilt to figure out a possible security issue if you can not gain access to the server.

With regards to the dev studio on the server. It does form part of the installation of SQL and if it was non SQL guru's who installed it changes are that the dev studio would be there.

Hope this helps

James Weatherall SSC Enthusiast Points: 123 More actions · Answer 7

Thanks for the advice, it is much appreciated. Security and permissions were the only thing I could link it to. I will attempt to gain access to the server and test the packages as you suggest.

James Weatherall SSC Enthusiast Points: 123 More actions · Answer 8

After managing to get remote desktop access to the server I logged in using the account which runs sql server agent. The package ran in exactly the same way in Dev studio with no problems but once again failed when ran through sql server. I'm totally stuck as to what is causing the package to fail. Is there anyway to log what is happening to the job as oppose to the package?

S. Kusen SSChampion Points: 10936 More actions · Answer 9

James, see my post on a similar forum:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=349385

Let me know if that helps.

Steve