Post reply

DTS package

  • February 15, 2005 at 9:48 pm

    #85471

    Hi all

    I want to create a DTS package that runs on a stored procedure which accepts parameters. This is important to avoid any hard coding of values in stored procedure.

    Question 1. Is it possible to prompt the user for parameter values when running DTS package? Currently, I am using Execute SQL Task and Global variables to do it. Is there an alternative?

    Question 2. My DTS package runs for a varchar type of parameter but gives an error when passing an int type variable to the stored procedure. How can I solve this?

    Question 3. The DTS package gives a message of successful execute and records no errors in the logs, But it does not perform the transfer of data. What could be wrong?

    This is the stored procedure I am using:-

    CREATE PROCEDURE proc1 @v1 varchar(50)

    AS

    INSERT INTO destiTable SELECT * FROM sourceTable WHERE column1 = @v1

    Thanks everyone!!

  • February 15, 2005 at 10:47 pm

    #542443

    1) Yes but it's not too optimal. A better solution is to have a end-user application that validates the values and then passes them to the package as global variables, or stores them in a table where the package reads them from.

    2) The stored procedure you posted expects a varchar parameter, this would infer that you're comparing against a varchar column? Where does the int variable come into the process?

    3) Do you have DTS Package Logging enabled? If so, you'll find messages there. Also, if you have a datapump task you can specify a seperate exception file on the Options tab of the Datapump Properties dialog.

     

    --------------------
    Colt 45 - the original point and click interface

  • February 16, 2005 at 9:30 am

    #542577

    Thanks a lot for your help.

    As for the int variable, I am using

    CREATE PROCEDURE proc1 @v1 int

    AS

    INSERT INTO destTable SELECT * FROM ssourceTable WHERE col2 < @v1

    I checked the log. It gives the following error:-

    "The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80040e21): Invalid character value for cast specification.)"

    Secondly, another DTS package similar to the above procedure gives a message of successful execution, obviously recording no errors in the log file, and yet sourceTable is empty. That's bewitching!!

    Any ideas? Thankyou.

  • February 16, 2005 at 1:37 pm

    #542637

    So is the error in the same package?? If you pass a character value to a procedure that's expecting an int then yes you will get that error.

    Are you sure your stored procedures work?

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply