Date Format issue

[Cpreston]

Hi

I am exporting data from SQL server to CSV. One of these fields is a time and date field.

I want the format to be just dd/mm/yyy. I have gone into advance properties and selected the date filed and changed the DataType to date[DT_Date]
I click OK. This does not work as it keeps reverting back to database timestamp [DB_DbTimeStamp]

Could someone advise please how I get my date to the format I require please.
Thanks

 

[Cpreston]

HI

I have created a convert command in the SQL view which outputs the date how I want it now.

If there is a simple way in SSIS I would still be interested to know how

Thanks

 

[fredericofonseca]

the correct and easiest way is to change it on the select to the format you wish.
Alternative method is to create a derived column or data conversion step on your dataflow and convert the datetime to a date.

The above will give you the value as a date - but does not ensure that the format on the csv file will be the one you asked for as that is locale dependent. If your requirement is to have the date always on a particular format then convert manually to a string. again it is easier to do this in tsql.

And you should consider that using a ISO format for the date may be your best option as it will be read and converted by almost any software.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

One issue I do have is I have my SQL to have 08:00 and 17:30
This exported from SSIS into CSV no problem.

I have now been informed the filed must be a decimal place and this as made the output to CSV incorrect.
I change it in SQL to 08.00 and 17.30

The out put in CSV file as 8 and 17.3

It goes to excel for CSV. If I view the result in SSIS it looks ok. Is there a way round this or is it how excel handles the import.

Thanks

 

[fredericofonseca]

never ever ever use Excel to validate that your file is on the correct format.

edit it with a normal text editor - if it is correct there and according to the specifications then that is what matters.

Excel will do all type of guessing and formatting and should not be used for testing.

Now if the final target of an extract is Excel you may need to extract data in a particular way and you will probably also need to run a Excel vba macro over the data once its loaded onto excel to format it according to the requirements. Not the case here I think.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Cpreston]

Hi

yes I must agree. However, when I create .CSV from SSIS it automatically creates the CSV in Excel.
I have changed the extension to .txt and opens up in Notebook and the format is correst. So obvioulsy an excel issue.
Therefore how do i get the file to be .CSV without Excel being the program selected to make the CSV file?

Thanks

 

[fredericofonseca]

you are mixing things.

one thing is creating the CSV file - that has nothing to do with Excel and you don't even need to have Excel on the computer where the SSIS package runs.

Another thing is opening the file for processing - by default if you have Excel installed on the computer if you double click on the csv file it will open with Excel. But this has nothing to do with SSIS.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[micang]

To illustrate what fredericofonseca is saying, instead of double clicking the csv file where it automatically opens excel(that is because the csv extension is associated with excel), open up a new workbook of excel and import the csv (Data\From Text) - go through the wizard (choose delimited and comma as delimiter) - make the column that has the time a TEXT data format in the wizard. You will then see that the zeros will remain there.

The reason they are disappearing is because when you double click the csv and excel is automatically opening it, it sees these values as numbers and is automatically removing the zeros.

Thanks

Michael

 

[Cpreston]

Hi

Yes of course thank you for the explanation and quite correct I was getting confused with the actual outcome.
The file itself will be imported to another business system so I am assuming their import ruotine will deal with this and I will be testing with them tomorrow.

Thanks for the replys and assistance.

 

[Cpreston]

Hi

Ok I have been using opening the CSV file through Excel import routine. We have come across an issue with a filed we are exporting.

The field holds special instructions and within this there as been data entered with carriage returns.

When this is the case the field is then forcing an extra row. In the image below you can see that the line Shiplap always SG2 should actually be a part of the row and just after the part which starts with *Must Change all price to per metre*

Is there a way to prevent this field from doing this perhaps in the Advanced part in the flat file connection manager editor perhaps. Thanks

 

[Cpreston]

Hi

Its ok solved this within SQL using REPLACE(REPLACE(dbrderHeader.SpecialInstructions, CHAR(13), ' '), CHAR(10), '')

Thanks