ActiveX VBScript problem

Below is a code snippet that is throwing the following error:

Error Source: Microsoft DTS Package

Error Description: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Object required: 'Server'

Error on line 13

In my code line 13 is the following:
Set Cnxn = Server.CreateObject("ADODB.connection")

I did set up a Microsoft OLE DB connection in my DTS package for this
ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
appreciated.

Thanks,

-p

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success

' connection, command and recordset variables
Dim Cnxn, strCnxn

' create and open connection
Set Cnxn = Server.CreateObject("ADODB.connection")
strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"
Cnxn.Open strCnxn

End FunctionThe Server object is only available when running scripts under IIS. You
don't need it when running scripts via DTS or stand-alone. Try:

Set Cnxn = CreateObject("ADODB.Connection")

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pippen" <123@.hotmail.com> wrote in message
news:ESCWc.223077$eM2.87297@.attbi_s51...
> Below is a code snippet that is throwing the following error:
> Error Source: Microsoft DTS Package
> Error Description: Error Code: 0
> Error Source= Microsoft VBScript runtime error
> Error Description: Object required: 'Server'
> Error on line 13
> In my code line 13 is the following:
> Set Cnxn = Server.CreateObject("ADODB.connection")
> I did set up a Microsoft OLE DB connection in my DTS package for this
> ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
> appreciated.
> Thanks,
> -p
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Main = DTSTaskExecResult_Success
> ' connection, command and recordset variables
> Dim Cnxn, strCnxn
> ' create and open connection
> Set Cnxn = Server.CreateObject("ADODB.connection")
> strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"
> Cnxn.Open strCnxn
> End Function|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:NNGWc.4935$N04.1043@.newssvr23.news.prodigy.co m...
> The Server object is only available when running scripts under IIS. You
> don't need it when running scripts via DTS or stand-alone. Try:
> Set Cnxn = CreateObject("ADODB.Connection")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pippen" <123@.hotmail.com> wrote in message
> news:ESCWc.223077$eM2.87297@.attbi_s51...
> > Below is a code snippet that is throwing the following error:
> > Error Source: Microsoft DTS Package
> > Error Description: Error Code: 0
> > Error Source= Microsoft VBScript runtime error
> > Error Description: Object required: 'Server'
> > Error on line 13
> > In my code line 13 is the following:
> > Set Cnxn = Server.CreateObject("ADODB.connection")
> > I did set up a Microsoft OLE DB connection in my DTS package for this
> > ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
> > appreciated.
> > Thanks,
> > -p
> > '************************************************* *********************
> > ' Visual Basic ActiveX Script
'************************************************* ***********************
> > Function Main()
> > Main = DTSTaskExecResult_Success
> > ' connection, command and recordset variables
> > Dim Cnxn, strCnxn
> > ' create and open connection
> > Set Cnxn = Server.CreateObject("ADODB.connection")
> > strCnxn = "data source=Pluto;initial catalog=Stats;User
Id=sa;password=;"
> > Cnxn.Open strCnxn
> > End Function

That fixed it! Thanks for your help.

-p

ActiveX VBScript DTS IsDate

Can someone explian why I get a return of not a valid date when using
activeX vbscript for DTS:
Select (Date())
Case (IsDate(Date())
msgbox "OK"
Case Else
msbox "I cant figure this out"
End Select
msbox "I cant figure this out" pops rather than msgbox "OK"Your Select Case statement is malformed. Try:
Select Case IsDate(Date())
Case True
MsgBox "OK"
Case Else
MsgBox "I cant figure this out"
End Select
Hope this helps.
Dan Guzman
SQL Server MVP
<lebeau777@.hotmail.com> wrote in message
news:1143836184.567616.6570@.z34g2000cwc.googlegroups.com...
> Can someone explian why I get a return of not a valid date when using
> activeX vbscript for DTS:
> Select (Date())
> Case (IsDate(Date())
> msgbox "OK"
> Case Else
> msbox "I cant figure this out"
> End Select
> msbox "I cant figure this out" pops rather than msgbox "OK"
>|||Please ignore my previous response (copy/paste error). The script should
be:
Select Case IsDate(Date())
Case True
msgbox "OK"
Case Else
msgbox "I cant figure this out"
End Select
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u45IViZVGHA.3716@.TK2MSFTNGP15.phx.gbl...
> Your Select Case statement is malformed. Try:
> Select Case IsDate(Date())
> Case True
> MsgBox "OK"
> Case Else
> MsgBox "I cant figure this out"
> End Select
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <lebeau777@.hotmail.com> wrote in message
> news:1143836184.567616.6570@.z34g2000cwc.googlegroups.com...
>

ActiveX transformations gone from SSIS?

In good old fashioned DTS there was the ability to perform custom transformations using activeX / vbscripty type language - does this still exist or are we stuck with the derived column editor?

I have never used DTS; but I know you can create Script tasks in the control flow and script components in the data flow.

Jamie Thomson has a series of post where he points some differences between DTS and SSIS:

http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

|||

This does not still exist. You can either use the Derived Column transform or the Script Component transform.

One of the reasons this went away was the poor performance of using interpreted script. Another is that the built-in transforms in SSIS are so much more complete.

But the Derived Column transform could certainly use a better editor...

ActiveX to define presidence

I have a DTS package (SQL2000) which uses ActiveX scripts to check for records with certain criteria in a table. If the certain criteria is met, I want to initiate a child package that preforms a data-extraction. Otherwise move onto the next step which check for a different set of criteria. Currently if the criteria is met, I flag the task as success to dictate moving to one set of steps, otherwise failure & moving onto the next check.

The problem I am realizing, is that if I 'Fail' on of the check steps, subsequent steps furthur down the route that I am directing it to go, do not run. These are other ActiveX tasks that send email, and do furthur analysis.

Why is this the case? Can't you perform a logical check, and based upon the check have a decision to run certain steps? I attempted to use the result 'Main = DTSStepScriptResult_DontExecuteTask', but I must not be using it correctly, because it lists the task as returning failure.

Any help would be great.

Kevin Albrecht

DTS is called "SQL Server Integration Services" in the SQL Server 2005 generation. If you re-post your question in the SSIS forums (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1) the DTS experts that hang out over there could likely answer your question.

Thanks,
Steve

|||Moving thread as per the suggestion to keep in SSIS related forums.|||Don't use success/fail as the flag to move to the next step. Set a variable and use an "Expression" precidence constraint to pick which one to move too.

ActiveX to copy and rename file

Hi,

I'm looking to use ActiveX in a DTS to copy a file from on elocation to another.
I have the code for this (attached below kindly supplied by a colleague), so that's cool.

dim filesys
set filesys=CreateObject("Scripting.FileSystemObject")

If filesys.FileExists("D:\Credits\Credits_MTD.mdb") Then
filesys.CopyFile "D:\Credits\Credits_MTD.mdb", _
"C:\Credits\Credits_200602.mdb"
End If

But what I'm looking to do is go one step further.
As you can see, it renames the file to yyyymm, but this is hard coded, and I'm looking for a coded solution so I can rename the file with yesterday's date.

Can anybody help me please?declare a variable path,get year,month,day-1 from system date,concatnate all and concatnate again with ur file path.
pass that variable.|||Cheers - sorry to be a apin but is it something like this:

dim newDate
newDate = FormatDateTime( Now()-1 , yyyymmdd )

....\Credits_" & NewDate & ".mdb"

I tried that, and it came back with Path Not Found|||Cheers - sorry to be a apin but is it something like this:

dim newDate
newDate = FormatDateTime( Now()-1 , yyyymmdd )

....\Credits_" & NewDate & ".mdb"



I tried that, and it came back with Path Not Found

Unforunatly I dont have vb platform to test it.

create the variable first and pass that variable.


dim path
path="C:\Credits\Credits_"&NewDate&".mdb"
filesys.CopyFile "D:\Credits\Credits_MTD.mdb", path|||Cheers,
Will try that now.

ActiveX Script Task being ran twice?

I currently have a DTS package that contains an ActiveX Script Task. This script creates a report and saves the report in the database (for scalability reasons.) When I execute the step in Enterprise Manager it generates two entries in the database, it should only create one. If I take the script out of the DTS package and run it using CScript.exe it only creates one entry in the database.

Has anyone run into this problem before? Could I have done anything to the package that would cause it to execute the ActiveX Script Task twice?

Thanks for any advice to resolve this issue.

Have a great day,

CraigI figured it out, I was making the following call, "Call Main()" in my script and didn't realize that by default DTS packages call Main().

ActiveX Script Task - SSIS

Within a SQL 2000 DTS Package I have an ActiveX Script that would go within my transform tasks and update the queries by concatenating a "Where" clause with a date from a database table. This way I could keep track of when the last time I updated the table so that I could only bring down the rows since the last run. How can this be done within SSIS? I've been looking and I'm getting confused. Any help would be greatly appreciated.

The most analogous thing to an ActiveX Script in SSIS is the script component but that isn't what you want to use here. Instead, set the SQL statement that pulls data from your source dynamically like so: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

Thank you for your response. We went ahead and used a data flow with a RecordSet Destination. Then we used a "ForEach" loop to cycle through the RecordSet and run our query based on each value and then stored the data in a raw table.

Thanks again for all your help on this issue and others.

Jerry

sql

ActiveX Script Running DTS Causes Error

Hello All,

I have an ActiveX script that runs DTS pachkages. In the event that a package fails I want to display the error message. I found the following code (in bold) for doing this but I keep getting an error message as follows.

Type mismatch 'oSoStep.GetExecutionErrorInfo'

All help appreciated.

Thanks, Gary

Could you post your code?
Jason
|||For Each oStep In oPkg.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErrNum, sSource, sDescr
msgbox(sDescr)
End If
End If
Next

activex script programming - DTS

Hi guys,
Do you have any good documents or tutorial websites on advance DTS
programming? Especially, I want to learn to write activex script inside
DTS package.
Thanks
JayFolks at DTS newsgroup would also be able to address this request.
http://www.microsoft.com/technet/co...c.sqlserver.dts
Thanks
http://zulfiqar.typepad.com
BSEE, MCP
"Britney" wrote:

> Hi guys,
> Do you have any good documents or tutorial websites on advance DTS
> programming? Especially, I want to learn to write activex script inside
> DTS package.
>
> Thanks
> Jay
>
>

ActiveX Script in DTS

I have a large csv import into a SQL Server data base and I am trying to
transform a field to DATETIME before it gets to the database. The data in
the field is '20030100' or something similar the first four characters are
year the next two are month an the last are place holders as the mainframe
application does not transfer its DATETIME fields nicely. there for I want
to do something like this
Function Main()
DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
Main = DTSTransformStat_OK
End Function
but it fails. I have also tried setting the value to Left(
DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD") ,
6 ) , '28').
Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
it is ok. It appears that I am trying to get 28 into the field wrong. Any
help out there?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyNever mind. I found it. I used single quotes instead of double quotes around
the 28.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||Ok mind again. This function fails because for some reason it does not like
converting the string value "20030128" into a DATETIME. Up to now I have
been loading the csv file into the database then running a stored procedure
that runs an update on the column that has the bogus datetime then I have
been loading that across to another table that has the date time field and
that works I believe because the TRANSDT field in the first is varchar. I
realize that I could convert the column in the first table but I would like
to do it in the initial load. Any suggestions?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:ekFkHFRPEHA.680@.TK2MSFTNGP11.phx.gbl...
> Never mind. I found it. I used single quotes instead of double quotes
around
> the 28.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> > I have a large csv import into a SQL Server data base and I am trying to
> > transform a field to DATETIME before it gets to the database. The data
in
> > the field is '20030100' or something similar the first four characters
are
> > year the next two are month an the last are place holders as the
mainframe
> > application does not transfer its DATETIME fields nicely. there for I
want
> > to do something like this
> >
> > Function Main()
> > DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> > Main = DTSTransformStat_OK
> > End Function
> >
> > but it fails. I have also tried setting the value to Left(
> > DTSSource("TRANCD") , 6 ) & '28' and to concat( Left(
DTSSource("TRANCD")
> ,
> > 6 ) , '28').
> >
> > Each one files but if I just set it to the Left( DTSSource("TRANCD") ,
6 )
> > it is ok. It appears that I am trying to get 28 into the field wrong.
Any
> > help out there?
> >
> > --
> > Andrew C. Madsen
> > Information Architect
> > Harley-Davidson Motor Company
> >
> >
>|||If anyone is interested. The solution I came up with is this:
Function Main()
Dim DateStrY
Dim DateStrM
Dim DateStrD
Dim DateStr
Dim HOLD
DateStrY = LEFT(DTSSource("TRANDT"),4)
DateStrM = Mid( DTSSource("TRANDT") , 5 ,2 )
DateStrD = "28"
DateStr = DateStrM + "/" + DateStrD + "/" + DateStrY
DTSDestination("TRANDT") =DateStr
Main = DTSTransformStat_OK
End Function
I needed to reformat the string as a date because the transform will not
recognize 20030128 as a date but does recognize 01/28/2003.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

ActiveX Script in DTS

I have a large csv import into a SQL Server data base and I am trying to
transform a field to DATETIME before it gets to the database. The data in
the field is '20030100' or something similar the first four characters are
year the next two are month an the last are place holders as the mainframe
application does not transfer its DATETIME fields nicely. there for I want
to do something like this
Function Main()
DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
Main = DTSTransformStat_OK
End Function
but it fails. I have also tried setting the value to Left(
DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD") ,
6 ) , '28').
Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
it is ok. It appears that I am trying to get 28 into the field wrong. Any
help out there?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
Never mind. I found it. I used single quotes instead of double quotes around
the 28.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
|||Ok mind again. This function fails because for some reason it does not like
converting the string value "20030128" into a DATETIME. Up to now I have
been loading the csv file into the database then running a stored procedure
that runs an update on the column that has the bogus datetime then I have
been loading that across to another table that has the date time field and
that works I believe because the TRANSDT field in the first is varchar. I
realize that I could convert the column in the first table but I would like
to do it in the initial load. Any suggestions?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:ekFkHFRPEHA.680@.TK2MSFTNGP11.phx.gbl...
> Never mind. I found it. I used single quotes instead of double quotes
around[vbcol=seagreen]
> the 28.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
in[vbcol=seagreen]
are[vbcol=seagreen]
mainframe[vbcol=seagreen]
want[vbcol=seagreen]
DTSSource("TRANCD")[vbcol=seagreen]
> ,
6 )[vbcol=seagreen]
Any
>
|||If anyone is interested. The solution I came up with is this:
Function Main()
Dim DateStrY
Dim DateStrM
Dim DateStrD
Dim DateStr
Dim HOLD
DateStrY = LEFT(DTSSource("TRANDT"),4)
DateStrM = Mid( DTSSource("TRANDT") , 5 ,2 )
DateStrD = "28"
DateStr = DateStrM + "/" + DateStrD + "/" + DateStrY
DTSDestination("TRANDT") =DateStr
Main = DTSTransformStat_OK
End Function
I needed to reformat the string as a date because the transform will not
recognize 20030128 as a date but does recognize 01/28/2003.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>

