Data Parsing in SQL Server views

If column1 in SQL Server column is text: 19980701

What is the syntax in the select statement to convert it to a date
like: 07/01/1998

Thanks for any help

RbollingerYou can use CAST() on a string to get a DATETIME data type. Of course,
you know that you do not formatting in the database in a tiered
architecture. And you know that you should only use the ISO-8601
standards for temporal data. If you want to be a bad programmer, you
can use CONVERT(). It is a proprietary piece of legacy syntax that
will let you pick a national format.

Data into XML

Hi, I have a select statement which returns data in sql server 2000, however i need to convert this into an XML document. I have the option of doing it via VB6 however, i do not want to do this as there will be up to 50, 000 records conveting, there must be a simple way just to convert it on the server.

Any ideas appretiatedJust bumping this back up the chain|||Not too sure if this is what you are looking for.

Dim comp As New Data.DataSet

After reading all the data required into your data adapter ect.

Use the final coding to write to an xml file in the spcified location:

comp.WriteXml("C:\xml\TestXml_Complete.xml")

\TestXml_Complete.xml is the name of the xml you will be saving...

Data Flow: Converting data in multiple columns

Hi,

I'm just wondering what's the best approach in Data Flow to convert the following input file format:

Date, Code1, Value1, Code2, Value2

1-Jan-2006, abc1, 20.00, xyz3, 35.00

2-Jan-2006, abc1, 30.00, xyz5, 6.30

into the following output format (to be loaded into a SQL DB):

Date, Code, Value

1-Jan-2006, abc1, 20.00

1-Jan-2006, xyz3, 35.00

2-Jan-2006, abc1, 30.00

2-Jan-2006, xyz5, 6.30

I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.

Ok, I have found a method to get what I wanted, but I'm not sure if it's the best approach. Any comments appreciated.

I first used a Multicast and feed the input data into 2 Script Components. The first Script Component has input columns of Date, Code1 & Value1, while 2nd Script Component has input columns of Date, Code2 & Value2. In both Script Components, the output columns are Date, Code & Value.

The output of each Script Component are then connected to a Sort and both Sort goes to a Merge. The output from the Merge will then go to a OLE DB Destination to be loaded into a SQL DB (not 2005 version).

Hope it makes sense.

|||

I think Unpivot tranformation cam deliver the same funcionality you put in your script components; but the multicast; the sort and merge join would be required anyway.

Rafael Salas

|||

Hi,

Why dont you have a union step feeded twice by the multicast

then

Delete and add lines so that you would have the following

Date date date

Field 2 cod1 cod2

Field 3 value1 value2

At the end you should have what you want,

Regards,