Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).
have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>
|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it
>

Bulk insert data with decimal point

I am having a problem with bulk insert when the data in the input file
contains a decimal point. I have given a shortened example below, in reality
the table has about 500 columns and about 5,000,000 records were inserted
sucessfully before records with decimal points were encountered. I have
verified in the regional settings that the decimal point is the period.
If necessary to solve the problem, I can easily drop the table, create it
with different data types, and start the insert again. Getting the input
data changed would be considerably more difficult.
CREATE TABLE [MY_TABLE] (
[ID] decimal (9,0) NOT NULL,
[CITY] varchar (50) NULL,
[BIRTH_DATE] datetime NULL,
[TOTAL_INCOME] decimal (10,2) NULL,
[NET_INCOME] decimal (10,0) NULL,
[NET_TAX_PAID] decimal (10,2) NULL)
BULK INSERT MYDB.me.MY_TABLE
FROM 'd:\batch\data1999.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
123456787|NEW YORK|1973/05/06|900|875|278|
123456788|LONDON|1946/08/01|563.75|550|125.27|
123456789|MADRID|1980/03/10|1067|987|338.27
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 4
(TOTAL_INCOME).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 3, column 6
(NET_TAX_PAID).have you try to create an FMT file?
this file will help you to define each input column.
Because I think you have a problem to identify the . (dot) has the decimal
separator.
have you try to use DTS to do this job?
DTS will help you to define the links and can create the FMT file for you.
"Carolyn" <postreply@.dontemail.com> wrote in message
news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
>I am having a problem with bulk insert when the data in the input file
> contains a decimal point. I have given a shortened example below, in
> reality
> the table has about 500 columns and about 5,000,000 records were inserted
> sucessfully before records with decimal points were encountered. I have
> verified in the regional settings that the decimal point is the period.
> If necessary to solve the problem, I can easily drop the table, create it
> with different data types, and start the insert again. Getting the input
> data changed would be considerably more difficult.
> CREATE TABLE [MY_TABLE] (
> [ID] decimal (9,0) NOT NULL,
> [CITY] varchar (50) NULL,
> [BIRTH_DATE] datetime NULL,
> [TOTAL_INCOME] decimal (10,2) NULL,
> [NET_INCOME] decimal (10,0) NULL,
> [NET_TAX_PAID] decimal (10,2) NULL)
> BULK INSERT MYDB.me.MY_TABLE
> FROM 'd:\batch\data1999.txt'
> WITH
> (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '|\n'
> )
> 123456787|NEW YORK|1973/05/06|900|875|278|
> 123456788|LONDON|1946/08/01|563.75|550|125.27|
> 123456789|MADRID|1980/03/10|1067|987|338.27
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 2, column 4
> (TOTAL_INCOME).
> Server: Msg 4864, Level 16, State 1, Line 1
> Bulk insert data conversion error (type mismatch) for row 3, column 6
> (NET_TAX_PAID).
>|||Thank you Jj, I will try using DTS to create a FMT file.
"Jj" <willgart@.BBBhotmailAAA.com> wrote in message
news:OqyzCJleFHA.132@.TK2MSFTNGP10.phx.gbl...
> have you try to create an FMT file?
> this file will help you to define each input column.
> Because I think you have a problem to identify the . (dot) has the decimal
> separator.
> have you try to use DTS to do this job?
> DTS will help you to define the links and can create the FMT file for you.
>
> "Carolyn" <postreply@.dontemail.com> wrote in message
> news:%qAte.49744$Ph4.1337809@.ursa-nb00s0.nbnet.nb.ca...
inserted[vbcol=seagreen]
it[vbcol=seagreen]
>

Bulk insert a .NET dataset?

Hi,
Does it possible to insert an XML document generated from a Datset to SQL
Server using the SQLXML feature?
and this, without transforming the input document.
In my case this is the situation:
I've an oracle database, I'll develop a small tool that extract some data
fron this database using a VB.NET application.
From this query I'll generate a Dataset, then I'll save it as XML file.
This XML file is sent by FTP and here I want to upload these data to an SQL
Server databases.
My question is: can I upload these data without transforming the XML file?
Before I start my tests, I want to know your experience.
Thanks.
Jerome.
If you have a mapping for it, you can use bulkload to do this.
Alternativly, we also have the SqlXmlAdapter which can be used to persist
changes to a Dataset back to Sql Server.
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:OEWujqmPEHA.2236@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Does it possible to insert an XML document generated from a Datset to SQL
> Server using the SQLXML feature?
> and this, without transforming the input document.
> In my case this is the situation:
> I've an oracle database, I'll develop a small tool that extract some data
> fron this database using a VB.NET application.
> From this query I'll generate a Dataset, then I'll save it as XML file.
> This XML file is sent by FTP and here I want to upload these data to an
> SQL
> Server databases.
> My question is: can I upload these data without transforming the XML file?
> Before I start my tests, I want to know your experience.
> Thanks.
> Jerome.
>

Building text files

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpdI'd go with the sproc and bcp out

Just change the IN to OUT

SET @.cmd = 'bcp ' + @.db_name + '..ETRS_ASI_FED_TEMP in '
+ @.FilePathAndName + ' -t"\t" -c -S' + @.@.servername + ' -Uscrub -Pscrub'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''''

Select @.Command_String

Exec(@.Command_String)|||Given this problem, I would probably try a VB or PERL script to extract the data. I know how to call bcp from PERL, but VB is still a bit new to me. Fortunately, if the data set you are exporting is small (few thousand rows), you could get away with just using FileObject writes. Biggest problem I have had with bcp is remembering to check the error file for any problems. Again, easy for me in PERL, but VB...|||Thanks! bcp is what I was looking for. Glad I can avoid DTS all together.

-bpd

Building Custom Component - marking all inputs

I have been building a custom component with the default GUI. I want to have all the input fields selected to be passed through to outputs without having to explicitly check each one in the GUI. Is there some method or property to set on the input to do this?

You have to map ExternalMetadataColumnID from source to destination

|||I'm not sure this is specific to SSIS. That is, you might want to look at the appropriate .Net forum for specifics on how to code the GUI for your project.|||

I would normally do this in the UI itself, but that would be a custom UI, not the standard UI you get for free, as you cannot extend that one at all.

You could do this in ReinitializeMetaData, such that if no columns are found, and you have forced RMD to build the input/output columns for the first time, when adding you also select the columns as you create them.

How you select the column, the equivalent of selecting a column in the UI, will depend on what type of component you are writing, but normally revolves around setting the usage type, or mapping input/output columns to external metadata columns.

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/

Build a xml doc from parameters passed to sp

I have a sp with some input parameters and i would like to build an xml document with the elements name of the parameters with the proper value.

ie:

declare @.par2 datetime;

set @.par2=getdate();

exec spTest 'test',@.par2

create procedure spTest(@.par1 nvarchar(50),@.Par2 datetime)

as

...do some job

i would like to obtain:

<spTest><par1>test</par1><Par2>10/20/2006 10:00:00</Par2><spTest>

or, btw the same result i could get querying with the forxml clause.

I need to create this Parameter/ParametersValues to xml in many sp that have different parameters.

Thank you very much

Maybe something like one of these?

Code Snippet

declare @.par1 varchar (50) set @.par1 = 'test'
declare @.par2 datetime set @.par2 = getdate()

select '<spTest><par1>' +
case when @.par1 is null then '' else @.par1 end +
'</par1><par2>' +
case when @.par1 is null then ''
else convert(varchar(23), @.par2, 121)
end + '</par2></spTest>'
as xmlResult

/*
xmlResult
-
<spTest><par1>test</par1><par2>2007-06-02 10:08:52.327</par2></spTest>
*/

select parm as [data()]
from ( select case when @.par1 is null then ''
else @.par1
end as Parm
union all
select case when @.par2 is null then ''
else convert(varchar(23), @.par2, 121)
end
) a
for xml path ('parm'), root('spTest')

/*
XML_F52E2B61-18A1-11d1-B105-00805F49916B
-
<spTest><parm>test</parm><parm>2007-06-02 10:16:56.187</parm></spTest>
*/