ActiveX Script in DTS

I have a large csv import into a SQL Server data base and I am trying to
transform a field to DATETIME before it gets to the database. The data in
the field is '20030100' or something similar the first four characters are
year the next two are month an the last are place holders as the mainframe
application does not transfer its DATETIME fields nicely. there for I want
to do something like this
Function Main()
DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
Main = DTSTransformStat_OK
End Function
but it fails. I have also tried setting the value to Left(
DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD") ,
6 ) , '28').
Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
it is ok. It appears that I am trying to get 28 into the field wrong. Any
help out there?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyNever mind. I found it. I used single quotes instead of double quotes around
the 28.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>|||Ok mind again. This function fails because for some reason it does not like
converting the string value "20030128" into a DATETIME. Up to now I have
been loading the csv file into the database then running a stored procedure
that runs an update on the column that has the bogus datetime then I have
been loading that across to another table that has the date time field and
that works I believe because the TRANSDT field in the first is varchar. I
realize that I could convert the column in the first table but I would like
to do it in the initial load. Any suggestions?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:ekFkHFRPEHA.680@.TK2MSFTNGP11.phx.gbl...
> Never mind. I found it. I used single quotes instead of double quotes
around
> the 28.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
in[vbcol=seagreen]
are[vbcol=seagreen]
mainframe[vbcol=seagreen]
want[vbcol=seagreen]
DTSSource("TRANCD")[vbcol=seagreen]
> ,
6 )[vbcol=seagreen]
Any[vbcol=seagreen]
>|||If anyone is interested. The solution I came up with is this:
Function Main()
Dim DateStrY
Dim DateStrM
Dim DateStrD
Dim DateStr
Dim HOLD
DateStrY = LEFT(DTSSource("TRANDT"),4)
DateStrM = Mid( DTSSource("TRANDT") , 5 ,2 )
DateStrD = "28"
DateStr = DateStrM + "/" + DateStrD + "/" + DateStrY
DTSDestination("TRANDT") =DateStr
Main = DTSTransformStat_OK
End Function
I needed to reformat the string as a date because the transform will not
recognize 20030128 as a date but does recognize 01/28/2003.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>sql

