MS SQL Server DTS - bcp native format file from DTS

Author

bcp native format file from DTS

Soma

2005-09-28, 9:23 am

Hi All,

We have a task of generating flat files (.dat) with bcp native fromat by
using DTS.

we have OLTP tables, by applying business rules we have to create flat
files, which are suppose to be in bcp native format.

Can any one suggest how can we do this. ?

Many thanks.

Soma

Allan Mitchell

2005-09-28, 11:24 am

So if you know you can do it using BCP then can you not loop through the
tables in the source and build a BCP string. Once you have that you can
use the ExecuteProcess task to execute the BCP string.

Examples of loopoinig are here

Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

And here is an example of manipulating the ExecuteProcess task.

How to manipulate the Execute Process task.
(http://www.sqldts.com/default.aspx?251)

Allan

"Soma" <Soma@discussions.microsoft.com> wrote in message
news:E0E9FBE6-FAC2-4E02-8407- C93812B83F14@microso
ft.com:

> Hi All,
>
> We have a task of generating flat files (.dat) with bcp native fromat by
> using DTS.
>
> we have OLTP tables, by applying business rules we have to create flat
> files, which are suppose to be in bcp native format.
>
>
> Can any one suggest how can we do this. ?
>
> Many thanks.
>
> Soma

Soma

2005-09-29, 7:24 am

Hi Allan Mitchell,

Thanks for your suggestion. As per u r suggestion i done like that only. But
i am not able to load that data using Bulk Insert (native).

I created views, in the views all my business logic (transformations) are
stored. I called that views from bcp to load data in flat files as native
format.

bcp Northwind..v1 out \\iss1w289\msn\test.dat -n -T -Siss1w289

After that while loading them in sql tables using Bulk Insert (native) i am
getting unexpected errors like
Bulk Insert T1 from '\\iss1w289\msn\test
.dat' with (datafiletype='nativ
e')

error:
"Bulk Insert fails. Column is too long in the data file for row 2, column 1.
Make sure the field terminator and row terminator are specified correctly. "

Can you suggest how can we fix this...

Regards,
Soma

"Allan Mitchell" wrote:

> So if you know you can do it using BCP then can you not loop through the
> tables in the source and build a BCP string. Once you have that you can
> use the ExecuteProcess task to execute the BCP string.
>
> Examples of loopoinig are here
>
> Looping, Importing and Archiving
> (http://www.sqldts.com/default.aspx?246)
>
> How to loop through a global variable Rowset
> (http://www.sqldts.com/default.aspx?298)
>
> And here is an example of manipulating the ExecuteProcess task.
>
> How to manipulate the Execute Process task.
> (http://www.sqldts.com/default.aspx?251)
>
> Allan
>
>
>
>
> "Soma" <Soma@discussions.microsoft.com> wrote in message
> news:E0E9FBE6-FAC2-4E02-8407- C93812B83F14@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-29, 7:24 am

There are some dicussions of this error and people fixing the issue here

http://www.google.com/search?source...+fil
e%22

"Soma" <Soma@discussions.microsoft.com> wrote in message
news:FF253A5D-8894-410F-9EAE- A8208E28B516@microso
ft.com:
[color=darkred]
> Hi Allan Mitchell,
>
> Thanks for your suggestion. As per u r suggestion i done like that only. But
> i am not able to load that data using Bulk Insert (native).
>
> I created views, in the views all my business logic (transformations) are
> stored. I called that views from bcp to load data in flat files as native
> format.
>
> bcp Northwind..v1 out \\iss1w289\msn\test.dat -n -T -Siss1w289
>
> After that while loading them in sql tables using Bulk Insert (native) i am
> getting unexpected errors like
> Bulk Insert T1 from '\\iss1w289\msn\test
.dat' with (datafiletype='nativ
e')
>
> error:
> "Bulk Insert fails. Column is too long in the data file for row 2, column 1.
> Make sure the field terminator and row terminator are specified correctly. "
>
> Can you suggest how can we fix this...
>
> Regards,
> Soma
>
>
> "Allan Mitchell" wrote:
>

Soma

2005-09-30, 9:23 am

Hi Allan Mitchell,

Thanks for the link. As per the suggestions of others i make sure the
datatype of both are same. But still i am getting problems, To fix them as
suggested by some one i used CAST to convert the datatype. It worked
perfectly.

Now the problem with Performance, previously with out applying CAST it taken
to me to load 8 million records to flat file just 15 min, now it is taking
more than 1 hr.

SELECT ua.Siid as SrcSubscriptionId,
CAST (ua.UserPUID AS BIGINT) as SrcPUID,
CAST('Amex' AS NVARCHAR(25)) as SrcAwardSponsor,
CAST(ua.AmexRewards as smallint) as SrcAwardAmount,
CAST(ua.AmexBalance as smallint) as SrcAwardBalance,
CAST(NULL AS SMALLDATETIME) as SrcAwardStartDate,
CAST(NULL AS SMALLDATETIME) as SrcAwardEndDate,
CAST(46 AS INT) as SrcSystemCode
FROM UserAccount as ua
WHERE (AmexBalance IS NOT NULL OR
AmexRewards IS NOT NULL)

This the script i used.

Any thing we can do to improve performance.

Regards
Soma

"Allan Mitchell" wrote:

> There are some dicussions of this error and people fixing the issue here
>
> http://www.google.com/search?source...+fil
e%22
>
>
>
>
>
> "Soma" <Soma@discussions.microsoft.com> wrote in message
> news:FF253A5D-8894-410F-9EAE- A8208E28B516@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-30, 9:23 am

I am sure you have an index on AmexBalance so short of that and if it is
the only way you can coerce this data then unfortunately not.

--

----------------------------------------
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

"Soma" <Soma@discussions.microsoft.com> wrote in message
news:B02570DB-7FDA-4D58-ABDE- 235809588D39@microso
ft.com:
[color=darkred]
> Hi Allan Mitchell,
>
> Thanks for the link. As per the suggestions of others i make sure the
> datatype of both are same. But still i am getting problems, To fix them as
> suggested by some one i used CAST to convert the datatype. It worked
> perfectly.
>
> Now the problem with Performance, previously with out applying CAST it taken
> to me to load 8 million records to flat file just 15 min, now it is taking
> more than 1 hr.
>
> SELECT ua.Siid as SrcSubscriptionId,
> CAST (ua.UserPUID AS BIGINT) as SrcPUID,
> CAST('Amex' AS NVARCHAR(25)) as SrcAwardSponsor,
> CAST(ua.AmexRewards as smallint) as SrcAwardAmount,
> CAST(ua.AmexBalance as smallint) as SrcAwardBalance,
> CAST(NULL AS SMALLDATETIME) as SrcAwardStartDate,
> CAST(NULL AS SMALLDATETIME) as SrcAwardEndDate,
> CAST(46 AS INT) as SrcSystemCode
> FROM UserAccount as ua
> WHERE (AmexBalance IS NOT NULL OR
> AmexRewards IS NOT NULL)
>
> This the script i used.
>
> Any thing we can do to improve performance.
>
> Regards
> Soma
>
>
>
> "Allan Mitchell" wrote:
>

Sponsored Links