Best Oracle source provider?

Hello,
I am using a massive Oracle db in SSIS and am trying to figure out the best way to increase performance in moving data from this db to sql server. As far as Oracle sources, which seem to have the best performance?
Thanks,
AdrianWhat did you mean by the 'Oracle sources'? We tried using OLEDB for oracle and the performance is really poor.|||I've had that experience as well with OLEDB. What I meant by sources is either the .NET OracleClient, Microsoft OLEDB provider for Oracle, or Oracle provider for OLEDB. I believe these are the only 3 options to connect to Oracle. I can't even use the Oracle provider for OLEDB without numerous errors, and have found that the other 2 are quite slow as well. So I was just wondering what other people's experiences were, and if they found 1 to be superior over another. And maybe some input from the SSIS developers as to what their suggestions are.
Thanks,
Adrian|||The OLEDB providers for Oracle that are currently available aren't terribly fast. Have you considered replicating (a subset of) your data to SQL Server from Oracle before (or at the beginning of) your package execution? This topic in BOL is probably where to start: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/2e013259-0022-4897-a08d-5f8deb880fa8.htm|||Hello,

We have built a super fast connector for Oracle for SSIS. We have been able to achieve about 30 times performance improvements over the OLEDB Oracle provider. E.g 1 million rows, we loaded in about 2:20 mins. Let me know if you are interested in beta bits.

Regards
Sidharth
http://www.persistentsys.com
siddossy@.hotmail.com

Best Oracle source provider?

Hello,
I am using a massive Oracle db in SSIS and am trying to figure out the best way to increase performance in moving data from this db to sql server. As far as Oracle sources, which seem to have the best performance?
Thanks,
Adrian
What did you mean by the 'Oracle sources'? We tried using OLEDB for oracle and the performance is really poor.|||I've had that experience as well with OLEDB. What I meant by sources is either the .NET OracleClient, Microsoft OLEDB provider for Oracle, or Oracle provider for OLEDB. I believe these are the only 3 options to connect to Oracle. I can't even use the Oracle provider for OLEDB without numerous errors, and have found that the other 2 are quite slow as well. So I was just wondering what other people's experiences were, and if they found 1 to be superior over another. And maybe some input from the SSIS developers as to what their suggestions are.
Thanks,
Adrian
|||The OLEDB providers for Oracle that are currently available aren't terribly fast. Have you considered replicating (a subset of) your data to SQL Server from Oracle before (or at the beginning of) your package execution? This topic in BOL is probably where to start: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/2e013259-0022-4897-a08d-5f8deb880fa8.htm|||Hello,

We have built a super fast connector for Oracle for SSIS. We have been able to achieve about 30 times performance improvements over the OLEDB Oracle provider. E.g 1 million rows, we loaded in about 2:20 mins. Let me know if you are interested in beta bits.

Regards
Sidharth
http://www.persistentsys.com
siddossy@.hotmail.com

Best approach

I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.

Source server SQL 2000 database allows NULL values in columns.

Destination Server also SQL 2000 but the database required a value in each column.

So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.

I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.

Maybe there is a better way. Suggestion and comment appreciated.

Ryan

Try using the T-SQL ISNULL() function in your initial query, replacing any null values with an empty string or whatever...

ISNULL(myField,'')

beginner question

Hi, folks:
I am looking into using SSIS to create a OLAP database. How easy it is to write a package to re-pull some fact data from the source databae base on some flags. What happend is we are planning to roll up some minute by minute data into hourly averages using SSIS but user is allowed to modified the minute by minute data (maximum is 90 days). Say if we set up some flags, and the package comes in every hour to check for these flags and only re-ETL the changed one. Can I be done or is there better way to do it.SSIS can push data straight into an OLAP cube from the pipeline. I'm not sure about updating values that are already there though (if that is what you are asking).

-Jamie|||Yes, that's updating the OLAP values is what I am asking. We do gas-flow calculation. For each well, we insert minute-by-minute data. Out of a thousand wells, only 10 wells will have the minute by minute data changed, and all I plan to do is to flag these 10 wells and re-ETL them.

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many Thanks
On Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed
|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegro ups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx