DTS-example

I found this useful:
http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,
"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.
|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/...ortxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/...ortxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTS-example

I found this useful:
http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
work.Got the error message:
'Can't create object: MSXML2.DOMDocument.4.0'
/Kent J,"Kent Johnson" <08.6044303@.telia.com> wrote in message
news:e7zgd.7447$d5.63210@.newsb.telia.net...
> I found this useful:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> work.Got the error message:
> 'Can't create object: MSXML2.DOMDocument.4.0'
> /Kent J,
You need to install XML 4.0. Goto http://msdn.microsoft.com. In the "Search
MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
button.|||Ron,
Great! Now it works!
/Kent J.
"Ron Hinds" <__NoSpam__ron@.__ramac__.com> wrote in message
news:#pfegggvEHA.1308@.TK2MSFTNGP09.phx.gbl...
> "Kent Johnson" <08.6044303@.telia.com> wrote in message
> news:e7zgd.7447$d5.63210@.newsb.telia.net...
> > I found this useful:
> > http://www.perfectxml.com/articles/xml/importxmlsql.asp#4
> >
> > But I didn't get the example (DTS ActiveX Script & MSXML 4.0 DOM) to
> > work.Got the error message:
> > 'Can't create object: MSXML2.DOMDocument.4.0'
> >
> > /Kent J,
> You need to install XML 4.0. Goto http://msdn.microsoft.com. In the
"Search
> MSDN for" box type in XML 4.0. Click Code and Downloads then the "Go"
> button.
>

DTSDestination - equivalent in SSIS?

Hi,

I am converting an ActiveX script. I am looking to replace the functionality of this statement:

If DTSDestination("CartoonCharacter") = "Felix" Then ...

So, it's checking the value in the TABLE, not the file.

I need to rewrite this for SSIS. What is the recommended approach? Is there a built-in task that will do this and how?

Thanks much

You'd use a lookup component to query the destination and then later use a derived column or a conditional split transformation to compare the lookup result with the source value (or a hardcoded value, if you wish)

dts2000 migration, activex script and odbc connections help needed

Apologies if this has been covered before or if i am in the wrong place.

I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS package that connects to an AS400 to retrieve teh record count of a table for integrity checking. Under SQL server 2000 this worked fine but in SSIS it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server 05 on windows 2003. The odbc driver for the as400 has been installed also.

The activex script is as follows:

dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string

set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")

Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%

'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"

set rs1 = cn.execute (insertstr1)

'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function

I have since changed MSDASQL to SQLNCLI but (after errors re ad hoc distributed queries which i have enabled) i now get errors re linked servers:

ole db provider "sqlncli" for linked server "(null)" returned message "invalid connection string attrbute"

Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks

Chris

I am contacting the SSIS team but I suspect that you need to run the 32-bit version of SSIS since - as you pointed out - the ODBC driver is a 32-bit DLL, somehwat similar to the case discussed at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1.

|||

I confirmed: if there is only 32-bit ODBC driver, you need to run the package using 32-bit runtime.

DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER Error

I have an SSIS Package that first goes out using an ActiveX script to search to see if an access database exists. If it exists it deletes it and recreates the database shell, if not it creates a new one. The second step is to create the tables the Access database is going to need. The last step is to populate the Access tables with data from a SQL Server database. I have changed the package from running in 64Bit to False. I have tried EVERY security mode for the package and the package still fails with the:

[Access - Definition [3781]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

[DTS.Pipeline] Error: component "Access - Definition" (3781) failed the pre-execute phase and returned error code 0xC020801C.

Please help!

Thanks!

The error message may be appearing for one of the following reasons:

· The connection string specifies a provider that is not supported.

· You do not have access to the specified data source.

· The specified data source is being used by another application.

|||

Thanks for your help! What I had to end up doing is...I have 83 tables I need to copy over to Access...so come to find out I had to split half of the tables over to another data flow and now I see nothing but green when I execute the package...its the weirdest thing...but it works so I am going with it...thanks for you help tho!

|||I know that this question is update dbut for others who have the same problem need to check out the security option. If you're using a password to access a dB then you have to use Persist Security info=true otherwise use Integrated security=true

Good luck

DTS: Transform Data Task with ActiveX

I trying to write out comment records in 40 byte chunks from a much longer
field(see below) but I can't find any way of writing out the records inside
the loop. The code below works (and the loop is being executed) but nothing
turns up in the output. Does anyone have any thoughts?
'***************************************
********************
' Visual Basic Transformation Script
'***************************************
********************
' Copy each source column to the destination column
dim Comment
dim CommentOut
dim CommentSub
Function Main()
if DTSSource("OrderID") <> DTSGlobalVariables("OrderID") then
' Process Comments
Comment = DTSGlobalVariables("Comment")
Do While len(Comment) > 0
if len(Comment) > 40 then
CommentOut = left(Comment,40)
CommentSub = mid(Comment,41,len(Comment)-40)
Comment = CommentSub
else
CommentOut = Comment
comment = ""
end if
DTSDestination("OrderID") = DTSGlobalVariables("OrderID")
DTSDestination("LineNumber") = DTSGlobalVariables("LineNumber")
DTSDestination("Comment") = CommentOut
Main = DTSTransformStat_OK
Loop
' Store Comment from the record causing the control break
DTSGlobalVariables("OrderID") = DTSSource("OrderID")
DTSGlobalVariables("Comment") = DTSSource("PackingNote")
end if
Main = DTSTransformStat_OK
End FunctionProblem sorted. Please ignore...
"Spike" wrote:

> I trying to write out comment records in 40 byte chunks from a much longer
> field(see below) but I can't find any way of writing out the records insid
e
> the loop. The code below works (and the loop is being executed) but nothi
ng
> turns up in the output. Does anyone have any thoughts?
> '***************************************
********************
> ' Visual Basic Transformation Script
> '***************************************
********************
> ' Copy each source column to the destination column
> dim Comment
> dim CommentOut
> dim CommentSub
>
> Function Main()
> if DTSSource("OrderID") <> DTSGlobalVariables("OrderID") then
> ' Process Comments
> Comment = DTSGlobalVariables("Comment")
> Do While len(Comment) > 0
> if len(Comment) > 40 then
> CommentOut = left(Comment,40)
> CommentSub = mid(Comment,41,len(Comment)-40)
> Comment = CommentSub
> else
> CommentOut = Comment
> comment = ""
> end if
> DTSDestination("OrderID") = DTSGlobalVariables("OrderID")
> DTSDestination("LineNumber") = DTSGlobalVariables("LineNumber")
> DTSDestination("Comment") = CommentOut
> Main = DTSTransformStat_OK
> Loop
> ' Store Comment from the record causing the control break
> DTSGlobalVariables("OrderID") = DTSSource("OrderID")
> DTSGlobalVariables("Comment") = DTSSource("PackingNote")
> end if
> Main = DTSTransformStat_OK
>
> End Function
>
>

DTS, finish execution of the package

I want to finsih the execution of a DTS package from an ActiveX task. If a condition is ok, the package would continue as normal. If not, I want to finish the package without any error. Just without executing the next tasks. Do you have any idea?
ThanksIs the same case I posted before.. but, is there any way to define diferent flows in a DTS packet? I mean by using boolean global variables or someting like that, and without using the success/failure workflows.

Sorry about my english, thanks in advance.

DTS, Excel, and ActiveX

Hi-
I have an excel spreadsheet that I want to poulate with data via DTS. What
I need is the previous data to be cleared from the spreadsheet starting
with the second row(The first row is going to be column headers). Can that
be accomplished via an ActiveX? Any help is appreciated.Hi
You could either start with a template file without any data and then
copy/create the final data file before you start see
http://www.sqldts.com/default.aspx?292 for help.
Alternatively this may help to delete the whole sheet
http://www.sqldts.com/default.aspx?245
If not then issue a SQL delete statement againsta range in your sheet.
John
"Jeff York" wrote:

> Hi-
> I have an excel spreadsheet that I want to poulate with data via DTS. Wha
t
> I need is the previous data to be cleared from the spreadsheet starting
> with the second row(The first row is going to be column headers). Can tha
t
> be accomplished via an ActiveX? Any help is appreciated.

DTS xp..sendmail error

Similar problem was discussed here very recently but that didn't solved my problem.

Inside a DTS package, I have a ActiveX task that checks for a file at some location, on success it should send me a mail using xp..sendmail and on failure it should send me a mail.
The mail task on success works but the mailing task on failure does not work.
Please help.

-RohitPost the activex code.|||here is the ActiveX code.
======================================
Function Main()
Dim fs, MyFile
Dim FileLocation
Dim FileName
Dim FileInfo
Dim mm
Dim dd

if Len(month(date)) < 2 then
mm = trim("0" & month(date))
else
mm = trim(month(date))
end if

if Len(day(date)) < 2 then
dd = trim("0" & day(date))
else
dd = trim(day(date))
end if

FileName = "Invoice" & mm & dd & year(date) &".txt"
FileLocation = DTSGlobalVariables("Invoice_Input_Location").Value
FileInfo = FileLocation & FileName

Set fs = CreateObject("Scripting.FileSystemObject")

If (fs.FileExists(FileInfo)) Then
fs.MoveFile (FileInfo),(FileLocation & "ATLAS_Invoice.txt")
set fs=nothing
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If

End Function|||What version of sql server are you using ?|||Microsoft SQL Server 2000 - 8.00.876 (Intel X86)|||Insert "exit function" after each "Main=DTSTaskExecResult_XXX".|||using "exit function" also was not working, I tried unchecking "Fail package on first error" on the DTS package properties and its now working.

Thanks for your help.|||Yes - that will do it. What made you set that ? Basically, nothing will run after that step has failed.|||I checked it because I wanted the package to stop executing in case of a failure, never knew it wont even run the task that should run with a failure.

DTS with ActiveX script schedule problem

Hello gang, can someone help me with a problem?
I have a DTS package that uses ActiveX script to copy and rename files. I
can run this DTS package manually and the files are renamed and copied.
However, if I schedule this package with a job, the job runs successfully bu
t
the files are not copied and renamed. I have logged both the DTS activity
and Job activity and everything appears successful but no files are written.
Has anyone ran into this same problem.
These are the items I have thouroughly checked out:
The paths are correct (because the DTS package works)
The owner of the DTS has privleges to write(because the DTS package works)
The Owner of the Job has privileges to write(because the log activity shows
the steps in the DTS running)
Thanks for your help in advance.
Thanks Kllyj64BTW. Both the Job and the DTS package are running on the actual server, so
I
am not having a problem about "where I am executing the objects."
--
Thanks Kllyj64
"kllyj64" wrote:

> Hello gang, can someone help me with a problem?
> I have a DTS package that uses ActiveX script to copy and rename files. I
> can run this DTS package manually and the files are renamed and copied.
> However, if I schedule this package with a job, the job runs successfully
but
> the files are not copied and renamed. I have logged both the DTS activity
> and Job activity and everything appears successful but no files are writte
n.
> Has anyone ran into this same problem.
> These are the items I have thouroughly checked out:
> The paths are correct (because the DTS package works)
> The owner of the DTS has privleges to write(because the DTS package works)
> The Owner of the Job has privileges to write(because the log activity show
s
> the steps in the DTS running)
> Thanks for your help in advance.
>
> --
> Thanks Kllyj64|||I found the problem, SQL jobs runs under NT and does not recognize mapped
drives. I changed the DTS ActiveX scripts to use UNC drives and the job
worked. I should have looked further before posting.
--
Thanks Kllyj64
"kllyj64" wrote:
[vbcol=seagreen]
> BTW. Both the Job and the DTS package are running on the actual server, s
o I
> am not having a problem about "where I am executing the objects."
> --
> Thanks Kllyj64
>
> "kllyj64" wrote:
>

DTS with ActiveX script schedule problem

Hello gang, can someone help me with a problem?
I have a DTS package that uses ActiveX script to copy and rename files. I
can run this DTS package manually and the files are renamed and copied.
However, if I schedule this package with a job, the job runs successfully but
the files are not copied and renamed. I have logged both the DTS activity
and Job activity and everything appears successful but no files are written.
Has anyone ran into this same problem.
These are the items I have thouroughly checked out:
The paths are correct (because the DTS package works)
The owner of the DTS has privleges to write(because the DTS package works)
The Owner of the Job has privileges to write(because the log activity shows
the steps in the DTS running)
Thanks for your help in advance.
--
Thanks Kllyj64BTW. Both the Job and the DTS package are running on the actual server, so I
am not having a problem about "where I am executing the objects."
--
Thanks Kllyj64
"kllyj64" wrote:
> Hello gang, can someone help me with a problem?
> I have a DTS package that uses ActiveX script to copy and rename files. I
> can run this DTS package manually and the files are renamed and copied.
> However, if I schedule this package with a job, the job runs successfully but
> the files are not copied and renamed. I have logged both the DTS activity
> and Job activity and everything appears successful but no files are written.
> Has anyone ran into this same problem.
> These are the items I have thouroughly checked out:
> The paths are correct (because the DTS package works)
> The owner of the DTS has privleges to write(because the DTS package works)
> The Owner of the Job has privileges to write(because the log activity shows
> the steps in the DTS running)
> Thanks for your help in advance.
>
> --
> Thanks Kllyj64|||I found the problem, SQL jobs runs under NT and does not recognize mapped
drives. I changed the DTS ActiveX scripts to use UNC drives and the job
worked. I should have looked further before posting.
--
Thanks Kllyj64
"kllyj64" wrote:
> BTW. Both the Job and the DTS package are running on the actual server, so I
> am not having a problem about "where I am executing the objects."
> --
> Thanks Kllyj64
>
> "kllyj64" wrote:
> > Hello gang, can someone help me with a problem?
> >
> > I have a DTS package that uses ActiveX script to copy and rename files. I
> > can run this DTS package manually and the files are renamed and copied.
> > However, if I schedule this package with a job, the job runs successfully but
> > the files are not copied and renamed. I have logged both the DTS activity
> > and Job activity and everything appears successful but no files are written.
> > Has anyone ran into this same problem.
> >
> > These are the items I have thouroughly checked out:
> > The paths are correct (because the DTS package works)
> > The owner of the DTS has privleges to write(because the DTS package works)
> > The Owner of the Job has privileges to write(because the log activity shows
> > the steps in the DTS running)
> >
> > Thanks for your help in advance.
> >
> >
> > --
> > Thanks Kllyj64

DTS to SSIS

I've written thousands of DTS Packages with thousands of lines of ActiveX code which does thousands of things that would have been really simple in T-SQL.
How do I upgrade them to SSIS packages?
Do I need Visual Studio to do this?
Man it's slow todayi think that upgrade utility will come out right after they fix windows vista. anybody in Redmnond getting that sinking feeling?|||I've written thousands of DTS Packages with thousands of lines of ActiveX code which does thousands of things that would have been really simple in T-SQL.

How do I upgrade them to SSIS packages?

Do I need Visual Studio to do this?

Use Notepad :D|||Actually, I think this is only supported in Notepad Enterprise Edition.|||Install the The SQL Server 2000 DTS Designer, that's what I did.|||I was thinking about using cursors