DTS Packages

I am new to SQL but have been tasked with setting up an SQL server. I have
setup a basic database and applied appropriate permissions. The problem is
that one of our analysts says that I am unable to maintian our policy of
giving limited permissions/access to create DTS packages without giving up
full Server control. His reasoning is that if you have the ability to create
DTS packages, you then have access to the command prompt and therefore to
system commands. Is there anyway to give a user the ability to create DTS
packages without giving up Server control, or creating security risk
problems'
Any help will be appreciated.
Thanks in advance.By default, anyone with access to SQL Server can save a DTS package to the
local repository. This does not in any way provide access to a command
prompt.
Perhaps the analyst is referring to creating SQL Server Agent jobs that run
DTS packages using the DTSRUN command-line utility in a CMDEXEC job step.
This is not allowed by default. You need to explicitly allow non-symins
to execute cmdexec job steps and specify a proxy account as the security
context. This would allow non-symins to execute command-line utilities
like DTSRUN yet be limited to the security context of the proxy account you
specify. The proxy account is usually a minimally privileged account. See
the Books Online for more information.
If SQL Agent scheduling is not a requirement, the DTS can be run
interactively (where it runs on the user machine) or scheduled by another
means.
Hope this helps.
Dan Guzman
SQL Server MVP
"r2d2" <John.Infotech@.gmail.com> wrote in message
news:eInzqH3xFHA.448@.TK2MSFTNGP11.phx.gbl...
>I am new to SQL but have been tasked with setting up an SQL server. I have
>setup a basic database and applied appropriate permissions. The problem is
>that one of our analysts says that I am unable to maintian our policy of
>giving limited permissions/access to create DTS packages without giving up
>full Server control. His reasoning is that if you have the ability to
>create DTS packages, you then have access to the command prompt and
>therefore to system commands. Is there anyway to give a user the ability
>to create DTS packages without giving up Server control, or creating
>security risk problems'
> Any help will be appreciated.
> Thanks in advance.
>

DTS package workflow

A basic question I know, but can somebody tell me how to add On Success,
On Failure and On Completion arrows between tasks? I have done quite a
bit of DTS, but never had to use these before.
Thanks,
Mike
*** Sent via Developersdex http://www.examnotes.net ***Click Task1
CTRL+Click Task2
Right Click
WorkFlow
TaDa...
Suggest having a look here www.sqldts.com
HTH. Ryan
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eV2vA%23MLGHA.3064@.TK2MSFTNGP10.phx.gbl...
>A basic question I know, but can somebody tell me how to add On Success,
> On Failure and On Completion arrows between tasks? I have done quite a
> bit of DTS, but never had to use these before.
>
> Thanks,
> Mike
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

DTS Import with no row terminator using VB

Using the DTS wizard in SQL 2000 Enterprise Manager, a DTS Package can be saved as a Visual Basic file. If a row terminates with a {CR}{LF}, the appropriate .ConnectionProperties("Row Delimiter") = vbCrLf is included on or around the third line of the package connection information for the text file.

I have two different files that I cannot import using the saved VB file. One is a .txt file with a carriage return {CR} as a row terminator. This imports fine using DTS but not from VB. The row delimiter is omitted when the package is saved. I have tried adding the connection properties using vbCr, {CR}, and CHR(13) as the row delimiters, but none of these will import the file from VB.

The other file, and the solution for this file can be used for the previous file, is a .dat file exported from SAP (I do not have access to pull the data directly from the Oracle servers into SQL). If the file is opened in a text editor, it contains no row terminator. DTS allows me to specify where the row ends and imports the file, but, once again, this property is omitted when the package is saved as a Visual Basic file. Unable to find a list of possible .ConnectionProperties, I have tried "Row Length", "Row Width", and every other possibility I could think of, but the file will not import. The records are 429 characters in length.

Any suggestions?

Moving to the SQL Server Integration Services forum.|||

Why moved here? It looks to be a DTS cquestion.

I would suggest the original poster headds to newsgroup microsoft.public.sqlserver.dts

-Jamie

DTS Help

