?
Solved

DTS Script question

Posted on 2006-06-27
2
Medium Priority
?
484 Views
Last Modified: 2013-11-30
Hi.   I'm new to SQL Server 2000 and DTS.    Currently, I'm trying to rebuild a DTS script package from a previous project, but I did not create it at that time so I'm suffering through some issues in my attempt to get it working.   The script uses a few SQL Tasks, VB ActiveX Scripts, and one Bulk Insert Task.


I continually get Package Execution Results:   " 1 task(s) failed during execution " message and then when I double-click to find out why error occurred it tells me:

[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

Questions:

1)    I have one Global Variable named "DateProc" set in the beginning ActiveX script as follows:

                DTSGlobalVariables("DateProc").Value = InputBox("Enter date [ mm/dd/yyyy ] to be loaded:")

      ' CHECK IF IT IS A VALID DATE
      dt = CDate(DTSGlobalVariables("DateProc").Value )
      Main = DTSTaskExecResult_Success

Then, at the end of package, on success of a Bulk Insert Task i then call a Stored Proc named:      SPTrades ?     When i click "parameters" button it tells me that "The SQL statement does not contain any parameters", but when I go to the stored proc's definition it begins like this:      CREATE PROCEDURE dbo.SPTrades(@dt datetime)

Can someone tell me the relationship(s) between the VB ActiveX Script variable 'dt', the global variable 'DateProc' and the ? that is used in the SQL Task's properties SQL statement window to execute the task?
Why does it tell me the SQL statement does not contain any parameters?    isn't the parameter =  ?

2)   The last task ( dbo.SPTrades ? ) continues to fail during execution,  and the error occurred =  [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error.     How can i resolve this error?   Do the number of columns in the table that receives the INSERT have to exactly match (by type and number of columns) the number of items I'm trying to insert?

3)   How does the stored proc dbo.Trades access the Global 'DateProc' variable that is set in the first script shown above?    Does the Stored Proc automatically gain access to the "DateProc" variable?



Please help.    

NOTE:    When I change the Connection in the DTS  from  [Microsoft ODBC Provider for SQL Server]  to   [Microsoft OLE DB Provider for SQL Server],  then the error I receive on the last item (  dbo.SPTrades ? ) of the DTS is   "No value given for one or more required parameters"





0
Comment
Question by:lblinc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 

Author Comment

by:lblinc
ID: 16992830
Btw  ~  I think I was using the wrong connection..    now that I changed to OLE DB Connection i see how to set the ? parameter to the Global Variable  "DateProc"
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 16997419
Your biggest help will be this site:

www.sqldts.com


A very handy site for DTS!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question