January 19, 2016 at 11:11 am
Hi,
I am trying to run a SSIS package as a job which copies data from sql view into a csv file using Execute process Task to a location (C:\IntegrationBCPData )
and from this location I have another Execute Process Task that will load the csv file to SQL database.
When I run the SSIS package from a SQL job, there is no error but the csv files are not created at C:\IntegrationBCPData and the job reports as success.
I tried copying the project into the server and opened using VS 2010 installed on the server . When I run it locally from my machine or from the server, it works as expected. But not quite as a job.
Details from Execute Process Task Editor :
Executable: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe
Arguments: "SELECT * FROM PWA.dbo.ABC_AssociationView" queryout "C:\IntegrationBCPData\LoadAssociation.csv" -S abcd\SPCONTENT -T -c -U xxxx -P yyyy
Working Directory :
Rest all as default
From CSV to SQL import :
Executable: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe
Arguments:[XYZ_V2].[Tbox].[LoadGeoMarketAssociation] in C:\IntegrationBCPData\LoadAssociation.csv -S newserver -T -c -U xxxx -P yyyy
Please help ,
Thanks,
PSB
January 19, 2016 at 11:34 am
PSB (1/19/2016)
Hi,I am trying to run a SSIS package as a job which copies data from sql view into a csv file using Execute process Task to a location (C:\IntegrationBCPData )
and from this location I have another Execute Process Task that will load the csv file to SQL database.
When I run the SSIS package from a SQL job, there is no error but the csv files are not created at C:\IntegrationBCPData and the job reports as success.
I tried copying the project into the server and opened using VS 2010 installed on the server . When I run it locally from my machine or from the server, it works as expected. But not quite as a job.
Details from Execute Process Task Editor :
Executable: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe
Arguments: "SELECT * FROM PWA.dbo.ABC_AssociationView" queryout "C:\IntegrationBCPData\LoadAssociation.csv" -S abcd\SPCONTENT -T -c -U xxxx -P yyyy
Working Directory :
Rest all as default
From CSV to SQL import :
Executable: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\bcp.exe
Arguments:[XYZ_V2].[Tbox].[LoadGeoMarketAssociation] in C:\IntegrationBCPData\LoadAssociation.csv -S newserver -T -c -U xxxx -P yyyy
Please help ,
Thanks,
PSB
I don't have an answer for you, but I do have a question: why aren't you doing this via a single data flow from server 1 to server 2?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 19, 2016 at 11:38 am
We are doing a BCP because of the amount of rows to be fetched from Server 1 to Server 2 . The time shrinks to 2-3 minutes from 30 -45 minutes and we need to run it every 30 minutes.
January 19, 2016 at 11:39 pm
PSB (1/19/2016)
We are doing a BCP because of the amount of rows to be fetched from Server 1 to Server 2 . The time shrinks to 2-3 minutes from 30 -45 minutes and we need to run it every 30 minutes.
Sounds like you may not have used OLE DB Destination with Fast Load enabled or something else was going on. The SSIS OLE DB Destination with Fast Load and BCP use the same SQL Server API, namely the Bulk Load API, so they should perform about the same on the load step. If you went from Server1 direct to Server2 in an SSIS Data Flow and skipped writing the data to disk so you could then use BCP you'd be saving cycles and so SSIS should end up being faster than what you're currently doing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply