Re: bas file does nothing

#1

In article <9EA2C501-708A-4CC5-8A57-FC062C6921AE (AT) microsoft (DOT) com>, TomTait747 wrote:

Quote:

The *.bas file created when saving the dts package to visual basic will go into Access, compile and run without error. The problem is that it does nothing. I've put in break points, changed the file path to ensure an error if one can be generated and nothing. The same thing happened in VB6. I should mention that I did add the DTS references in both cases prior to compiling with success.

When you say it does nothing. Does the code actually get hit?

Have you commented the line that says you are to run it now or the line that says save it now?

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

#2

In message <9EA2C501-708A-4CC5-8A57-FC062C6921AE (AT) microsoft (DOT) com>,
TomTait747 <TomTait747 (AT) discussions (DOT) microsoft.com> writes

Quote:

The *.bas file created when saving the dts package to visual basic will
go into Access, compile and run without error. The problem is that it
does nothing. I've put in break points, changed the file path to
ensure an error if one can be generated and nothing. The same thing
happened in VB6. I should mention that I did add the DTS references in
both cases prior to compiling with success.

How do you know it does nothing?
How do you know the package did not error during execution? The default
code produces just executes the package. There is no code to check that
the package executed successfully. DTS (rightly) does not throws
run-time errors all the way up to VB when it fails.

You may want to look at some of the methods shown here, and rip off the
error checking methods-

Execute a package from Visual Basic (VB)
(http://www.sqldts.com/default.aspx?208)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

#3

In article <5E721F0D-621E-4761-8589-EC89DD028AD3 (AT) microsoft (DOT) com>, TomTait747 wrote:

Quote:

Yes the code actually gets hit. I can set through the code and see it hit each line.
goPackage.Execute is not commented out. I've put in the password to connect to SQL Server. Why won't it at least throw an error?

When you say it does nothing. Does the code actually get hit?

Have you commented the line that says you are to run it now or the line that says save it now?

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Maybe you could try setting the FailOnError property of the package object to true

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

#4

Allan,
I created a package with the name sited by the code and made the switch you recommended. Now I am getting an error. Thank you. I'm sure I'll have another question in a minute. I'll post another question when I have one.

Tom

#5

If a DTS package fails to execute, such as bad file, then this does not
raise an error in VB/Access. To find out if an error has occurred you
need to check that the package step execution result or use events to
get the error event passed up. The package has executed OK, as a step
error is perfectly acceptable, since you can handle that with an On
failure constraint for example. The execution itself can work fine, but
the tasks/steps inside that execution may have failed, the distinction
is not obvious I know, but it is there.

Since you create the package as opposed to loading it, then taking the
sample code linked, ignore the load bit of the code, as this is covered
by your creation, so from page 2 you want to add -

oPKG.Execute ' Already Have this and above

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize ' Already Have this and above

You could do similar and adapt the Events sample from the MS KB, linked
from that article, or the event demo code provided in the download.

In message <9AC5C02D-906A-476F-80DB-A5B77A543BE1 (AT) microsoft (DOT) com>,
TomTait747 <TomTait747 (AT) discussions (DOT) microsoft.com> writes

Quote:

Darren,
I would love to rip off the code and use it, but after looking at the code,
it appears to me that the line
sPackageName = "MyPackage"
implies that there is a package saved within Enterprise manage in the
'folder' local packages...and that sPackageName is supposed to refer to
that. I do not have the package there (as far as I know). I have the
package in the form of VB script in a sub procedure called "Main"...and
that is where I would prefer to keep it for purposes of control via MS
Access. Maybe I do not understand this correctly, let me know, if so.
Allan Mitchell said I could run the whole package with vb code as
opposed to using DTSrun /A, for example.
To answer your question, the purpose and only purpose of the
package is to import a text file into SQL Server. After it runs, I check the
SQL Server table and there are still no records in it. No records=>does
nothing...that's how I connect the dots.
Because the package throws no errors no matter what garbage I put
in the file path I would say that I do not know that no errors occur. What I
do know is that the VBA with Access has a "on error goto trap" line that I
put in to trap errors, and the 'cursor' never goes to the trap...so the VBA
doesn't perceive an error as hit. I suppose your point is that the error
occurs on the SQL Server side and VBA doesn't know about it? Where
do I go from here?

"Darren Green" wrote:

How do you know it does nothing?
How do you know the package did not error during execution? The default
code produces just executes the package. There is no code to check that
the package executed successfully. DTS (rightly) does not throws
run-time errors all the way up to VB when it fails.

You may want to look at some of the methods shown here, and rip off the
error checking methods-

Execute a package from Visual Basic (VB)
(http://www.sqldts.com/default.aspx?208)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

#6

Darren,
I put in that code and I got this error:
Source: Microsoft Data Transformation Services Flat File Rowset Provider
Description: Incomplete file format information - file cannot be opened.

What is a Rowset Provider? Is that the record terminator?

#7

In message <2541A868-190E-4FE3-8C62-C69AF0916FA4 (AT) microsoft (DOT) com>,
TomTait747 <TomTait747 (AT) discussions (DOT) microsoft.com> writes

Quote:

Darren,
I put in that code and I got this error:
Source: Microsoft Data Transformation Services Flat File Rowset Provider
Description: Incomplete file format information - file cannot be opened.

What is a Rowset Provider? Is that the record terminator?

Flat File Rowset Provider is the text file driver, so that is the error
source basically.

Sounds like something if wrong in that area, or just missing
information, maybe a record terminator.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

#8

Darrel, this is not all that surprising. The record terminator is a pipe + a return character that appears as a square in notepad. It translates to chr(10) in visual basic. I can't get bulk copy to recognized it to save my life, which is why I'm trying to make it happen with DTS. MS Access can deal with it. DTS has no problem with the file when I run the local package from with Enterprise manager. Why does it cough when I run it from the VB script? Also, is there a spot in the code where I can define the record terminator...I could find one in the bas that Ent Mgr created.

Quote:

Sounds like something if wrong in that area, or just missing
information, maybe a record terminator.

#9

Darren,
I am going to go ahead and post another question. I'm not sure you are going to read this one. Plus, it basically another topic now.
-Tom

"Darren Green" wrote:

Quote:

In message <2541A868-190E-4FE3-8C62-C69AF0916FA4 (AT) microsoft (DOT) com>,
TomTait747 <TomTait747 (AT) discussions (DOT) microsoft.com> writes
Darren,
I put in that code and I got this error:
Source: Microsoft Data Transformation Services Flat File Rowset Provider
Description: Incomplete file format information - file cannot be opened.

What is a Rowset Provider? Is that the record terminator?

Flat File Rowset Provider is the text file driver, so that is the error
source basically.

Sounds like something if wrong in that area, or just missing
information, maybe a record terminator.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: bas file does nothing

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: bas file does nothing - 07-13-2004 , 02:21 PM

Re: bas file does nothing - 07-13-2004 , 03:16 PM

Re: bas file does nothing - 07-13-2004 , 03:21 PM

Re: bas file does nothing - 07-13-2004 , 04:30 PM

Re: bas file does nothing - 07-13-2004 , 04:52 PM

Re: bas file does nothing - 07-13-2004 , 05:44 PM

Re: bas file does nothing - 07-14-2004 , 01:50 AM

Re: bas file does nothing - 07-14-2004 , 01:47 PM

Re: bas file does nothing - 07-14-2004 , 02:45 PM