VBScriptQuestion for DTS

[thendrickson]

I need to create a DTS package where I use a query to import year, month and day into a datetime field.

My package fails on that line due to what I am certain is my lack of knowlege of Vb Scripting

The souce query is:

select distinct consentregid,inscarrier,schoolname,sendattempt,year(datepacketcreated)as yr,month(datepacketcreated)as mo,day(datepacketcreated)as da,schoolyear,dateofservice from tblChildPacketSchoolHistDet order by consentregid.

I need to concantenate The year, month and day using vbscript in the DTS package if I can.

I cannot seem to locate syntax for this for some reason.

Thanks

 

[Rhys666]

There's no need for an ActiveX task for this is there?

Can't you use datepart()? Assuming what you're after if the concatenated year+month+day of the datepacketcreated field that'd be;

select distinct 
	consentregid,
	inscarrier,
	schoolname,
	sendattempt,
	CAST(datepart(YYYY, datepacketcreated) as VARCHAR) + CAST(datepart(MM, datepacketcreated) as VARCHAR) + CAST(datepart(dd, datepacketcreated)as VARCHAR) as concatenateddate,
	schoolyear,
	dateofservice 
from 
	tblChildPacketSchoolHistDet  
order by 
	consentregid

apologies if it's not what you're after, if not can you clarify a little?

Rhys
Gene Roddenberry was a legendary pioneer of thought-provoking, futuristic science fiction. George Lucas created Jar Jar Binks

 

[thendrickson]

Oops,

Of course your suggestion will work. I would prefer to do the concantenation from within the DTS package using VBScript if I can. Not from within the SQL statement.

If not possible to do it with VB script, I will use the SQL of course.

I will be cleaning the data in the morning before anybody gets in so I have a bit of time

 

[Rhys666]

You can do it in VBScript but it's far, far less efficient as you'll likely have to access the data as a recordset, iterate it and do what you want row by row.

Any particular reason/need for wanting to use script over T-Sql?

Rhys
Gene Roddenberry was a legendary pioneer of thought-provoking, futuristic science fiction. George Lucas created Jar Jar Binks

 

[SQLDenis]

Something like this
DTSDestination("Year") = year(DTSSource("datepacketcreated")
DTSDestination("Month") = month(DTSSource("datepacketcreated")
DTSDestination("day") = day(DTSSource("datepacketcreated")

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[thendrickson]

Thanks for you help. I will use the SQL Statement.

The reason for wanting to use script is not a good one although it is valid in the situation I find myself.

Not worth the effort though.

I really apprecate your help. You at least convinced me to do it the right way regardless