Hello Everyone,
Im a bit stumped in doing DTS. I really have basic knowledge on it.
anyway, here's my issue.
Im trying to import data and my source is a text (.txt) file. The
information in the text file was cut and pasted from Excel. This is
what I a normally used of doing.
Anyway, so I ran DTS (source - text file; destination: database) and at
the end of the package run, I get an error saying:
TransformCopy DirectCopyXform conversion error: Conversion Invalid for
datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
destination column EXPIRY (DBTYPE_DBTIMESTAMP)
Now I know that this has something to do with the data type, since my
source is 'varchar' and my destination column holds datatype
'datetime.' true enough, when I tested it again and this time changed
the EXPIRY column datatype to varchar, the DTS went well.
Now the question would be, what do i need to perform the DTS correctly?
so that the conversion would be valid (varchar to datetime). without
having to change the dataype of my destination column.
Or...if first I would need to change the destination column to varchar
datatype first so that I can perform the DTs without errors...what are
the additional steps I need to make in order to convert to datatype
datetime.
Thanks,
Timber
Any insight will be greatly appreciated.Hi Timber
DTS can import directly from Excel, which may remove the problem that you
have.
If you have imported it as varchar then you may want to try and see what the
values are in the column, possibly using the ISDATE function as well. Once
you know the type of values that are not being correctly converted you will
have a better idea or what is necessary to solve it.
Books online has quite a bit of information on DTS and www.sqldts.com is an
excellent site with many code examples.
You may be interested in holding the data in a staging table (which is
effectively what you have done) and then it can be manipulated before being
put into the final destination table. This would mean extra step(s) which may
only be a Execute SQL task to do in INSERT...SELECT from the staging table.
A different approach may be to use an ActiveX transformation to take the
data input into this column, test and manipulate it before inserting into the
destination column. For a tutorial on ActiveX see
http://www.sqldts.com/default.aspx?279
HTH
John
"timber" wrote:
> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>|||> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
One method is to reformat the date string into yyyy-mm-dd format using an
ActiveX column transformation script. For example, if the file dates are
formatted as yyyyddmm:
Function Main()
DTSDestination("MyDateString") = _
Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
Mid(DTSSource("MyDateString"), 5, 2)
Main = DTSTransformStat_OK
End Function
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
The staging table method approach is best if you are uncomfortatable coding
VBScript or if you need to data-intensive processing (e.g. lookups via
joins). In this case, I'd go with the ActiveX script.
Hope this helps.
Dan Guzman
SQL Server MVP
"timber" <iamtimber@.gmail.com> wrote in message
news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...
> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:
> > Now the question would be, what do i need to perform the DTS correctly?
> > so that the conversion would be valid (varchar to datetime). without
> > having to change the dataype of my destination column.
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
> > Or...if first I would need to change the destination column to varchar
> > datatype first so that I can perform the DTs without errors...what are
> > the additional steps I need to make in order to convert to datatype
> > datetime.
> The staging table method approach is best if you are uncomfortatable coding
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...
> > Hello Everyone,
> >
> > Im a bit stumped in doing DTS. I really have basic knowledge on it.
> > anyway, here's my issue.
> >
> > Im trying to import data and my source is a text (.txt) file. The
> > information in the text file was cut and pasted from Excel. This is
> > what I a normally used of doing.
> >
> > Anyway, so I ran DTS (source - text file; destination: database) and at
> > the end of the package run, I get an error saying:
> >
> > TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> > datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> > destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> >
> > Now I know that this has something to do with the data type, since my
> > source is 'varchar' and my destination column holds datatype
> > 'datetime.' true enough, when I tested it again and this time changed
> > the EXPIRY column datatype to varchar, the DTS went well.
> >
> > Now the question would be, what do i need to perform the DTS correctly?
> > so that the conversion would be valid (varchar to datetime). without
> > having to change the dataype of my destination column.
> >
> > Or...if first I would need to change the destination column to varchar
> > datatype first so that I can perform the DTs without errors...what are
> > the additional steps I need to make in order to convert to datatype
> > datetime.
> >
> > Thanks,
> > Timber
> >
> > Any insight will be greatly appreciated.
> >|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:
> > Now the question would be, what do i need to perform the DTS correctly?
> > so that the conversion would be valid (varchar to datetime). without
> > having to change the dataype of my destination column.
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
> > Or...if first I would need to change the destination column to varchar
> > datatype first so that I can perform the DTs without errors...what are
> > the additional steps I need to make in order to convert to datatype
> > datetime.
> The staging table method approach is best if you are uncomfortatable coding
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...
> > Hello Everyone,
> >
> > Im a bit stumped in doing DTS. I really have basic knowledge on it.
> > anyway, here's my issue.
> >
> > Im trying to import data and my source is a text (.txt) file. The
> > information in the text file was cut and pasted from Excel. This is
> > what I a normally used of doing.
> >
> > Anyway, so I ran DTS (source - text file; destination: database) and at
> > the end of the package run, I get an error saying:
> >
> > TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> > datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> > destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> >
> > Now I know that this has something to do with the data type, since my
> > source is 'varchar' and my destination column holds datatype
> > 'datetime.' true enough, when I tested it again and this time changed
> > the EXPIRY column datatype to varchar, the DTS went well.
> >
> > Now the question would be, what do i need to perform the DTS correctly?
> > so that the conversion would be valid (varchar to datetime). without
> > having to change the dataype of my destination column.
> >
> > Or...if first I would need to change the destination column to varchar
> > datatype first so that I can perform the DTs without errors...what are
> > the additional steps I need to make in order to convert to datatype
> > datetime.
> >
> > Thanks,
> > Timber
> >
> > Any insight will be greatly appreciated.
> >