ActiveX Script

I ran this activeX script with my DTS package. For debugging purpose I included the message box. The message is showing the actual name of the file but the the file name is not changing to the name display in the message box.

Function Main()
ms_year = year(date())
ms_month = month(date())
ms_day = day(date())
ms_date = ms_year & "_" & ms_month &"_" & ms_day & "_"

dim objFSO, strFullNm

set objFSO = CreateObject("Scripting.FileSystemObject")
strFullNm = DTSGlobalVariables("FilePathRoot").value & "\IMS_ALL_DONATIONS.txt"

'check to see if file exist and then concatenate the file
if objFSO.FileExists(strFullNm) then
strFullNm = DTSGlobalVariables("FilePathRoot").value & "\ " & ms_date & "IMS_ALL_DONATIONS.txt"
MsgBox "This is the new filename: " & strFullNm
else MsgBox "File does not exist"
end if

set objFSO = nothing
Main = DTSTaskExecResult_Success
End FunctionUm, I'm not sure exactly what you are trying to achieve here.

You say the correct filename is being displayed but strFullNm is not being set to the right value, is that right??

After you display strFullNm what are you doing with it? Looking at the code you have there you aren't doing anything with it at all and it will be discarded. Are you trying to rename a file or move a file or something?

activex error and dts

I am tying to run this DTS:
the job has an activex part which uses :
"Set fso = CreateObject("Scripting.FileSystemObject")"
this row gives me the following error:
"ActiveX Scripting encountered a Run Time Error during the execution of the script. "
the dts worked when I run it local on my station but when I try running it on the server this error comes
can u help?
10xSound like permission problem.
http://support.microsoft.com/kb/298725

