SQL task works running the package but not when called via C#

This should be a very easy question.

I'm trying to develop an SSIS package that will eventually do many things, but I can't get it to do very basic things.

My current test package has 2 tasks in it:

1) File System Task ( FST )

2) Execute SQL Task ( EST )

When running just the SSIS package via the VS2005 IDE ( as startup project ), everything works fine. The FST moves a file from DIR_A to DIR_B and the EST inserts a test record into the test table.

If I set my C# app to the the startup project and execute the package from within the C#, it kinda works. The FST works fine, but the EST does not work and the package returns a "FAILURE" code to the C#.

The EST is incredibly basic. This is the SQL text:

insert into tmpssis ( tmpdata ) values ( 66 );

I'm using ADO.NET, Direct input, FALSE for IsQueryStoredProcedure, and it's using the only connection I've set up to the database.

The FST block runs - the file gets moved, but then it fails on the SQL block for some reason.

I'm open to any suggestions.

Thanks,

-BEP

You might want to post your C# code here so that we can see where you are going wrong. If you have the SSIS package working fine on its own, calling it via a C# program should not be a problem at all. I actually just completed a project to do the same. Here is some code snippets to just execute the dtsx package..not exactly sure if thats what you are asking for but here goes, you need to add the Microsoft.SqlServer.Dts.Runtime assemblies and the System.Data.SqlClient assemblies to the package.

Your code to just call the app and execute would look like so:

result would contain the success or failure result of the package, you can do a ToString to display it.

The package runs when I call it from C#, but only the first of the 2 blocks runs.

This is the C# code:

The packagePath variable is correct - I look at it while stepping through the C# code. I run it in both debug and non-debug mode and get the same results.

If I disable the first block in the package that does the file moving, it behaves the same: the SQL block runs fine if I run the package from the VS2005 IDE by itself, but when it's executed from C#, it returns "FAILURE" and the SQL hasn't run ( since the record didn't get inserted ).

I've recreated the SQL Task and that didn't help. I've done a myriad of other things over a 4+ hour period to get it to work and none of them helped. I'm incredibly stumped on this.

The SQL 2005 server is on the local machine.

-BEP

YoungEngineer wrote:

enable SSIS logging, and put the actual error message up on here so that we can take a look.

I was working on trying to get something to a log when I posted last night. I had issues with getting logging working so gave up for the night. I just started to mess with this again and came across another issue - the "Design Script" button in Script Tasks isn't working. Research on this issue tells me it's because I'm missing an update to SQL2005 which I am downloading and installing now. Maybe it will fix the main issue of the package working fine except when being called by C#.

Just saying it doesnt work doesnt help us out much.

Which is why I provided enough information to show:

1) The package works by itself

2) What the package contained

3) The SQL statement was simple - no variables, no parameters, etc.

4) The package IS being executed from the C#

5) Part of the package runs successfully when being called from C# runs, and part of it doesn't run successfully

6) The package returns a FAILURE result code to the C#

There's a fine line between providing too many details and not providing enough. I provided what I believe is the correct amount of information for anyone who has been in the same situation to recognize that I have the same problem.

If no one has seen this situation, then I will provide more details, and logging, as you pointed out, will be a great way to get that detail. . . once I figure out logging.

A nice sticky would be a tutorial on logging and a push to have people include the appropriate logging output in their requests for help.

The update for SQL2005 is about 80% complete. As soon as it's complete, I'll reboot and see if it fixes my issues ( C# execution, Design Script issue, logging problems ). If not, I'll give logging another shot and provide the results.

Thanks,

-BEP

Installing the SQL2005 update fixed all of my issues.

I appreciate the replies.

-BEP