DTS Help

Hello Everyone,
Im a bit stumped in doing DTS. I really have basic knowledge on it.
anyway, here's my issue.
Im trying to import data and my source is a text (.txt) file. The
information in the text file was cut and pasted from Excel. This is
what I a normally used of doing.
Anyway, so I ran DTS (source - text file; destination: database) and at
the end of the package run, I get an error saying:
TransformCopy DirectCopyXform conversion error: Conversion Invalid for
datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
destination column EXPIRY (DBTYPE_DBTIMESTAMP)
Now I know that this has something to do with the data type, since my
source is 'varchar' and my destination column holds datatype
'datetime.' true enough, when I tested it again and this time changed
the EXPIRY column datatype to varchar, the DTS went well.
Now the question would be, what do i need to perform the DTS correctly?
so that the conversion would be valid (varchar to datetime). without
having to change the dataype of my destination column.
Or...if first I would need to change the destination column to varchar
datatype first so that I can perform the DTs without errors...what are
the additional steps I need to make in order to convert to datatype
datetime.
Thanks,
Timber
Any insight will be greatly appreciated.
Hi Timber
DTS can import directly from Excel, which may remove the problem that you
have.
If you have imported it as varchar then you may want to try and see what the
values are in the column, possibly using the ISDATE function as well. Once
you know the type of values that are not being correctly converted you will
have a better idea or what is necessary to solve it.
Books online has quite a bit of information on DTS and www.sqldts.com is an
excellent site with many code examples.
You may be interested in holding the data in a staging table (which is
effectively what you have done) and then it can be manipulated before being
put into the final destination table. This would mean extra step(s) which may
only be a Execute SQL task to do in INSERT...SELECT from the staging table.
A different approach may be to use an ActiveX transformation to take the
data input into this column, test and manipulate it before inserting into the
destination column. For a tutorial on ActiveX see
http://www.sqldts.com/default.aspx?279
HTH
John
"timber" wrote:

> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>
|||> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
One method is to reformat the date string into yyyy-mm-dd format using an
ActiveX column transformation script. For example, if the file dates are
formatted as yyyyddmm:
Function Main()
DTSDestination("MyDateString") = _
Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
Mid(DTSSource("MyDateString"), 5, 2)
Main = DTSTransformStat_OK
End Function

> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
The staging table method approach is best if you are uncomfortatable coding
VBScript or if you need to data-intensive processing (e.g. lookups via
joins). In this case, I'd go with the ActiveX script.
Hope this helps.
Dan Guzman
SQL Server MVP
"timber" <iamtimber@.gmail.com> wrote in message
news:1159599545.059085.105030@.e3g2000cwe.googlegro ups.com...
> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>
|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:[vbcol=seagreen]
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
>
> The staging table method approach is best if you are uncomfortatable coding
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegro ups.com...
|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:[vbcol=seagreen]
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
>
> The staging table method approach is best if you are uncomfortatable coding
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegro ups.com...

DTS Help