ActiveX and SQL Server question?

Apologies for posting this in the wrong section, now pointed to here.

I have the following

AciveX script set up as a DTS package which I would like to alter so

that the output is sent to another server. :-

Function Main()
dim oCmd, sSql, oDom
set oDom = CreateObject("Msxml2.DOMDocument")
set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=MyServer; Initial Catalog=MyDB; Integrated Security=sspi"

sSql = "<ROOT

xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>SELECT

* FROM VW_Projs1 FOR XML AUTO</sql:query></ROOT>"
oCmd.CommandText = sSql
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("Output Stream") = oDom
oCmd.Execute, , 1024
oDom.Save "C:\Inetpub\wwwroot\test.xml"
Main = DTSTaskExecResult_Success
End Function

I want to schedule the package so it runs overnight, so
1.

Do I have to change the 'Integrated Security=sspi' part of the script

to reflect a user who has suitable rights to create/write a file on the

other server? If so How?
2. Would I redirect to the another

server/directory just by changing the path in oDom.Save to

"ServerName\DirectoryName".? or would there be a different format?

Thanks

I don't have much exp. in ActiveScript but I can say that if you are creating a flat file on a different server apart from the local server then you need to define the path correctly \\servername\path in order to create the file. Also ensure to check the SQLAgent permissions on the specified path as you're going to schedule this as a job.

