Execute SQL task editor problem

hi !

I have problem here with sql task editor. i have a stored proceedure with 1 input value and 1 output value which needs to be updated in a variable.

stored proc - exec GetRDate ? , ? output

input value is set in a varaible.

I tried giving 1 input and 1 output in the parameter box and result set as fullresultset 1 value for variable -

" failed with the following error: "No disconnected record set is available for the specified SQL statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Get Retention Date

what should i give in the parameter and result set

Thanks,

Jasmine

Check out "The ExecuteSQL Task" explained @. http://www.sqlis.com/default.aspx?58
Lots of different examples are discussed.

Also check out the sections 'Using Parameters with Stored Procedures'', 'Getting Values of Return Codes', 'Specifying a Result Set Type', 'Populating a Variable with a Result Set' @. http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Thanks,
Loonysan

Execute SQL Task Editor - Variables

Hi,

Im trying to do an Insert:

INSERT INTO myTable(column1)

VALUES(..)

How can I use my parameter mapping value inside the SQL statement ablove?

Thank you.

Use ? where you want to use the parameters.

INSERT INTO myTable(column1)

VALUES (?)

|||

My SQL Task looks as follows:

Parameter Mapping:

User::ID Direction: Input Data Type: VarChar ParameterName: ID

SQL SourceType:

Direct Input

INSERT INTO Table1(id, name)
VALUES (1,'john')

How can I use my Parameter Mapping value inside the SQL Insert statement (instead of the 1 I put above)?

Thank you.

|||Try using a parameterName of 0 (zero)

Then:
INSERT INTO Table1 (id, name) VALUES (?,'john')|||

It worked!!

Thanks a lot!!

Execute Package Task using SSIS Package Store package

From the Execute Package Task Editor, what info do you have to enter to
reference a SSIS Package Store package?
For the Location, I've tried both "SQL Server" and "File" and nothing
works except selecting the actual location from the "File" option.
For the Connection, I've tried the default "Native OLE DB\..." and "..OLE DB
Provider for DTS.." as Provider with no result.
For PackageName, I've tried various combination "\File System\PackageName",
"PackageName", etc... and nothing works.

I'm assuming this task allow for the 3 different ways to store the package (SQL Server/MSDB, File System, Package Store (hybrid)).

Any ideas!!!?

The documentation for the task here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/042d4ec0-0668-401c-bb3a-a25fe2602eac.htm

says that the package can originate only from the File System or MSDB.

You could however use DTExec to fire the package with the /D[ts] package_path
parameter
Allan|||Remember as well that all the package store is the only the logical container for MSDB and the folder location specified in MsDtsSrvr.ini. The thing about it is that the service is aware of this location.
Allan|||The problem with the Package Store is that it is both or "managed" File System. I think someone has submitted a bug report on my behalf for this. Thanks.

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.

Execute DTS 2000 Package Task Editor (Inner Variables vs Outer Variables)

Hi,

I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.

Jo?o

Can anyone Help me? Any help is welcome. Thanks a lot!

Jo?o

|||

Hi,

Were you able to pass the variables from SSIS to DTS package (called from SSIS using Execute DTS 2000 package task) and back to the parent package (SSIS) global variables?

Thanks in advance,

Subah.

|||

The outer variables are a means of communication from the SSIS package to the DTS package it executes using late binding (the outer variable is evaluated before the DTS package is executed and not earlier).

We map the outer variables from the SSIS package as global variables to the DTS package. E.g. if you add an outer variable called User::mystring variable to the list, we strip the namespace (because DTS does not know about namespaces) and pass it as a global variable named mystring to the DTS package.

|||Thanks, that helped my understanding a lot. I was able to implement the same.