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.

DTS package to overwrite the existing Excel

Hello,
Is there are a way to set up a DTS package
to export data to an Excel format, overwriting or deleting the existing Excel file.
Thank you,
Ythere r a few tricks for this.
(1) add a ActiveX or SQL task before the export task to delete existing xls and copy a blank xls file in the same name
(2) export as CSV file and that can be opened with excel directly (can aslo change the extension to xls)
(3) write script to delete rows from existing excle file and then append data.

DTS Package - AS400 data load to SQL Server

I'm trying to convert numeric dates on the AS400 to smalldatetime in
SQL. I'm able to customize to match format but when I save the
tranformation I get the following error. I don't know why they sould
let you customize the field if you cant convert. Any ideas?
"Error Description: TranformationDateTimeString('X') column pair 1
(source column 'Y'(DBTYPE_NUMERIC), destination column 'Z'
(DBTYPE_DBTIMESTAMP)): Cannot convert source datatype to datetime
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!If this is an import, try dumping it to a temp table then let the MS drivers
do the job natively. What drivers are you using for this? client access?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

DTS output file with "Append " mode?

I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

GuyangDid you ever get an answer on this? I have the same requirement

Originally posted by guyang2000
I have set up an export which use DTS service to extract data from a table to a Flat ( text) format file.

Is there any way setup this simple DTS as a " APPEND" mode? so that next time's data will append to the flat file output?

because the default way I can do for this flat file is " overwrite " .
the second time's run will always overwrite the privious run result.
even save the DTS scipt to VB file still can't find where to setup output file write mode.

Appreciate any help

Regards

Guyang|||If you transform the data to a excel sheet instead of a flat file its always appended. you might think of making the DTS package in such a way that first step will move data to a excel file(which is always appended) and the second step will transform data from that excel to a text file. this might solve your purpose.|||Thank-you but,

That won't work for me. The number of entries exceeds the limitations of Excel.

Currently my only viable option is to export subsets into a number of text files and concatenating them into one text file.

To me this is less than ideal.|||I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||I am currently doing exactly that. But be careful if the order is important..

I am transferring a varchar value into an ntext value in another table (only way to maintain paragraph formatting) to generate LDIF files for an X500 directory. I have attempted to force a sort order through a series of SQL UPDATE statements.

Unfortunately, the sort order gets destroyed by the size of the export to the point that the text file does not match the order of the table. Indexing of this table is impossible.

Originally posted by bpdWork
I was about to post the same question, but hoping there was an append mode for bcp. There is no switch listed for appending, but I really need it.

The alternative for me would be to post results to a temporary table, then output that table to a file, but I am hoping to avoid it.|||My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.|||Take a serious look at an ntext (blob) as opposed to a nvarchar(4000). You can maintain the format and end charachters in an ntext to text output.

Originally posted by bpdWork
My requirement is to build an SAP feed of billing data for a customer. Since ordering is very important (the file is actually broken up into blocks by cost center), I was thinking of a temp table cosisting of either an int or timestamp id, and a varchar(4000), which is much larger than any line can be. I simply output ordering by the id field.

I thought about building a text field and appending, but the added work of pointers, plus adding the end of row terminiators, etc. is a pain in the ass, and seems like a breeding ground for bugs. Sounds like our requirements are different though.

DTS Output Excel File - how to format?

Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?

Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)

Thanks!I will have a similar situation coming up so I am curious to see the answer. We are installing a SQL Server box in our department. It will be our own server where I will be able to create DTS jobs to export data every 15 minutes throughtout the course of the day. I plan on appending data to a table and having it do so for each and every job's output on SQL Server. The issue is I am only inhouse very early in the morning so I want to allow the client to see the reports as they run. They are tech challenged so I want to use either Excel or Access to create reports as they are run.

ddave|||I had vb developers modify a csv file while I wrotw the sql...

It was a whole store and forward app...scheduling, formatting ect...

sql just delivered the file to a location...|||>Also an easier question:
>What is the best (easiest) way to create a unique filename in Excel with a >datetimestamp in the file name (i.e. MyFile-20040608.xls)

DTS outputs it to any MyFile.xls and the next ActiveX step renames MyFile.xls file to MyFile-<date>.xls
-rohit

DTS Import txt File looping.

Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>

DTS Import from Text file - can't skip just one row

Hello,
I am trying to import a text file to a SQL table. The file is fixed length
format, but the first record is a header record and I want to skip it. If I
set the import wizard to skip 1 row, it skips the header AND the next row.
If I set it to skip 0 rows, I get the header AND the next record. I cannot
get it to skip just the header. I did NOT select "First row has column
names."
Any ideas?
Thanks,
- ArtHave you tried selecting the "First row has columne names"
at all? What happens when you do this? I have used this
a number of times when the column headers were actually in
the first row, but I am not sure what it will do with
random header data in te first row.
Also, is the header the correct fixed length? Maybe that
is why skipping the first row removes the first real data
row also? Or maybe there is a missing end of row
character?
Just some initial thoughts. I hope this helps somehow.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hello,
>I am trying to import a text file to a SQL table. The
file is fixed length
>format, but the first record is a header record and I
want to skip it. If I
>set the import wizard to skip 1 row, it skips the header
AND the next row.
>If I set it to skip 0 rows, I get the header AND the next
record. I cannot
>get it to skip just the header. I did NOT select "First
row has column
>names."
>Any ideas?
>Thanks,
>- Art
>
>.
>|||Art,
just check the First row has column names, and do not skip any row.
Quentin
"Art Bragg" <abragg@.transedge.com> wrote in message
news:OztK6aeWDHA.3088@.tk2msftngp13.phx.gbl...
> Hello,
> I am trying to import a text file to a SQL table. The file is fixed
length
> format, but the first record is a header record and I want to skip it. If
I
> set the import wizard to skip 1 row, it skips the header AND the next row.
> If I set it to skip 0 rows, I get the header AND the next record. I
cannot
> get it to skip just the header. I did NOT select "First row has column
> names."
> Any ideas?
> Thanks,
> - Art
>