Problem with Look up

Hi all;

I have created a SSIS package to import data from flat file to database table, which consists of
Flat file Source -> Look up -> OLE DB Destination.
I'm using Look Up in order to avoid duplicate records.
My database table contain around 35,00000 s of rows.
So when I'm running the package the above amount of data goes to Lookup cache, which require a large storage memory.
So I'm not able to execute my package.
I need to run the package in order to update my database.
So is there any solution, in which I can check the duplicate records to get into the database and time consumption to run the package will be less.

Thanks in advance...

Be sure that you are using a SQL statement to only select distinct keys from the table:

select primary_key from table

Then you just simply join the key fields.|||

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

|||

Andy Leonard wrote:

Hi Sanidha,

You may want to consider key staging the lookup table. Prior to performing the lookup against a 35 million row table, create a staging table containing only the columns (and rows, if identifiable) needed to perform the lookup, then use it instead of the full table.

Hope this helps,
Andy

Why would this matter? Selecting the primary key of the full table (should be indexed!) should be less work than staging the keys first, and then selecting from there. That sounds like an extra, unnecessary step to me and will, in turn, cost more than my approach.

Am I missing something? Unless of course, you're talking about performing a SQL join against that staging table so as to avoid caching the keys in memory.|||

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

|||

Andy Leonard wrote:

My apologies Sanidha,

I thought the lookup table contained too many rows to fully cache - it was an assumption on my part.

Andy

Well, yeah, that's the one thing we don't know. I want to be sure the user is selecting *just* the key, and not the whole table.

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.
Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>
sql

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type =
ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote 
3;"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I
got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type =
> ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote &#
123;"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN"
I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

Problem with importing TXT file to SQL Server

I have a .TXT file that I would like to import to SQL Server on my machine.
The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
(columns). The size of the TXT file is 4 KB.
A sample of 1 of the row in the TXT file is as follows:
"A","B"," B1","B0001","F1","OMAAA,"D",.........
In SQL Server I created a new database called "maint". I right click - All
Tasks - Import Data.
On the question "From where do you want to copy the data, I selected "Text
File" for "Data Source" and enter the filename "C:\MN.TXT"
On the file format, I selected all the default : "Delimited", File Type = ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip Rows
= 0.
On the Column delimiter, I selected the default = Comma
On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
and selected my server (Local) and my database "maint".
When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got an
error "Not enough storage is available to complete this operation". It
successfully created the tables and fields (columns) in the "maint"
database.
I have 24 G of free space in my machine.
Am I importing this TXT file incorrectly ?
Thanks.Sorry, I posted too early. I found out my problem. I have to click on
"Transform" to change the field from varchar(8000) to varchar(30). This
fixed the problem. Thanks.
"fniles" <fniles@.pfmail.com> wrote in message
news:%23LxAn89rEHA.3428@.TK2MSFTNGP11.phx.gbl...
>I have a .TXT file that I would like to import to SQL Server on my machine.
> The .TXT file is comma (,) delimited, with only 4 rows and 185 fields
> (columns). The size of the TXT file is 4 KB.
> A sample of 1 of the row in the TXT file is as follows:
> "A","B"," B1","B0001","F1","OMAAA,"D",.........
> In SQL Server I created a new database called "maint". I right click - All
> Tasks - Import Data.
> On the question "From where do you want to copy the data, I selected "Text
> File" for "Data Source" and enter the filename "C:\MN.TXT"
> On the file format, I selected all the default : "Delimited", File Type => ANSI, Row Delimiter = {CR}{LF}, Text Qualifier = Double Quote {"}, Skip
> Rows = 0.
> On the Column delimiter, I selected the default = Comma
> On the destination, I selected "Microsoft OLE DB Provider for SQL Server"
> and selected my server (Local) and my database "maint".
> When I run it, on the step "copy data from MN to [maint].[dbo].MN" I got
> an error "Not enough storage is available to complete this operation". It
> successfully created the tables and fields (columns) in the "maint"
> database.
> I have 24 G of free space in my machine.
> Am I importing this TXT file incorrectly ?
> Thanks.
>
>

problem with importing the excel file SQL Server 2k5

I got the following error when importing file to sql server 2005. I reinstalled the MDAC as well.

Please help.

TITLE: SQL Server Import and Export Wizard

An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.

ADDITIONAL INFORMATION:

Exception has been thrown by the target of an invocation. (mscorlib)

The connection type "EXCEL" specified for connection manager "{E3861233-443A-439A-BB8D-2777D84DB343}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({C3728B0D-B172-4246-9B14-6EEDAB60F191})

The connection type "EXCEL" specified for connection manager "{E3861233-443A-439A-BB8D-2777D84DB343}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({C3728B0D-B172-4246-9B14-6EEDAB60F191})

BUTTONS:

OK

Have you removed any connection manager that was used by some task(s)?

Since the error message shows the ID of the connection manager; it looks like that a connection manager was delete it but the references to it have not been removed. Make sure all tasks/source/destiantions in the mapping are pointing to a valid connection manager.

|||what do u mean by connection manager? I've no idea.|||Ok. Let's back to the basics. How did you create the package? are you running it in BIDS?|||

Could you tell us what version of SQL Server you have installed (RTM, SP1, SP2)?

Also, could you describe how you configured each page of the Import/Export wizard?

Thanks,

Bob

|||

I am having the similar problem: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1867309&SiteID=1

I would like to provide a greater detailed info if needed. I am looking for solutions.

Problem with importing a CSV into database

Hi,

Presenlty Iam facing problem while trying to import a .csv file into the database.
Issue is that... I am able to achieve my functionality when i import a file from D:\
But when i run the same program... and try to import from C:\ its throwing an exception.

I am coding in VB (Visual Studio 2005 ) and database is Microsoft SQL SERVER 2005.
My project is currently running on local machine in C:\Inetpub\wwwroot

Im using the following code to query the database...

SELECT employeeid, IDCubeID, ClockTime, ClockType, CreatedDate, Status, EntryType, ' ' as Remark,YardID,YardName,' ' as UserModify,' ' as UserCreate,DateModify,JobNo FROM & File1.PostedFile.FileName.ToString

where File1.PostedFile.FileName.ToString = D:\RawData_ClockTime.csv

when i import from C drive .. (File1.PostedFile.FileName.ToString = C:\RawData_ClockTime.csv)
it is throwing an exception as follows...

The Microsoft Jet database engine could not find the object 'RawData_ClockTime.csv'. Make sure the object exists and that you spell its name and the path name correctly.Always remember that C: drive on your local machine and on your server is not the same.

Use \\machine_name\.... instead.

Good Luck.

Problem with import data from excel format file.

Hi All

I have a huge excel format file wants to export to sql server
database. One of the field has combination of numeric and
alphanumeric. When I import the excel format to sql server database,
the numeric is successfully imported but the alphanumeric does not
import at all.

Does anyone know how to solve my problem?

I am using SQL 2k and the spreadsheet is excel 2003

Thanks

Rich

You may have to use Data Conversion to convert data suitable to your SQL data type.sql

Problem with import data from excel format file.

Hi all,

I have a huge excel format file wants to export to sql server
database. One of the field has combination of numeric and
alphanumeric. When I import the excel format to sql server database,
the numeric is successfully imported but the alphanumeric does not
import at all.

Does anyone know how to solve my problem?

Thanks.

-HS PhuahOn 2 Dec 2003 02:21:24 -0800, hsphuah@.usa.com (Hon Seng Phuah) wrote:

>Hi all,
>I have a huge excel format file wants to export to sql server
>database. One of the field has combination of numeric and
>alphanumeric. When I import the excel format to sql server database,
>the numeric is successfully imported but the alphanumeric does not
>import at all.
>Does anyone know how to solve my problem?
>Thanks.
>-HS Phuah
Is the data in that column in the first row to be processed completely
numeric? Maybe if you sorted it such that this was alpha, sql server
would recognize the column as being alphanumeric and bring the lot in.

I'm assuming that you haven't defined the table in your db, and are
letting some tool do it for you.|||hsphuah@.usa.com (Hon Seng Phuah) wrote in message news:<3898598f.0312020221.e23a72c@.posting.google.com>...
> Hi all,
> I have a huge excel format file wants to export to sql server
> database. One of the field has combination of numeric and
> alphanumeric. When I import the excel format to sql server database,
> the numeric is successfully imported but the alphanumeric does not
> import at all.
> Does anyone know how to solve my problem?
> Thanks.
> -HS Phuah

You really need to give some more information - what version of SQL
Server, how are you importing the file, what format are the source
data in, what is the DDL for the target table, what does "not import"
mean?

One possibility is that the data type of the target column doesn't
allow alpanumeric characters. Another is that you're seeing this
issue:

http://www.sqldts.com/default.aspx?254

Simon

Problem With Excel Import in Execute Phase

Hi All

I have a SSIS package running and tested fine on my desktop. However when I deploy the package to my server I get the error given below.

SSIS package "CR Sec Watch List 2.dtsx" starting.

Information: 0x4001100A at Transform Master Files: Starting distributed transaction for this container.

Information: 0x4004300A at Transform Master Files, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Transform Master Files, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Transform Master Files, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Transform Master Files, DTS.Pipeline: Execute phase is beginning.

Error: 0xC0202009 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

Error: 0xC0208265 at Transform Master Files, Excel Source [1]: Failed to retrieve long data for column "NKoreaPoi".

Error: 0xC020901C at Transform Master Files, Excel Source [1]: There was an error with output "Excel Source Output" (9) on component "Excel Source" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at Transform Master Files, Excel Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output "Excel Source Output" (9)" failed because error code 0xC0209071 occurred, and the error row disposition on "component "Excel Source" (1)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error: 0xC0047038 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

Error: 0xC0047039 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.

Error: 0xC0047021 at Transform Master Files, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.

Information: 0x40043008 at Transform Master Files, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Transform Master Files, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Transform Master Files, DTS.Pipeline: "component "SQL Server Destination" (20)" wrote 0 rows.

Task failed: Transform Master Files

Information: 0x4001100C at Transform Master Files: Aborting the current distributed transaction.

Warning: 0x80019002 at Process Master Files: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (8) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "CR Sec Watch List 2.dtsx" finished: Success

For some reason it tells me that it not able to retrive the long data for a column. The column mentioned in the error contains special characters.

Once again, there seems to be no problem with the package as the package runs fine on my desktop.

Any help or clues as to why this is happening on my server is greatly appreciated.

Regards

Ryan

Jet Engine for Excel guesses data type of a column. Please see threads on TypeGuessRows and IMEX to understand how Jet works, and its limitations, and how to tune it to work for you.

Setting TypeGuessRows to 0 would force Jet to scan all rows in the Excel to guess the type. You may also need to set the Defalut Type to Text.

Using Imex=1 in your Connection string is essential to get mixed types to default to Text.

Other Potential Problems:

Maybe the excel file on your PC contains Long Data, and the one you used on the server does not contain even a single row with Long Data in your NorthKorea column.

Maybe the Jet Engine Settings have different values on your PC and your server.

If the same file is giving errors, even when Jet Engine settings are same, then I would speculate if it is a Locale problem?

HTH

Kar

|||

Thanks Kar.

You recommendation worked. Setting typeguessrows to 0 did the trick.

Regards

Ryan

problem with DTS and text file

Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.
Someone any idea what's wrong?does your comma separate decimals from the significant digits?|||Originally posted by troj
Hello,
i have the following problem:
i import a flat text file with DTS everything look wel but my comma disappears in the values in the SQL Table. The field is defined as FLOAT.

Someone any idea what's wrong?

Nothing's wrong...although I'm suprised it didn't blow up...comma's in numbers are just a presentation issue...unlike precision...

But for example...you can't do..

CREATE TABLE myTablefloat (col1 float)
INSERT INTO myTablefloat(col1) SELECT 1,203.45

But for some reason in a text file it allows it|||Originally posted by ms_sql_dba
does your comma separate decimals from the significant digits?

it are money values, for example 10,22
in sql table it becomes 1022|||Originally posted by troj
it are money values, for example 10,22
in sql table it becomes 1022

What's the collation, language of your sql server box?

Sounds like it doesn't handle comma as precision.

Load the data in to a work table that the column defined as varchar..

Then populate your final table by doing an insert and convert your data..something like

SELECT CONVERT(float,REPLACE(col1,',','.'))|||If changing datatype is a problem you can even use the ActiveX replace function in the DTS transformation to replace "," by "."

Problem with DTS

I am using a DTS package to import data from primary server to secondary
server to update the changes occured in few tables and both located in a
different location. The problem is that whenever this DTS fails to execute i
will loose even the existing datas in secondary server since it delete the
existing data before inserting, how to overcome this? OR Is there any better
ways to implement this?
Thanks in advanceHi
Well, I'd transfer the OLD data before deleting into a temporary table and
in case of failure ( in order to not loose the data) nove the data back.
What's error do you get when you run the DTS and it failed?
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
>I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
> i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any
> better
> ways to implement this?
> Thanks in advance
>|||It just showing "Job Failed". It happens whenever if there any problem with
internet or network.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Hi
> Well, I'd transfer the OLD data before deleting into a temporary table
and
> in case of failure ( in order to not loose the data) nove the data back.
> What's error do you get when you run the DTS and it failed?
>
>
> "imtiaz" <Imtiaz@.microsoft.com> wrote in message
> news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
execute
the
>|||Ok, so you can specify an OUTPUT file under Advanced Tab in the Step's
definition.It will give the error desciption
You will have to introduce some logic behind like if the job's step is
failed go to the next step and do soemthing
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:uFH027KfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> It just showing "Job Failed". It happens whenever if there any problem
> with
> internet or network.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> and
> execute
> the
>|||Can you put Delete and Insert into a single transaction? If one step fails,
just roll back the whole transaction.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>|||Try www.sqlscripter.com to transfer your data.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>

Problem with decimal point

I have got txt file and I am trying to import this file into the database. For that action I use SQL Server Import and Export Wizard. I use Locale Czech and CodePage 1250. In the text file is the column in that format: 18152.65 - it is number with decimal point. When I use BCP utility for importing data, I use datatype decimal(10,2) and everything is OK. But when I try to use Import and Export Wizard, I choose for that column datatype numeric (DT_NUMERIC - precision 10, scale 2), the Import doesn't start and occurs the error:

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "PRED_CEL " returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Is it always a precision of 10, and a scale of 2? You may have some inconsistent data. There's two things you could do.

1) Increase the precision and scale.

or

2) Create a custom SSIS package, and redirect the error rows to see where the data inconsistency is. The Import/Export Wizard is great for straight forward tasks, but you'll find that with the smallest blib you'll need to create a custom package.

|||The import is very picky about number formats when importing from flat files. It will NOT import zero padded numbers into a decimal field. Even though it will implicetly convert it internally.

For example:
3.99 - OK
003.99 - FAILS
+003.99 - OK
-003.00 - OK

If you import "003.99" into a varchar(12) and then update a decimal(10,2), with the value of teh varchar, it will work fine.

Problem with Date query

Hi!

I'm trying to query an Informix linked server to import data into a SQL database.

The OLE DB Provider is ifxoledbc. I can do general queries such as

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t ')

but when I try to add the where clause

Select * from openquery(INFLINKMON, 'SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006"')

I get the error:

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate="04/12/2006"" for execution against OLE DB provider "Ifxoledbc" for linked server "INFLINKMON".

Does anyone know what I am doing wrong? I can do a where query on other fields but not on date.

Thanks for any input that you can give.

Does the query run on the native system, without executing it through the linked server ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver0205.de
|||

Thanks for responding...

Yes, the query runs on the native system, unix, by typing the select query on the command line exactly the same as the select in single quotes SELECT * FROM mydatabase:mytable_t where mydatabase:mytable_t.d_thisdate = "04/12/2006" .

Thanks Again for any input!