Also you might try creating the file locally on the server and then copy the same using copy command in SQL scheduled job.

ActiveX ActivePerl DTS Task

I need help with executing an ActiveX Perl DTS Task.

I have ActivePerl installed and I do not get a task error when I run it. The script works perfectly when I run it from a command prompt. The problem is that the task will not extract the files from the tar file.

sub Main()
{
#using modules
use Archive::Tar;
use Compress::Zlib;

#create a new tar archive object
my $tar = Archive::Tar->new;

#set the name of the compressed files
$main_tar = "C:\Inetpub\wwwroot\legacyrealestate\db\DW168E.tmp. tar";

#read the main tar file and extract files
$tar->read($main_tar,1);
$tar->extract();

return 0; # DTSTaskExecResult_Success;
}

Any clue?I think I know why it appears that it wasn't working. It worked but not like I expected. It extracted the files into the default sql server directory.

So My next question is how do specify the directory to extract to?

Originally posted by jcochran2003
I need help with executing an ActiveX Perl DTS Task.

I have ActivePerl installed and I do not get a task error when I run it. The script works perfectly when I run it from a command prompt. The problem is that the task will not extract the files from the tar file.

sub Main()
{
#using modules
use Archive::Tar;
use Compress::Zlib;

#create a new tar archive object
my $tar = Archive::Tar->new;

#set the name of the compressed files
$main_tar = "C:\Inetpub\wwwroot\legacyrealestate\db\DW168E.tmp. tar";

#read the main tar file and extract files
$tar->read($main_tar,1);
$tar->extract();

return 0; # DTSTaskExecResult_Success;
}

Any clue?|||This is really a perl question but I would try setting the directory by using the SetCwd(directory) command.|||Thanks I will try that.|||I keep getting a "Function not found" error. Can anybody give me a peice of sample code using Win32::SetCwd()?|||Here is the updated perl script.

#using modules
use Archive::Tar;
use Compress::Zlib;
use Win32::File;

sub Main()
{
#create a new tar archive object
my $tar = Archive::Tar->new;

#set the name of the compressed files
$main_tar = "C:\Inetpub\wwwroot\legacyrealestate\db\DW168E.tmp. tar";
$res_tar = "ListingsRESIDENTIAL-Residential.txt.gz";

# set the directory
$dir = "C:\Inetpub\wwwroot\legacyrealestate\db\";
Win32::SetCwd($dir);

#read the main tar file and extract files
$tar->read($main_tar,1);
$tar->extract();

return 0; # DTSTaskExecResult_Success;
}

ActiveX

I have to put some ActiveX code in the first step of a DTS package, which will search thru all the files in some folder and if it finds a file with filename starting with "Test" (like TestFile.txt), the script will rename it and then use it for transforming data to SQL tables.

In VB Dir$ function could have been used, what should I use in ActiveX?

Thanks a lot for your helpI'm not sure how to rename the file but you might be able to use the xp_fileexist extended proc to see if the file is in the directory.|||This is a start:

Function Main()

