Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts

Thursday, March 29, 2012

DTS screws up dd/mm/yyyy dates

Hi

Unlike the colonies, we still use dd/mm/yyyy format in the UK. A bit like VB string concatentation, DTS likes to treat dd/mm/yyyy dates as mm/dd/yyyy if dd <=12.

I've run into this problem twice and been stumped both times. The first time I was setting up a new DTS and switched to BULK INSERT instead. This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems but...

is there a third way? Can you tell SQL Server to expect to see the incoming dates in DD/MM/YYYY format and respond accordingly? I expected the answer to be in the DateTimeString transformation but I cannot get it to work - errors (haven't got the precise error to hand but can supply).

Not urgent. Not even important. Just curious :)

TIA chapsIf you are purely in SQL Server, you can try setting the dateformat for the query:

set dateformat dmy
select convert(datetime, '20/4/2006'), getdate()

If this is in the VB code, or whatever equivalent you have, then I am not entirely certain what you can do beyond checking the locale settings of the machine it is running on.|||Hi

It's actually during the DTS transformation. This doesn't apply to that does it?

You've pointed me towards something though. I correct for this in VB when working in that. Presumably I can write a script and use that as an ActiveX script for the transformation?|||Yep. dateformat won't have any effect in a data pump task. I found an interesting bit of code over here:
http://www.a1vbcode.com/snippet-3656.asp
towards the bottom of the example that may help you along with reworking the transform task. Modifying the transform task is something that I have successfully avoided since about 1997 or so.|||The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).|||I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).

-PatP|||The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).I'm just curious, but where in the standard did you find this particular date format referenced?

-PatP|||This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems
I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).
:D

I pretty well always use ISO 8601. It just so happens that it was a bit of a pest to do so on this occasion and the name "Data Transformation Services" implies to me that I might be able to inform the process that incoming dates are dd/mm/yyyy.
It is no biggie and if the response is "you are transferring data between two data sources - use the universally recognised format you moron" then I will quite merrily accept the advice.

MCrowley - apologies for not getting back. I have to admit that it is because I never quite fathomed out how it related to the problem :o|||No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).|||No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).Lol - yup - converting isn't a problem (just a minor hassle) and the locale of the DTS client is known and, in my case, unvarying from server to server so it is probably a bit overkill as far as my situation is concerned.

I will have a word with myself for sending me this mess :p To be fair - the outputting program merely picked up the local settings and patriotically outputted the dates in good old British DD/MM/YYYY. I have since told it to output in the rather more sterile YYYY-MM-DD.

Sunday, March 25, 2012

DTS Problem

I'm having a bit of a problem getting a date parsed from a text file, if anyone has some time please take a look at this DTS code and let me know if you have any ideas. Thanks in advance!

'************************************************* *********************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************* ***********************
Function Main()
DTSDestination("TableName") = DTSSource("Col001")
DTSDestination("Code") = DTSSource("Col002")
DTSDestination("Status") = DTSSource("Col003")
DTSDestination("ISN") = DTSSource("Col004")
DTSDestination("Description") = DTSSource("Col005")
DTSDestination("LongDescription") = DTSSource("Col006")
DTSDestination("OwnerSystem") = DTSSource("Col007")
DTSDestination("CodeFieldAttribute") = DTSSource("Col008")
DTSDestination("CodeFieldLength") = DTSSource("Col009")
DTSDestination("CodeEditRules") = DTSSource("Col010")
DTSDestination("EffectiveDateMaint") = DTSSource("Col011")
DTSDestination("StartDate") = getdate(DTSSource("Col012"))
'DTSDestination("StartDateInv") = getdate(DTSSource("Col013"))
DTSDestination("EndDate") = getdate(DTSSource("Col014"))
' DTSDestination("EndDateInv") = getdate(DTSSource("Col015"))
DTSDestination("Hold") = DTSSource("Col016")
Main = DTSTransformStat_OK
End Function

Function getDate(aDate)
Dim year, month, day

If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If

year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)

getDate = DateSerial(year, month, day)

End FunctionLooking at your code leads to a few questions:

What is the error that you are getting? What is the value within DTSSource("Col012") and DTSSource("Col014")? Are the date fields all coming back NULL?

If so, then is the original column data in the format 20060725?

Also, the DateSerial function takes in numeric parameters, not strings, so you might need to convert the year, month, and day.

Finally, the section:

If Trim(aDate) = "" Or IsNumeric(aDate) Then
getDate = Null
Exit Function
End If

would work better as

If IsNumeric(aDate) Then
getDate = Null
Exit Function
Elseif Trim(aDate) = "" Then
getDate = Null
Exit Function
End If

Since Trim on a Numeric will cause an error, so it is good to test the trim after confirming it is not numeric.|||a simple rearrangemet of text date data will do the trick. try the code below (this assumes your text data is in the form of yyyymmdd otherwise you need to change the code a bit)

