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?
--
- JJ, Eugene OR

[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]

Peter:

I tried doing it from the command prompt and ran into problems, even when I
tried it on the same SQL Server 2000 database server that runs the DTS
Execute Process task just fine. So, I'm not sure what this test really
tells us.

For example, clsrv121 is the name of our production database server with SQL
Server 2000 and from which the DTS package can bcp files just fine. When I
open a command prompt and type the following:

> bcp "Select * From CTSreal..Employee" queryout c:\BcpTest.txt -Sclsrv121 -U -P -c -r \n -t \t

I get the following error:
"Copy direction must be either 'in' or 'out'. Syntax error in 'queryout'."

So, I changed the text "queryout" to just "out" and tried the following at
the command prompt:

> bcp "Select * From CTSreal..Employee" out c:\BcpTest.txt -Sclsrv121 -U -P -c -r \n -t \t

This time, I get the error message:
"CTLIB Message: ...
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found. Establishing connection failed."

I verified the typing of the server name several times and even re-typed the
entire string manually several time to make sure there was no typo.

I'm not sure what I am doing wrong at the command prompt, but I'm also not
sure what that tells us since I am able to get the DTS package to work on
that same server. And the server/issue I am struggling with is a different
server: SQL Server 2005 on a different PC and dealing with SSIS. See my
confusion?

[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,

[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.

[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
Suga

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

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