Dim objFSO
Dim objFolder
Dim objFile
Dim fileName
Dim folderName

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("\\server\share$\dir\")
folderName = "\\server\share$\dir\Archive\" & Month(Now) & Day(Now) & Year(Now) & "_" & Hour(Now) & Minute(Now) & Second(Now) & "\"

For Each objFile in objFolder.Files

objFSO.CreateFolder(folderName)

fileName = objFile.Path

objFSO.MoveFile fileName, folderName
Next

Main = DTSTaskExecResult_Success

End Function|||Now I'm getting what I need to do, thanks a lot for your help.sql

Active X script help in a DTS package

I have a file that gets placed on our ftp site once a week. The file is named achconsxx.dat

The xx is the fiscal week meaning the file one week looks like achcons18.dat and the next week it will look like achcons19.dat

I am trying use an ActiveX script in my DTS package to unzip that file and rename to achcons.dat so I can import.

My code looks like this so far:

Function Main()

Dim objWshshell, objFso
Dim strCmd, strSubject, strMessage, strFileattachment, rc

DTSGlobalVariables("gv_strWklydir").value = "\\E$\Update\Data\" ' Weekly directory
DTSGlobalVariables("gv_strACHFile").value = "achcons" & DTSGlobalVariables("gv_strFW").value & ".zip" ' ACH Group weekly file

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objWshshell = CreateObject("WScript.Shell")
rc = 0

' Build the Winzip command

If (objFso.FileExists(DTSGlobalVariables("gv_strWklydir").value & DTSGlobalVariables("gv_strACHFile").value)) Then

strCmd = "winzip32.exe -e -o " & DTSGlobalVariables("gv_strWklydir").value &_
DTSGlobalVariables("gv_strACHFile") & " " & DTSGlobalVariables("gv_strWklydir").value

rc = objWshshell.Run(strCmd, 1, true)

If (objFso.FileExists(DTSGlobalVariables("gv_strWklydir").value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFile")) & ".dat")) Then
objFso.GetFile(DTSGlobalVariables("gv_strWklydir").value & objFso.GetBaseName(DTSGlobalVariables("gv_strACHFile")) & ".dat").Copy (DTSGlobalVariables("gv_strWklydir").value & "achcons.dat")
End If

Set objWshshell = Nothing
Set objFso = Nothing
End If

I also have an e-mail routine that sends me an e-mail if the file doesn't exist. The e-mail works so my code can't see the file it needs to unzip and rename.

Any help is appreciated.May refer to http://www.sqldts.com for any reference on activex using in DTS.

Active X Error

I get this error msg when I run my ActiveX script in a DTS package.

Err number: 429
Err Message: ActiveX component can't create object

When I Set crApplication = CreateObject("CrystalRuntime.Application.9")

if Err.Number <>0 then
'I get the message here

ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative accessdo you have crystal object library installed on the box?|||Hi there,

I assume you have installed Crystal on your box! So, I can imagine two reasons:

1. No file type is associated with this application (had this kind of problem recently with Cognos Impromptu!) and the system doesn't "know" CrystalRuntime.Application.9.

2. Is CreateObject("CrystalRuntime.Application.9") the correct syntax? Is the ".9" correct there?

Greetings,
Carsten

Originally posted by vmlal
I get this error msg when I run my ActiveX script in a DTS package.

Err number: 429
Err Message: ActiveX component can't create object

When I Set crApplication = CreateObject("CrystalRuntime.Application.9")

if Err.Number <>0 then
'I get the message here

ne one know what this is about? I'm running this package on SQL server 2000 with Admistrative access|||hm, i don't think file association is required if the object library is properly registerd with all dependent components (i've seen dll's that cannot be registered without ocx's being registered first, etc.)|||Originally posted by ms_sql_dba
hm, i don't think file association is required if the object library is properly registerd with all dependent components (i've seen dll's that cannot be registered without ocx's being registered first, etc.)

If thats not the case where could i start resovling this issue? Any starting points? thanks.|||you need to make sure that you know exactly what components have been loaded on the server and that you have the required dll (-s) present. if the installation was done through windows installer you will see some footprint of it in controll panel/add/remove programs.sql

Active Script OK But Scheduled Job Fails

I have an ActiveX Script task in a DTS package which executes fine
within DTS.
However, if I run it as a sheduled job, it fails with a 'path not
found error'. I've cut the code down to the approriate problem and
posted it here, it just makes a simple text file with a name derived
from the current date and time and puts 'Hello World' in the file
although in reality I go on to make an xml file. (make sure your path
exists before you execute it). Why does the scheduled job not execute
this simple task. I have loads of other jobs that run packages to a
schedule and the account is a domain account with windows
administrative rights.

'******************************
' Visual Basic ActiveX Script
'**********************

Const strPath="d:\xml"

Private Function GetDateTime()
Dim strNow, stryear, strMonth, strDay, strTime
strNow = Now
stryear = Mid(strnow, 7, 4)
strMonth = Mid(strnow, 4, 2)
strDay = Left(strnow, 2)
strTime = Replace(Mid(strnow, 12, 8), ":", "")

GetDateTime = stryear + strMonth + strDay + strTime
End Function

Function Main()
Dim fso,f,MyFile 'file objects
Set fso = CreateObject("Scripting.FileSystemObject")
dim strRightNow
strRightNow=GetDateTime()
Set MyFile= fso.CreateTextFile(strPath &"\" & "UPD-"
& strRightNow & ".txt", True) 'This line fails from a scheduled job
MyFile.WriteLine("Hello World")
MyFile.Close
Main = DTSTaskExecResult_Success
End Function

'Error Source= Microsoft VBScript runtime error Error Description:
Path not found Error on Line 25"TiredOfSpam" <spam@.drmcl.free-online.co.uk> wrote in message
news:403767a1.0410080148.b529ff@.posting.google.com ...
>I have an ActiveX Script task in a DTS package which executes fine
> within DTS.
> However, if I run it as a sheduled job, it fails with a 'path not
> found error'. I've cut the code down to the approriate problem and
> posted it here, it just makes a simple text file with a name derived
> from the current date and time and puts 'Hello World' in the file
> although in reality I go on to make an xml file. (make sure your path
> exists before you execute it). Why does the scheduled job not execute
> this simple task. I have loads of other jobs that run packages to a
> schedule and the account is a domain account with windows
> administrative rights.
> '******************************
> ' Visual Basic ActiveX Script
> '**********************
> Const strPath="d:\xml"
> Private Function GetDateTime()
> Dim strNow, stryear, strMonth, strDay, strTime
> strNow = Now
> stryear = Mid(strnow, 7, 4)
> strMonth = Mid(strnow, 4, 2)
> strDay = Left(strnow, 2)
> strTime = Replace(Mid(strnow, 12, 8), ":", "")
> GetDateTime = stryear + strMonth + strDay + strTime
> End Function
> Function Main()
> Dim fso,f,MyFile 'file objects
> Set fso = CreateObject("Scripting.FileSystemObject")
> dim strRightNow
> strRightNow=GetDateTime()
> Set MyFile= fso.CreateTextFile(strPath &"\" & "UPD-"
> & strRightNow & ".txt", True) 'This line fails from a scheduled job
> MyFile.WriteLine("Hello World")
> MyFile.Close
> Main = DTSTaskExecResult_Success
> End Function
> 'Error Source= Microsoft VBScript runtime error Error Description:
> Path not found Error on Line 25

Assuming that D: is a local drive (ie. not mapped to a network share), I can
reproduce your error using the code sample, but I have no idea what the
solution is. Usually, this comes down to a problem of ownership and/or
permissions:

http://support.microsoft.com/?kbid=269074

However, even with both the package and the job owned by the same account (I
tried using both sa and a domain admin account in the sysadmin role), and
with the NTFS permissions of the target folder set to allow Everyone to
create a new file, the scheduled job still failed.

You might want to post this in microsoft.public.sqlserver.dts - you may get
a more useful answer there.

Simon