DTSDestination("StartDate") = left(DTSSource("Col012"),4) & "-" & mid(DTSSource("Col012"),5,2) & "-" & right(DTSSource("Col012"),2)|||Thanks guys, both your posts were very helpful. The problem actually was in the text file that I was trying to import, someone had entered dates in a yy/mm/dd format rather then yyyy/mm/dd once that was fixed the function performed as it should. Thanks alot!|||A couple of questions:

1.
With the supplied code
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 5, 2)
day = Mid(aDate, 7, 2)
"
wouldn't the slashes get in the way? I would guess that the code would have to change to:
"
year = Mid(aDate, 1, 4)
month = Mid(aDate, 6, 2)
day = Mid(aDate, 9, 2)
"
to get past whatever /,-, or . that was delimiting the date?

2. How does one Charboil something? I mean, by being in water wouldn't it get overcooked but not charred? :)

Sunday, March 11, 2012

DTS Package for AS/400 to SQL Server

Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

ThanksYes, we had this exact same issue. There are several steps you will need to take to resolve it:

1. Check the version of IBM Client Access that you are running on your SQL server. We installed V5R1 with the latest service pack. The latest service pack is critical because the original version of V5R1 has a bug that limits the size of the connection library to 9 characters (it's supposed to be 10).

2. Make sure that your SQL Agent is running as a user account (preferably a Domain User). Make sure it is NOT running as the local system account. Verify this by right-clicking on the SQL Server Agent icon under the management folder of your SQL Server (in EM) and then clicking on Properties. The Service Startup Account information should be filled in.

3. Set up an account on the AS/400 with the EXACT same name as the service account for SQL Agent. The password must also be EXACTLY the same (nb, at least on our AS/400, passwords are limited to 10 characters; the password for your SQL Agent account must conform to the same limitation).

4. When you set up the ODBC DSN connection to the AS/400 on your SQL Server, be sure to specify to use Windows Authentication (no prompting).

Doing all of this will ensure:

1. That your server will connect to the AS/400 without the password prompt
2. That rebooting your server will not impact the state of the connection.

Bear in mind that if you change the password for the SQL Agent account, then you will need to change it on the AS/400 as well.

I hope that this helps.

Hugh Scott

Originally posted by lossman
Set up a DTS to transfer data from an AS/400 to SQL Server
7 using a file DSN (Client Access ODBC Driver (32 bit)).
It works great except that every time I run it, it asks me
to log into the AS/400, even though I specified the ID and
password in the DSN and DTS.

Also, tried to set the password for the DTS connection using Disconnected Edit to no avail. The password always blanks out. Seems something is blocking the saving of the password.

I need to run this unattended, daily without a manual log
in.

Any ideas?

Thanks

DTS package failing with Object required WScript

I have the following error when I try to execute a DTS package.
All the package is doing, is a bit of WScript to map a drive (need it for other packages), so:

[code]

Function Main()
Dim WshNetwork
Set WshNetwork = WScript.CreateObject("WScript.Network")
WshNetwork.RemoveNetworkDrive "S:"
WshNetwork.MapNetworkDrive "S:", "\\myserver\folder1\folder2\folder3\folder4"
Main = DTSTaskExecResult_Success
End Function

[/code]

If I copy it out to a .vbs file and execute it logged in as the SQL Agent profile, it works. Execute the package under a job, or just execute it, it fails with the above error.
Any ideas?

This is a SSIS forum, not DTS! Try microsoft.public.sqlserver.dts, you're more likely to get help there.

-Jamie

|||WScript object is specific to wscript.exe host application (that runs .VBS files), it is not part of VBScript language, so you can't use it in DTS scripts.
I think just CreateObject (without "wscript.") should work as well, but I don't know VBScript. You may try VBScript or DTS newsgroups to get better answer.|||

Sussed it. If I use:

Set WshNetwork = CreateObject("WScript.Network")

It works fine

Friday, February 24, 2012

DTS is rubbish

I'm gonna say something slightly controversial just to get a bit of debate
going, but I think DTS is just a drag and drop wizard for programmers who
don't know what they're doing. As with any wizard, it can take you so far,
but if you need anything complicated it's an ActiveX script. You do not use
VBScript (or a n other scripting language) to work with large volumes of
data.
Discuss? ; )
I'm personally a big fan of bcp and BULK INSERT; there is nothing faster for
shifting data.
I guess DTS might be ok for small import jobs, and the workflow could be
useful ...
Let me know what you think.
DamienDamien <Damien@.discussions.microsoft.com> wrote:
> I'm gonna say something slightly controversial just to get a bit of
> debate going, but I think DTS is just a drag and drop wizard for
> programmers who don't know what they're doing. As with any wizard,
> it can take you so far, but if you need anything complicated it's an
> ActiveX script. You do not use VBScript (or a n other scripting
> language) to work with large volumes of data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing
> faster for shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could
> be useful ...
> Let me know what you think.
Exactly the opposite: DTS is fast and flexible. IMHO it's good that you
don't have to write a program or a bcp control file just to get data from
one db to another. Plus it's cross database - as long as both DB support
ODBC you can even use DTS with non MS databases.
robert|||"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:77DE764E-C242-4448-B1DB-33FBD0E6EC58@.microsoft.com...
> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so
> far,
> but if you need anything complicated it's an ActiveX script. You do not
> use
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster
> for
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien
BULK INSERT is great if you just need to import a single delimited file per
table but isn't a complete integration solution, which is what DTS aspires
to be.
To handle row-level validation and transformations involving multiple tables
or non-relational data sources you need somewhere to put the transformation
and validation logic. Broadly, the two common approaches are: 1) ELT -
Extract and Load to staging tables. Transform and validate in the database.
2) ETL - Extract, Transform and validation process first, then Load to the
database. DTS is usually positioned somewhere between these two approaches.
In my experience, when it comes to true ETL capability DTS falls short on
features and performance compared to rival integration software products.
That's a signifcant issue because the ETL appraoch has important benefits
for many enterprises. That's why DTS probably hasn't taken much market share
from the likes of Informatica and Ab Initio.
SQL Server 2005 Intergration Services is a big improvement on DTS. It
remains to be seen what market that will find now Microsoft is pushing
harder to promote SQL Server's potential as a complete BI tool suite.
David Portas
SQL Server MVP
--|||Damien wrote:
> I'm gonna say something slightly controversial just to get a bit of
> debate going, but I think DTS is just a drag and drop wizard for
> programmers who don't know what they're doing. As with any wizard,
> it can take you so far, but if you need anything complicated it's an
> ActiveX script. You do not use VBScript (or a n other scripting
> language) to work with large volumes of data.
I've only used it for small or one-off migration projects, but it's been
really nice to not have to really think about the programming and to just
drag and drop and set some parameters and go.
One of the most complicated situations I've used it in was for testing some
VB apps and stored procedures for a large data integration project. We were
getting data in Access tables from an outside source, importing them to
staging tables, manipulating them with stored procedures and populating
other tables. There were several steps and combinations of VB apps and
stored procedures because there were several types of data coming in and
going out and they had to be in specific order and with some assumptions of
values set by a previous step.
I was able to create several DTS packages to clear out and re-load the data
and initialize values to simulate a previous step in order to trouble-shoot
various stages of the process. All this without taking time to write an
extra application. I knew what I was doing obviously (I wrote the VB apps
and stored procedures), but it was really quick to use DTS in this case
because I could just point and click and run the process, then get back to
work on the real application for the client. Another benefit of DTS in this
case was that I was working remotely through a secure VNC tunnel and
recompiling the VB apps on my machine, FTPing them over and moving them to
the right directory was a pain (security was very rigid). With DTS, the
packages were stored right there in the server, so I didn't have to looking
anywhere else for them or upload them after changes were made.
There are multiple ways to solve different problems, but this one seemed to
fit the bill quite well.
David Cornelius
http://CorneliusConcepts.com|||The more complex the transformations, then the slower DTS will run. If your
transformation VBScript task is making calls to external COM objects (like
the File System Object or a custom DLL component), then performance will
seriously take a hit. It is true that seperating the load and transformation
steps can improve performance and perhaps make the transformation
programming more maintainable. Here is the method I use for loading 10 GB+
daily into a data warehouse.
#1 Bulk copy / DTS raw source data into staging database / tables.
#2 Run data quality queries against the staging tables to verify
referential integrity, etc. This is especially important if you are
importing data from a 3rd party applications, and it's best to detect and
resolve these issues before it reaches production.
#3 Select rows from staging tables into production database / tables
using T-SQL for the transformations.
#4 Drop or truncate the staging tables.
For performance reasons, the staging database can be placed on a seperate
disk drive from the production database and RAID is optional.
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:77DE764E-C242-4448-B1DB-33FBD0E6EC58@.microsoft.com...
> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so
> far,
> but if you need anything complicated it's an ActiveX script. You do not
> use
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster
> for
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien|||Thanks for the input guys!
"Damien" wrote:

> I'm gonna say something slightly controversial just to get a bit of debate
> going, but I think DTS is just a drag and drop wizard for programmers who
> don't know what they're doing. As with any wizard, it can take you so far
,
> but if you need anything complicated it's an ActiveX script. You do not u
se
> VBScript (or a n other scripting language) to work with large volumes of
> data.
> Discuss? ; )
> I'm personally a big fan of bcp and BULK INSERT; there is nothing faster f
or
> shifting data.
> I guess DTS might be ok for small import jobs, and the workflow could be
> useful ...
> Let me know what you think.
>
> Damien

Tuesday, February 14, 2012

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