BCP skip field if not an INT? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

BCP skip field if not an INT?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2009-05-06 : 10:14:57

Hello,

Is there away to make BPC skip a field if the value being copied is not an INT..
(Im building my BPC task using SQL Server Business Intelligence Development Studio)

I want the BPC to skip just the filed but not the full record.

Thanks
Shane

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 10:23:37

Insert complete file into a staging area and the do your tests and updates.
When done, insert into production area.

E 12°55'05.63"
N 56°04'39.26"

Shanew
Starting Member

20 Posts

Posted - 2009-05-06 : 10:32:00

quote:
Originally posted by Peso

Insert complete file into a staging area and the do your tests and updates.

Im not sure how that will make BPC skip fields...
I have a coworker imporing the CSV file now into Access and then from Access to SQL. I want to get away from using Access and just use BPC. Some how when she imports with Access it skips fields with the worng/non INT value but not the full record.. this is what i want BPC todo.

Thanks for any help.
Shane

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-05-06 : 15:39:45

It won't make bcp skip rows, you filter the rows in sql

The staging table would have the column defined as varchar.
Load all the data into the staging table
move the valid data into the production table

insert into production table
select *
from staging table
where isnumeric (column1) = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-06 : 16:36:56

In the staging area, you update those records to NULL for the column that is not having a INT value.
Then you instert into production area. Doing this in two steps makes you have more control over the process that doing this in one step.
Because if you want to do this in SSIS, you will need a calculated column to replace all columns you want to check.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources