Execute Process Task & FTP -- NEED HELP 911

Guys!

Need some help on to figure out some solution for my problem. I have Execute process task in my DTS to execute the BAT file to connect to FTP site using simple DOS commands. All, i need, after executing the BAT file using Execute process task, need to send an Email to the administrator about the status of execustion.

The problem occured here, I specifically, take out the password field in the BAT file to connect to the FTP site. In general, the batch file could not connect to FTP site, since i taken out the password field in the BAT file. And after that, in DTS package i tried executing the Execute process Task seperately, and all i can get the messages "command executed successfully".

Based upon the status of the execute process task, I am writing a "Not Successful" message in the table. From that, table i am using the stautus to send an Email to an Administrator.

So Folks please help me out, how can I sql server know the status of the Execute Process task status, whether is it Successful or not.

In my case, this execute process task simply says successful, een though the BAT file could execute the command.

Guys help me out and I am in a little presure to accomblish this ASAP.

Baski.Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Hey Bratt,

I totally understand your way of thought and appriciate it. Great, Since, the DTS which we wrote was completely huge. So any thoughts about error handlimg or pass the error code for Execute process task.

Is there anyway, can i able to check the status of the Execute process task as well as BAT file, that called inside the Execute Process task.

Thanks for your time Guys.

Thoughts are most welcome.

Baski.

Originally posted by Brett Kaiser
Hey, the bat file did complete successfully..it didn't blow up.

It just didn't do what you wanted...

Why not echo the results of the bat to a file and interogate that?

Or set up 2 osql bat files that are called within the ftp..one for success one for failure and have them insert a row in to a log...

Just an idea...

But why use bcp and a sproc?

That's what I would do...|||Check inside the bat file

f errorlevel 1 goto ERROR
if errorlevel 0 goto SUCCESS

:ERROR
echo transfer failed
goto ENDFTMS

:SUCCESS
echo transfer successful
goto ENDFTMS

:ENDFTMS
echo batch program complete|||Bratt,

Good to hear your thought on this. Well, I believe you aware that, the DOS BAT file is executing in FTP location. Also, is there any thought about, CHECKING THE FILE EXISTENCE IN THE FTP LOCATION USING BAT FILE.

Once, i figure out if the xyz.abc file is located in the FTP file, I can pass the parameter or return code as 0/1 to Execute process task to find whether the TASk is successful or not.

Make sence? GUYS IS THERE ANY THOUGHT TO CHECK THE FILE EXISTENCE IN THE FTP LOCATION & RETURN WITH CODE 0 OR 1 AFTER EXECUTING THE BAT FILE.

THANKS IN ADVANCE GUYS.|||Yeah, in a sproc

Delete From Ledger_Folder

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Select @.Command_String = @.FilePath + '\*.*'

-- Select @.Command_String

-- Insert Into Ledger_Folder exec master..xp_cmdshell @.Command_String

Insert Into Ledger_Folder exec master..xp_cmdshell 'Dir d:\Data\Tax\SmartStreamExtracts\*.*'

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- select * from ledger_folder

Delete From Ledger_Folder_Parsed

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 6
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

Insert Into Ledger_Folder_Parsed (Create_Time, File_Size, File_Name )
Select Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_outp ut,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From Ledger_Folder
Where Substring(dir_output,1,1) <> ' '
And (Substring(dir_output,1,1) <> ' '
And Substring(dir_output,25,5) <> '<DIR>')

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 7
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

-- Get File For the 1st Month of a Quarter

If ( Select Count(*)
From Ledger_Folder_Parsed
Where Substring(File_Name,16,2)= @.Month1 And Substring(File_Name,11,4)= @.Proof_Year

) = 0
BEGIN
SELECT @.Error_Loc = 8
SELECT @.Error_Message = 'First Monthly File Not Found. Check Syntax for File Name. '
+ ' Syntax is: ' + @.Fn1 + '_yymmdd.txt'
SELECT @.Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END|||Hey Bratt,

Thanks a Lot and ofcourse, I may not be using the sproc for checking the file existence. Since, we have been trying to upload the file to a MAINFRAME BASED FTP SITE.

So it would be more welcome, if you provide some solution or step based on the DOS mode or command line statements.

Thanks in Advance.

Baski.|||You can also use the ActiveX Script task in dts to test for file existence using the fso object - this would be cleaner. Why don't you use the ftp task in dts rather than using a batch file ?|||The Problem is you may not be using the FTP task for upload or download function in Mainframe based FTP site. SO i have to use the DOS version of file existence as BAT file in Execute process task.

Thanks

Execute Integration Package from a C# Solution

Hi all
Does somebody know how I can execute a SSIS Package on a SQL 2005 Server?
I need to execute from a C# Solution which is running on a client.
Thanks for any comments
Best regards
Frank Uray

On the server, configure an unscheduled SQL Agent job. On the client, use ADO.NET to invoke the sp_startjob system stored procedure.

It would also be possible to create a simple CLR stored procedure that uses the Integration Services API to launch a package on the server. I am working on the sample code for this today.

-Doug

|||

Hi Doug

You mean that there is no Namespace for accessing SSIS Packages ?
On SQL Server 2000, there was this need dts Namespace (API) ...

Thanks anyway

Best regards
Frank Uray

|||

Yes, certainly there is a very complete Integration Services API, and you'll note that I mentioned it in my reply.

However I'm not certain, without testing, whether you can use the API from a client to execute packages. As you know, a package runs on the computer it is called from (therefore, in this case, the client), and NOT on the computer where it is stored.

Integration Services is now a server platform and no longer a redistributable tool like DTS. Although installing Tools on the client gives you enough of SSIS to design packages, they cannot normally be executed outside the IDE.

Running the package on the server where it is stored -- as the SQL Agent solution accomplishes -- actually avoids a lot of common issues with paths to resources that are not available from client computers.

-Doug

|||

Hi Doug

Thanks again for your answer !

Yes, I have seen it on your first replay but I don't want to execute through
a stored procedure ...
The idea is to execute the Package on the server,
as if you execute a stored procedure. They also don't run on the client.
For example: I make a SQL connection and than start the package async. on
this created connection ...

Do you have a sample code for executing a SSIS Package in a
stored procedure on the 2005 server?

Best regards
Frank Uray

|||

Using the API will launch the package wherever the API code runs...that's why you would want to put the code in a CLR stored procedure to ensure that it runs on the server.

I won't have my sample finished and tested until later today, but it's basically the same "run a package" code shown in "Running an Existing Package from a Client Application ," wrapped in a CLR procedure with input params for package name etc as needed.

using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
class Program
{
static void Main(string[] args)
{
string pkgLocation;
Package pkg;
Application app;
DTSExecResult pkgResults;

pkgLocation =
@."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();

Console.WriteLine(pkgResults.ToString());
Console.ReadKey();

}
}
}

|||

Hi Doug

Thanks a lot, that is helping !

Would be happy to get your sample when you have it finished.

Best regards
Frank Uray

|||

Frank,

It looks like my idea of a CLR stored procedure to launch packages using the Integration Services API may not be feasible. I've run into too many errors and security complications trying to register the Runtime Wrapper interop assembly and the Runtime assembly in SQL Server before I can even start on the simple code.

It sounded like a simple and useful concept, but SQL CLR is extremely fussy about what's going to run inside the SQL process space (understandably).

-Doug

|||Doug,

I ran into the exact same issue you did. I needed to invoke a package from an asp.net app and wanted to do it via a CLR stored proc. Oh well...

I documented all my SSIS experiences in a series of articles:

http://www.blognet.info/weblogs/entry.php?u=adhalejr&e_id=849

Maybe that will provide some pointers.

Donnie