Hello Everyone,
Im a bit stumped in doing DTS. I really have basic knowledge on it.
anyway, here's my issue.
Im trying to import data and my source is a text (.txt) file. The
information in the text file was cut and pasted from Excel. This is
what I a normally used of doing.
Anyway, so I ran DTS (source - text file; destination: database) and at
the end of the package run, I get an error saying:
TransformCopy DirectCopyXform conversion error: Conversion Invalid for
datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
destination column EXPIRY (DBTYPE_DBTIMESTAMP)
Now I know that this has something to do with the data type, since my
source is 'varchar' and my destination column holds datatype
'datetime.' true enough, when I tested it again and this time changed
the EXPIRY column datatype to varchar, the DTS went well.
Now the question would be, what do i need to perform the DTS correctly?
so that the conversion would be valid (varchar to datetime). without
having to change the dataype of my destination column.
Or...if first I would need to change the destination column to varchar
datatype first so that I can perform the DTs without errors...what are
the additional steps I need to make in order to convert to datatype
datetime.
Thanks,
Timber
Any insight will be greatly appreciated.Hi Timber
DTS can import directly from Excel, which may remove the problem that you
have.
If you have imported it as varchar then you may want to try and see what the
values are in the column, possibly using the ISDATE function as well. Once
you know the type of values that are not being correctly converted you will
have a better idea or what is necessary to solve it.
Books online has quite a bit of information on DTS and www.sqldts.com is an
excellent site with many code examples.
You may be interested in holding the data in a staging table (which is
effectively what you have done) and then it can be manipulated before being
put into the final destination table. This would mean extra step(s) which ma
y
only be a Execute SQL task to do in INSERT...SELECT from the staging table.
A different approach may be to use an ActiveX transformation to take the
data input into this column, test and manipulate it before inserting into th
e
destination column. For a tutorial on ActiveX see
http://www.sqldts.com/default.aspx?279
HTH
John
"timber" wrote:

> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>|||> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
One method is to reformat the date string into yyyy-mm-dd format using an
ActiveX column transformation script. For example, if the file dates are
formatted as yyyyddmm:
Function Main()
DTSDestination("MyDateString") = _
Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
Mid(DTSSource("MyDateString"), 5, 2)
Main = DTSTransformStat_OK
End Function

> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
The staging table method approach is best if you are uncomfortatable coding
VBScript or if you need to data-intensive processing (e.g. lookups via
joins). In this case, I'd go with the ActiveX script.
Hope this helps.
Dan Guzman
SQL Server MVP
"timber" <iamtimber@.gmail.com> wrote in message
news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...
> Hello Everyone,
> Im a bit stumped in doing DTS. I really have basic knowledge on it.
> anyway, here's my issue.
> Im trying to import data and my source is a text (.txt) file. The
> information in the text file was cut and pasted from Excel. This is
> what I a normally used of doing.
> Anyway, so I ran DTS (source - text file; destination: database) and at
> the end of the package run, I get an error saying:
> TransformCopy DirectCopyXform conversion error: Conversion Invalid for
> datatypes on column pair 5 (source column 'Col005' (DBTYPE_STR)),
> destination column EXPIRY (DBTYPE_DBTIMESTAMP)
> Now I know that this has something to do with the data type, since my
> source is 'varchar' and my destination column holds datatype
> 'datetime.' true enough, when I tested it again and this time changed
> the EXPIRY column datatype to varchar, the DTS went well.
> Now the question would be, what do i need to perform the DTS correctly?
> so that the conversion would be valid (varchar to datetime). without
> having to change the dataype of my destination column.
> Or...if first I would need to change the destination column to varchar
> datatype first so that I can perform the DTs without errors...what are
> the additional steps I need to make in order to convert to datatype
> datetime.
> Thanks,
> Timber
> Any insight will be greatly appreciated.
>|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:[vbcol=seagreen]
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
>
> The staging table method approach is best if you are uncomfortatable codin
g
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...|||Guys,
Thank you for your help. I'll try it, then see how it goes. Appreciate
your input. This forum is awesome, especially for a noob like me.
Timber
Dan Guzman wrote:[vbcol=seagreen]
> One method is to reformat the date string into yyyy-mm-dd format using an
> ActiveX column transformation script. For example, if the file dates are
> formatted as yyyyddmm:
> Function Main()
> DTSDestination("MyDateString") = _
> Mid(DTSSource("MyDateString"), 1, 4) + "/" + _
> Mid(DTSSource("MyDateString"), 7, 2) + "/" + _
> Mid(DTSSource("MyDateString"), 5, 2)
> Main = DTSTransformStat_OK
> End Function
>
> The staging table method approach is best if you are uncomfortatable codin
g
> VBScript or if you need to data-intensive processing (e.g. lookups via
> joins). In this case, I'd go with the ActiveX script.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "timber" <iamtimber@.gmail.com> wrote in message
> news:1159599545.059085.105030@.e3g2000cwe.googlegroups.com...