SSIS Execute Process Task and BCP

[JJ of Eugene OR]

Fellow SSISers:

I have a fully working DTS package that runs an Execute Process task. The
Execute Process task bcp's the output of a stored procedure as an XML file.
When I try to translate the same functionality into an SSIS Execute Process
task, the task fails.

In DTS, the properties are filled in this way:

Win32 Process: bcp
Parameters: "Exec('Exec
clsrv121.ctsReal.dbo.proc_absFacilToState_AbsOut')" queryout
P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer\LCOG_tblABUSE.xml -c -r -t -Sclsrv121 -T

I'm not very familiar with bcp or command-line utilities. I just fiddled
with the above until I was able to get it to work. Thus, I wasn't suprised
when I couldn't get it to work correctly in SSIS right away. So, I did a lot
of tinkering with the syntax. even so, I still get an error. Here is the
latest variation for the properties in SSIS:

Executable: bcp.exe
Arguments: "Exec('Exec
clsrv197.ctsReal.dbo.proc_absFacilToState_AbsOut')" queryout
P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer\LCOG_tblABUSE_clsrv197.xml -c -r -t -Sclsrv197 -T
Working Directory:
P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer

The error messages look like this:

Error: 0xC0029151 at Export Abuse_Invest, Execute Process Task: In Executing
"bcp.exe" ""Exec('Exec clsrv197.ctsReal.dbo.proc_absFacilToState_AbsOut')"
queryout
P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer\LCOG_tblABUSE_clsrv197.xml
-c -r -t -Sclsrv197 -T" at
"P:\Access_MS\App_CTS_SDS\TransferData\Abuse_FacilityTransfer", The process
exit code was "-1" while the expected was "0".

FYI: In both packages (both the DTS and SSIS versions), I have a connection
set up to the database which contains the stored procedure referenced above
(proc_absFacilToState_AbsOut), though I don't know if that is necessary or
not.

I can't figure out from the error message just what it is that SSIS doesn't
like. I thought that I would try to simplify things to see if that would
produce a different error message or maybe even work. So, I created the
following SSIS execute process task:

Executable: bcp.exe
Arguments: "Select * From Employee" queryout C:\MyTest.txt -c -r -t
-Sclsrv197 -T
Working Directory: <blank>

And I got the following error message:

[Execute Process Task] Error: In Executing "bcp.exe" ""Select * From
Employee" queryout C:\MyTest.txt -c -r -t -Sclsrv197 -T" at "", The process
exit code was "-1" while the expected was "0".

I'm sure I'm just not getting the syntax right, but after the billion
mutations I've already tried, I'm out of ideas. Any help out there?

[privatenews]

Hello,

Please make sure you could run the bcp command properly in the Command
Prompt. For example, I used the following command to test:

bcp "SELECT au_lname FROM pubs..authors" queryout c:\dell\myfile.txt -S
sha-petery-2003 -U -P -c -r \n -t \t

I added the following as Arguments and it works fine.

"SELECT au_lname FROM pubs..authors" queryout c:\test\myfile.txt -S
sha-petery-2003 -U -P -c -r \n -t \t

Please try the command above on your side to tet the situation.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[JJ of Eugene OR]

This post might be inappropriate. Click to display it.

[privatenews]

Hello,

I just want to check if bcp could work properly.

bcp "SELECT au_lname FROM pubs..authors" queryout c:\dell\myfile.txt -S
sha-petery-xp -U -P -c -r \n -t \t

You shall be able to run above command by using a bcp.exe in SQL 2000

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"

For example, if server if SQL 2000, it has pubs sample database, the
following command shall work properly

"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT
au_lname FROM pubs..authors" queryout c:\dell\myfile.txt -S sha-petery-xp
-U -P -c -r \n -t \t

Once you could run above command properly in CMD, you shall be able to run
it in SSIS package. It is a little weird that you could run it in DTS
package in SQL 2000 that you could not run in CMD.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[JJ of Eugene OR]

Peter:

Your posting gave me the clue I needed to get this to work. I didn't
understand what you wanted me to test, but I thought you might be making a
point about fully qualifying the bcp.exe file. I hadn't realized that this
might be important since I thought that the program was not having trouble
finding the executable. But on the theory that maybe there is more than one
bcp.exe file out there or something like that, I did the following test: I
found the parallel location for the executable in SQL Server 2005:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe

I put the above text in the SSIS package, in the 'execute process' task
under the 'executable' property. I kept all of the other property settings
the same as I had originally posted. This solved the problem! My SSIS
package now works as smoothly as my old DTS package. Very nice.

Thanks a bunch for your help.

[privatenews]

Welcome! :-)

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

[sugavaneswaran]

I have a Execute process task

I have set the following in the task:

Executable: C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe

Arguments: pubs.dbo.GenReligion out C:\IG\abc.dat -c -T –STestServer

when i run the package i get the below error

[Execute Process Task] Error: In Executing "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe " "pubs.dbo.GenReligion out C:\IG\abc.dat -c -T –STestServer" at "", The process exit code was "1" while the expected was "0".

However when i run the same in command prompt it runs successfully.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>bcp pubs.dbo.GenReligion out abc.dat -c -T –STestServer

Can anyone please point out the issue?

Thanks
Sug

From http://www.developmentnow.com/g/103_2006_4_0_0_745519/SSIS-Execute-Process-Task-and-BCP.ht

Posted via DevelopmentNow.com Group
http://www.developmentnow.com/g/