Mapping Output Parameter To A Variable!
Apr 25, 2007
Hi there,
I am working on SSIS package that gets data from SQL 2005 Database and writes that to a flat file. But I need to write the count of records as part of the header.
Here is what i am trying:
The OLE DB Source is calling a stored procedure and returning two things i.e. a resultset and an output parameter. The data access mode is SQL Command.
Code SnippetEXEC [Get_logins] ?, ?, ? OUTPUT
In the Set Query Parameters dialogbox, all the three patameters are mapped to three different user variables.
What is happening is that the user variable that is mapped to output parameter is never updated. The header property expression is written as follows
Code SnippetRIGHT("0000000000" + (DT_STR, 10, 1252)@LoginCount, 10)
I tried to watch the variable in watch window but to no avail. Any guidance if it is bug or I am missing some thing? Any thoughts, how can I accomplish this? I have also tried adding Row Count Transformation but its variable has the same behaviour. If I set the value of @LoginCount variable to some value, this initially set value is successfully written to the file header.
Thanks
Paraclete
View 4 Replies
ADVERTISEMENT
Jan 18, 2006
Is there any benefit to using Output parameters:eg.[query set-up and execution goes here]
View 2 Replies
View Related
Mar 19, 2008
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Any thoughts!
View 5 Replies
View Related
Mar 16, 2006
I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', ?, NULL, 1, ?, ?, ?, NULL, ? OUTPUT;
Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = ?, @Started = NULL, @Status = 1, @FileType = ?, @FileName = ?, @FilePath = ?, @Description = NULL, @ItemId = ? OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong?
Thanks.
View 4 Replies
View Related
Oct 2, 2007
Hello,
I am struggling with this, having read many msdn articles and posts I am non the wiser. I have a sproc with an output parameter @regemail. I can call the sproc OK from my code as below, via a tableadapter. And the sproc works as desired from management studio express. I want to get the result returned in the OUTPUT parameter (NOT the Return Value) and place it in a variable. Can anyone advise how I can do this? J.
THE CODE I USE TO CALL THE SPROC
Dim tableAdapter As New DataSet1TableAdapters.RegistrationTableAdaptertableAdapter.SPVerifyUser(strRegGuid, String.Empty)
THE STORED PROCEDURECREATE Proc [prs_VerifyUser
@regid uniqueidentifier,@regemail nvarchar(250) OUTPUT
ASBEGIN
IF EXISTS(SELECT RegEmail from dbo.Registration WHERE RegID = @regid)
BEGIN
SELECT @regemail = RegEmail FROM Registration WHERE RegID = @regid
Return 1
END
Return 2
END
View 4 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Aug 23, 2006
I have a For Each Loop that iterates over a recordset stored in a variable. One of the columns in the recordset is type xml and I want to map it to a variable using Variable Mappings of the For Each Loop container. I am getting this error:
Error: 0xC001C012 at FELC Loop thru report defs: ForEach Variable Mapping number 4 to variable "User::Parameters_xml" cannot be applied.
I have tried changing the type of the Parameters_xml variable to Object and String, but I get the same error. Any ideas?
View 3 Replies
View Related
Jun 20, 2007
I can€™t figure out how to map xml data stored in a table to a variable in integration service.
For example:
I would like to use a €œfor each loop container€? to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like €œvariable mapping number X to variable XXX can€™t apply€?.
Any help?
View 1 Replies
View Related
Oct 18, 2006
Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:
SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable
but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.
thanks a lot for your help!
View 3 Replies
View Related
Nov 17, 2006
I'm just getting started with SSIS and have a couple basic questions. I have a Foreach Loop that iterates over a collection of database names. The collection must include only database names that match a substring. I haven't found a way to filter the SMO Enumerator collection so, instead, I feed the resultset of an Execute SQL Task into the Foreach Variable Enumerator.
Of course this variable (oDBnames) is object type so in Foreach Variable Mapping a string variable (sDBname) is mapped to the only value in the collection (database name). Now sDBname should be available for an Execute SQL Task inside the container. But I keep getting the following errors:
Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::sDBname" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::sDBname" cannot be applied.
Can someone explain what is going on and how to correct this? I haven't found an answer in BOL or MSDN yet. Thanks.
Another beginner question: The results/messages from a query run in Management Studio are displayed in the results/messages panes. But how would you save and view query output when using the Execute SQL Task? Seems like I should be able to dump Full result set into an object variable and display that, but everything I try raises an error ...
View 3 Replies
View Related
Jan 29, 2008
Hi
I have migrated a DTS 2000 package to an SSIS package.
Half of it works fine, when stored procedures are called that don't use parameters they work fine on SSIS.
However when a SP is called with a parameter it can't find the parameter name ?
I have mapped the parameter under "parameter mappings" , the parameter is a simple date value
which is created at the beginning of the SSIS package in a SQL task.
it is saved as a global variable , and i have mapped this as the parameter , yet it still can't find the param. name ?
any sugestions ?
View 1 Replies
View Related
Oct 5, 2015
Why the index value is used in variable mapping while using for each loop container. In one of ssis package I saw the index value as 2. What 2 indicates in index?
View 3 Replies
View Related
Apr 17, 2006
Hi,
I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.
" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "
" Error: The type of the value being assigned to variable "User::Value" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. "
Pls anyone have a look and give me a solution asap.
Thanks & Regards,
Prakash Srinivasan.
View 4 Replies
View Related
Mar 19, 2008
Hi pals,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Is there any way to see/look the sql statement which is formed after Parameter Subsititution inside the log file?
Can we print the SQL Statement Formed by the Execute SQL task inside a script task ?
Any help would be greatly appreciated !
Thanks in advance
View 1 Replies
View Related
Apr 17, 2008
I have 2 questions on this
(1) I know how to use the ? ? ? and 0, 1, 2 notation in Parameter Mapping within Execute SQL Task. However, the interface allows me to give descriptive names to my parameters (other than the ordinals 0, 1, 2, ...). To be more clear, if you go into Parameter Mapping and click in Parameter Name column, you are not just restricted to typing in 0, 1, 2, ... You can type anything you want for the name. Does this suggest that I can use other things besides a "?" in my SQL command?
(2) What is Parameter Size? Is this like a data type? If so, why am I allowed to type in anything I want in there?
View 3 Replies
View Related
Dec 13, 2007
Hi All,
I am using a stored procedure defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetPriority] @PriorityID TINYINT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [Priority]
FROM [MTD Dashboard].[dbo].[Priority] WHERE [Priority ID]=@PriorityID
END
I want to use this stored procedure in a Execute SQL Task. What should be the SQL Statement, Parameter mappings and Result Set?
Can someone please help me in doing this.
Thanks
View 5 Replies
View Related
Jun 12, 2007
Hi,
I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.
The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?
Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.
Thanks in advance
View 6 Replies
View Related
Mar 9, 2006
I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:
if exists
(
select name
from sys.databases
where name = ?
)
begin
drop database ?;
end;
go
create database ?;
go
This is the error I am getting:
[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.
Regards,
DO
View 13 Replies
View Related
Jul 25, 2006
Hi:
I am trying to loop through 4 files in a folder and read the names of those files through the For each loop container task. I have 4 readme files (readme1.txt thru readme4.txt) in a folder called C:SSIS.
I have added a for each Loop container and a script task to my package. In the Variable Mapping page I have named the Variable and configured the index to be 0. The problem is when I execute the package the file name that is read is always readme1.txt. I want all the file names to be read under the folder ( in other words configure the index to be 0 thru 4). How do I configure the index so that it can read all the files?.
The following is the code I have in my script task:
Public Sub Main()
Dim variables As Variables
If Dts.Variables.Contains("FileName") = True Then
Dts.VariableDispenser.LockOneforRead("FileName", variables)
End if
Msgbox ("Retrieved the File " & CStr(variables("FileName").Value))
Dts.TaskResult=Dts.Results.Success
End Sub
When I execute the package with the above code, I always see a message box popping up and saying Retrived File Readme1.txt.
I want the msgbox to pop up and say
Retrieved Readme1.txt
Retrieved Readme2.txt
Retrieved Readme3.txt and so forth...
Any help on achieving this is appreciated.
Thanks
AK
View 1 Replies
View Related
Jul 23, 2007
Hi all,
I'm working with Execute SQL task. Connection type: OLE DB. With the following settings, the task works fine:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::InputFile Input NVARCHAR 0
User::DesiredOutput Input NVARCHAR 1
SQLStatement:
Code Snippet
exec [spu_CreateOutput] ?, ?
However, I want to put in some conditions so I modified the task as:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::OutputFile Input NVARCHAR 0
User::InputFile Input NVARCHAR 1
User::DesiredOutput Input NVARCHAR 2
SQLStatement:
Code Snippet
if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
But the modification doesn't work. The following message was thrown:
Error: 0xC002F210 at CREATE OUTPUT, Execute SQL Task: Executing the query "if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: CREATE OUTPUT
Can anyone tell me what I have been wrong with it? It seems that parameter mapping can only apply for a single select statement/function/procedure call :-?
Thank you,
GiaHi
View 5 Replies
View Related
Jul 12, 2006
Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.
The input parameter is define as @Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:
SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging
update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DCLoading.dtsx" finished: Failure.
Any help?
View 6 Replies
View Related
Dec 17, 2007
Hi ALL,
I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.
The stored procedure is defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[SetSLATimePriority]
AS
BEGIN
DECLARE @PriorityID tinyint,@MAXPriorityID tinyint
DECLARE @Priority NVARCHAR(MAX), @SLATime int
SET @PriorityID=1
SET @MAXPriorityID=0
SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]
SET NOCOUNT ON;
WHILE @PriorityID<=@MAXPriorityID
BEGIN
SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]
SET [SLA Time] = @SLATime WHERE [Priority] like @Priority
SET @PriorityID=@PriorityID+1
END
END
The Properties of Execute SQL Task are set as follows:
Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False
Parameter Mapping:
Variable Name : User::IntValue
Direction: ReturnValue
Data Type: Long
ParameterName: 0
I am getting the following error, when I run this package.
[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am not able to figure out, where exactly the problem is.. Can some one please help me out?
View 3 Replies
View Related
Feb 12, 2007
Hi,
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
Please help.
Kumar
View 3 Replies
View Related
Apr 17, 2008
After running my ssis pkg for some time with no problems I saw the following error come up, probably during execution of a stored procedure ...
An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The formal parameter "@ReportingId" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.".
I see some references to this on the web (even this one) but so far they seem like deadends. Doe anybody know what this means? Below is a summary of how the variable is used in the stored proc.
The sp is declared with 21 input params only, none of them is @ReportingId. It declares @ReportingId as a bigint variable and uses it in the beginning of the proc as follows...
Code Snippet
select @ReportingId = max(ReportingId)
from [dbo].[GuidToReportingid]
where Guid = @UniqueIdentifier and
EffectiveEndDate is null
if @ReportingId is null
BEGIN
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
END
ELSE
BEGIN
select @rowcount = count(*) from [dbo].[someTable]
where ReportingId = @ReportingId and...lots of other conditions
...later as part of an else it does the following...
Code Snippet
if @rowcount > 0 and @joinsMatch = 1
begin
set @insertFlag = 0
end
else
begin
update [dbo].[GuidToReportingId]
set EffectiveEndDate = getdate()
where ReportingId = @ReportingId
insert into [dbo].[GuidToReportingId]
select @UniqueIdentifier,getdate(),null,getdate()
set @ReportingId = scope_identity()
end
...and before the return it's value is inserted to different tables.
View 5 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
May 27, 1999
hey folks...
i am new to store procedures, crystal and sql server... ..one heck of a combination..yikes....neways.... writing a stored procedure in 6.5 to run a crystal report 7.0... want to create an output variable in the procedure that the report will see as a field... can i do this...and if so how....
any and all help muchos gracious.... the higher ups just don't get why i can't do all in 10 days of learning sp's on my own.... egads... management
:-)
take care.....kim
View 2 Replies
View Related
Mar 26, 2008
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .
alter proc r (@id INT)
as
BEGIN
DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
SET @input = 'db2,oracle,sybase'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input
select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in
select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end
Print @list
EXECUTE ('SELECT Label FROM systemtype WHERE Label Not IN (' + @list + ')')
END
my actual task is like this
My input is a list of values seperated by commas
now my output should be list of values not in the table joined by comma
eg : if my table consists of list of all databases like
db2
oracle,
sybase,
mssql,
mysql,
myinput would be like this db2,sybase,oracle
my output should be mssql,mysql
how to get that
?
Any suggestions
View 2 Replies
View Related
Mar 26, 2008
here's my code i want my output to be stored in a variable or out statement .Any suggestions on the below procedure
alter proc r (@id INT)
as
BEGIN
DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @inVARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input
select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in
select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end
PRINT @list
END
View 7 Replies
View Related
Jul 23, 2005
I know this has been dealt with a lot, but I would still reallyappreciate help. Thanks.I am trying to transform this tableYY--ID-Code-R1-R2-R3-R4...R402004-1-101--1--2-3-42004-2-101--2--3-4-2....2005-99-103-4-3-2-1Into a table where the new columns are the count for 4-3-2-1 for everydistinct code in the first table based on year. I will get the yearfrom the user-end(Access). I will then create my report based on theinfo in the new table. Here's what I've tried so far (only for 1stcolumn):CREATE PROCEDURE comptabilisationDYN@colonne varchar(3) '*receives R1, then R2, loop is in vba access*ASDECLARE @SQLStatement varchar(8000)DECLARE @TotalNum4 intDECLARE @TotalNum3 intDECLARE @TotalNum2 intDECLARE @TotalNum1 intSELECT SQLStatement = 'SELECT COUNT(*) FROMdbo.Tbl_Réponses_Étudiants WHERE' + @colonne + '=4 AND YY = @year'EXEC sp_executesql @SQLStatement, N'@TotalNum4 int OUTPUT', @TotalNum4OUTPUT INSERT INTO Comptabilisation(Total4) VALUES (@TotalNum4)GO
View 6 Replies
View Related
Mar 26, 2008
This is my stored procedure .It is working fine .I want to capture the output in a print statement .Can anyone help me please .
alter proc r (@id INT)
as
BEGIN
DECLARE @input VARCHAR(800)
DECLARE @c_input INT
DECLARE @i_Input INT
DECLARE @input_left VARCHAR(800)
DECLARE @delimiter CHAR(1)
select @delimiter = ','
DECLARE @in VARCHAR(800)
DECLARE @list VARCHAR(800)
declare @list2 VARCHAR(800)
SET @input = 'AWCA,GCS,IHP,Aetna'
select @c_input = (select dbo.Fx_CharCount(@delimiter,@input))
set @c_input = @c_input + 1
while @c_input > 0
BEGIN
select @i_input = charindex(@Delimiter,@input)
if @i_input != 0
BEGIN
select @input_left = left(@input, @i_input - 1)
END
else
select @input_left = @input
select @in = '''' + @input_left + ''''
select @list = ISNULL(@list + ',', '') + @in
select @input = right(@input ,(len(@input) - @i_input))
SET @c_input = @c_input -1
if @c_input = 0 or @input = @input_left
break
end
Print @list
EXECUTE ('SELECT Label FROM repricingsystemtype WHERE Label Not IN (' + @list + ')')
END
View 5 Replies
View Related
Apr 17, 2008
create procedure usp_test (@AccountID INT)asbegin
DECLARE @getAccountID CURSOR
SET @getAccountID = CURSOR FOR
SELECT Account_ID
FROM Accounts
OPEN @getAccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @AccountID
FETCH NEXT
FROM @getAccountID INTO @AccountID
END
CLOSE @getAccountID
DEALLOCATE @getAccountIDend i get nearly ten rows in the print statement how can i assign the output to a out variable where i get all ten rows.
View 5 Replies
View Related
Feb 20, 2007
Hello,
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
Thanks,
Phil
View 5 Replies
View Related
May 14, 2007
Hi,
I've been looking into using the ability to select the table of an OLEDB destination task based on a package variable but would like to know if this could be utilised in the following example.
I have a CSV file (potentially there are many) with a number of rows. The rows do not necessarily contain the same number of elements. Each row has an EventID and this is used to determine the database table that the row is to be inserted in.
Initially i thought about using a conditional split to send each row down the path relating to its event ID. There would be multiple destinations depending on the table. With the possibility of 60 to 70 different event ID's this is very quickly going to get out of hand in the designer. I'd like to know if it would be possible to set up a case statement (or similar) in a script component and specify the table name as a variable depending on the event ID. I'd then use this table variable to set the destination task's table property.
Does this sound like a possibility or do i have to go down the route of multiple paths?
Many thanks in advance,
Grant
View 3 Replies
View Related
