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.
> >