BULK INSERT flat file with only one column

Hi,

I have a text file with a single column that i need to bulk insert into a table with 2 colums - an ID (with identity turned on) and col2

my text file looks like:

row1
row2
row3
...
row10

so my bulk insert i have like this:
BULK INSERT test FROM 'd:\testBig.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

but i get the error:

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

However, as you can see from the text file, there is only one column, so i dont have any field terminators.

Any ideas how to make this work?

Thanks.You'll probably need to use a format file to be able to skip columns.|||i dont think i want to skip any columns ??

the above method works if i have say 2 columns in my text file, and 3 columns in my table, since the first column in the table is an identity column.

my problem is that the bulk insert doesnt seem to be able to handle a text file with just one field per row. it seems to be looking for a field delimiter, when in fact there is only one field and then its the row delimiter etc etc.|||Create a table with one column which is used to store data from your text file and then do BULK INSERT.

BULK INSERT test FROM 'd:\testBig.txt'
WITH (
DATAFILETYPE = 'char',
--FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

At last, you can add a column for the ID.|||Of course you do, you want to skip columns in the target table by not populating them (I am talking about your identity field.) With "FORMATFILE = 'your_format_file' " you can define exactly what you want or don't want to populate.

Building a Truly Platform Agnostic SSIS Package (SQL Server and Oracle Interop)

As painful as it is proving out to be, I am trying to create a single package that is vendor neutral between SQL Server and Oracle.

At first, I thought that as long as I was going OLEDB in and out, and I ensured that my SQL syntax was generic enough, I'd be OK.

Well, not exactly. I am finding there is more to this. Specifically, metadata gets generated differently based on the source/targets.

For example, on an OLE DB Source task, datatypes will vary (i.e. NUMBER in Oracle vs Int in SQL). The job here is to pick the common denominator for the SSIS specific type. Time consuming, but doable, I think.

Another issue is on an OLE DB Desitnation. If you choose Table for Data Access Mode, this value gets written to the dtsx XML file. So, even when both RDBMS have the same schema (which is an obvious prereq) if choosing SQL Server "dbo.DimTable" will get written to the file and for Oracle "SCHEMA.DIMTABLE" will get written.

So, I am am wondering, what is the best way to address this?

My inital thought was using a dtsConfig file (which I am already using extensively) and set the target table names accordingly. This approach would have the added benefit of allowing for post-deployment configuration changes should a table name change, for example.

This section of the dtsx file shows the values of interest:

Code Snippet

<component id="138" name="OLE_DST_DimTable" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property'>http://www.microsoft.com/sql/support;4">http://www.microsoft.com/sql/support;4">
<properties>
<property id="139" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>
<property id="140" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">"ORASCHEMA"."DIMTABLE"</property>

<!-- More Properies -->
</component>

Ideally, I'd like configuration time control over the name attribute of the component element (highlighted) so that I can set the value of the property element with the OpenRowset attribute type (also highlighted). This way, presumambly as long as datatypes were generic enough, the mapping would just work.

But, in walking through the dtsConfig tree, I don't see these elements or attributes exposed.

I would sincerely appreciate any suggestions on how to accomplish this.

Rick

The OpenRowset property is not exposed through Configurations. You can try using the Table From Variable access mode. This goes through OpenRowset with the table name stored in a variable. You can then use Configurations to update the variable.

|||Great idea, I will try that thanks.|||

Hi Ted,

How do you recommend I go about setting the variable? I have used the Script Task in the Control Flow, but Script Component Task seemed somewhat unintuitive given its directional contexts (source, destination, transform). I don't want to put the script in the Control Flow aspect because I want to scope the variable iniatialization to the specific Data Flow Task that is in context at the time.

It seems that I want to just put a script in the data flow task that checks the provider value and if SQL, sets the variable to "dbo.TableName" and if Oracle sets the variable to "SCHEMA.TABLENAME".

You mention "You can then use Configurations to update the variable." Can you please elaborate?

Thanks,

Rick

|||

RickGaribay.NET wrote:

Hi Ted,

How do you recommend I go about setting the variable? I have used the Script Task in the Control Flow, but Script Component Task seemed somewhat unintuitive given its directional contexts (source, destination, transform). I don't want to put the script in the Control Flow aspect because I want to scope the variable iniatialization to the specific Data Flow Task that is in context at the time.

It seems that I want to just put a script in the data flow task that checks the provider value and if SQL, sets the variable to "dbo.TableName" and if Oracle sets the variable to "SCHEMA.TABLENAME".

You mention "You can then use Configurations to update the variable." Can you please elaborate?

Thanks,

Rick

Package configurations are a mechanism to set various component and variable properties through external means, such as XML configuration files. Take a look in SQL Server Books Online: http://msdn2.microsoft.com/en-us/library/ms141682.aspx

A few additional links:

http://sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx http://www.sqlis.com/26.aspx|||

Thanks Matthew.

We came up with a potential solution simply involving the initialization and assignment of named variables with the specific fully qualified table name based on the provider. To your point, the added benefit of this is that if we wanted to ever override these values, we could simply expose them via package configurations.

Thanks again,

Rick