Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
George
george collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
David Gugick
Imceda Software
www.imceda.com
|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
Georgegeorge collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
--
David Gugick
Imceda Software
www.imceda.com|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
>> Hi again everyone,
>> I build a temp table with a data type of int on one column. I then
>> use BCP to export this table to a TXT file. I use the -c option and
>> also the -t"" (no column delimiter).
>> The problem I am having is that I am limited to 5 characters for this
>> column by the company the data is being sent too (archaic I know, I
>> think they are reading it with Cobol) and the int exports to about 9
>> characters.
>> Is there a way to change an int to a char on the fly with INSERT?
>> Something like CAST?
>> Beating my brains out, thanks for any suggestions.
>> George
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data Transformation Services (DTS)(Bulk Insert)

Hi All,

I'm using DTS package, a tool to transfer data from a txt file to database(Bulk Insert).

The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view.It seems that the Bulk Insert task supports only OLE DB connections for the destination database. But I want to use sql server authentication as OLEDB connection requires windows authentication.

So can the bulk insert be done using SQLServer authentication ? if yes then please help me.

I have given the code snippet below.

Code Sample:

Dim oPackage As New DTS.Package2()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask
Try
oConnection = oPackage.Connections.New("SQLOLEDB")
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSBulkInsertTask")
oCustomTask = oTask.CustomTask
With oConnection
oConnection.Catalog = "pubs"
oConnection.DataSource = "(local)"
oConnection.ID = 1
oConnection.UseTrustedConnection = True
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"
End With
oPackage.Connections.Add(oConnection)
oConnection = Nothing
With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = "c:\dts\authors.txt"
.ConnectionID = 1
.DestinationTableName = "pubs..authors"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With
oStep.TaskName = oCustomTask.Name
With oPackage
.Steps.Add(oStep)
.Tasks.Add(oTask)
.FailOnError = True
End With
oPackage.Execute()
Catch ex As Exception
MsgBox("Error: " & CStr(Err.Number) & vbCrLf_
& Err.Description, vbExclamation, oPackage.Name)
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try

This is really a SSIS forum not DTS. There is a newsgroup for DTS, with a web/forum style interface.

Corrected Snippet

oConnection.UseTrustedConnection = False ' Must be false to use SQL Security
oConnection.UserID = "Tony Patton"
oConnection.Password = "Builder"