Help With A List Variable

Aug 3, 2005

I have made the following test stored proc that all it does right now
is accept a string variable that will be a list ie. :5,9,6,13. Right
now all the SP is doing is creating a temp table to store the values in
the list, then looping through the list and inserting the values into
the temp table, then selecting all the records from teh temp table.
It's WORKING however i must have done something wrong because it's not
inserting ALL the values. If the list is "5,9,6,13" only 5, 9 and 6
will get inserted, not the 13. if I pass in the list like this
"5,9,6,13," with an extra comma at the end, they all get inserted fine
but thats not how the strings will be coming in. Here's what I have so
far:

CREATE PROCEDURE sp_searchTaskTest

-- in params
@strAssignedTovarchar (200) = NULL

AS


CREATE TABLE #tblAssignedTo (
strAssignedToIDvarchar(10)
)

-- initialize variables
DECLARE @lengthOfString int
DECLARE @startingPosition int
DECLARE @parseString1 int
DECLARE @strAssignedToID varchar(10)


SET @startingPosition = 0
SELECT @parseString1 = CHARINDEX (',', @strAssignedTo,1)
WHILE ( @parseString1 > 0 )
BEGIN
SELECT @parseString1 = CHARINDEX (',',
@strAssignedTo,@startingPosition)
SET @lengthOfString = @parseString1 - @startingPosition
IF @lengthOfString > 0
BEGIN
SET @strAssignedToID = SUBSTRING(@strAssignedTo, @startingPosition,
@lengthOfString)
SET @startingPosition = @parseString1 + 1
END
ELSE
BEGIN
SET @parseString1 = 0
SET @strAssignedToID = ''
END
IF @strAssignedToID != ''
BEGIN
INSERT #tblAssignedTo(strAssignedToID)
VALUES(@strAssignedToID)
END
END

SELECT * FROM #tblAssignedTo
GO

View 2 Replies


ADVERTISEMENT

ERROR: A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List.

May 22, 2006

Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.

From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"

Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)

I "sometimes" get the following error:

OnError,I4,TESTDOMAdministrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Help would be appreciated!

I have seen other posts on this but, not able to relate the solution to my scenario.

View 9 Replies View Related

A Variable May Only Be Added Once To Either The Read Lock List Or The Write Lock List

May 10, 2006

Hi All,



I have seen a few other people have this error.

Package works fine when run from BIDS, DTExec, dtexecui. When I schedule it, It get these random errors. (See below)

The main culprit is a variable called "RecordsetFileDIR" which is set using an expression. (@[User::_ROOT] + "RecordSets\")

A number of other variables use this as part of their expression and as they all fail, pretty much everything dies.

I have installed SP1 (Not Beta) on server. Package uses config files to set the value of _ROOT.



The error does not always seem to be with this particular variable though. Always a variable that uses an expression but errors are random. Also, It will run 3 out of 10 times without a problem. I am the only person on the server at the time.

Any ideas?



Cheers,

Crispin



Error log:

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073659822,0x,The variable "User::RecordsetFileDIR" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073639420,0x,The expression for variable "rsHeaderFile" failed evaluation. There was an error in the expression.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636247,0x,Accessing variable "User::rsHeaderFile" failed with error code 0xC00470EA.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1071636390,0x,The file name is not properly specified. Supply the path and name to the raw file either directly in the FileName property or by specifying a variable in the FileNameVariable property.

OnError,,,DF_Header_Header,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,Move All Data,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,Load Open Batches and Process Files,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

OnError,,,POSBasketImport,,,10/05/2006 12:03:34,10/05/2006 12:03:34,-1073450901,0x,"component "rsHeader" (365)" failed validation and returned validation status "VS_ISBROKEN".

View 1 Replies View Related

IDIOT NEEDS HELP Comparing A Variable Against A List

Feb 14, 2007

I have a variable called @ORComm which has been selected using a cursor from each line on an order.

DECLARE TC2 CURSOR FOR
SELECT [Commodity],[Total] FROM [CSITSS].[dbo].[Ordrate] WHERE [OrderNumber]= @OrdNum AND [Companydiv] = 'GLPC-TRANS'
OPEN TC2
FETCH NEXT FROM TC2 INTO @ORComm, @ORTotal

I need to compare the resulting @ORComm against a list of valid commodity types selectable by

SELECT [CommodityClass] FROM [CSITSS].[dbo].[Comclass] WHERE [CompanyDiv] = 'GLPC-TRANS' AND [DELETED] = 0

What's the easiest way to do this?

View 2 Replies View Related

Can Store A List Of Values In A Variable?

Apr 16, 2014

I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.

I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.

View 7 Replies View Related

Sql Command From Variable, List Empty

Apr 7, 2008

Hello.

I created a package scope variable to use as a sql command for a data
flow task. But when I click the drop down to select it, nothing
appears. Anybody else have this issue and find a solution?


Thanks,
Rich

View 2 Replies View Related

Use Variable As IN List For OleDB Command

Sep 27, 2007

Group,

First let me say that I'm new to SSIS, so be gentle with me.

I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this.

Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist)

As you can see, this is an impossible query on many levels.

My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter.

My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed.

Any ideas would be appreciated.


RH

View 11 Replies View Related

Using A Variable In PIVOT Value List As Column Heading

Sep 20, 2007

Hi All,
 I have never used PIVOT before but looks exactly what I want for this scenario:
I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date.....  I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like:
DECLARE @todayDate varchar(255),
DECLARE @todayPlusOne varchar(255),
DECLARE @todayPlusTwo  varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express.
Thanks in advance.
Lee
 

View 5 Replies View Related

The Variable System::LocaleID Is Already On The Read List.

Sep 17, 2007

I've been getting the error below inconsistently when kicking SSIS packages off from a custom windows service. This same service works fine on our development and integration servers but not on our production servers. When I run the packages manually, it works great. Can someone provide any insight as to what is going on?

Error:

The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.


I have tired the soultions found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=211081&SiteID=1 and applied the hotfix http://support.microsoft.com/kb/918091

I'm running SQL Server 2005 on Windows Server 2003 R2 SP2.


Thanks in advance

View 19 Replies View Related

SQL 2012 :: List All Different Values That Go With Single CAS ID To Appear As Comma Separate List

Jun 15, 2015

So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function

So the output would look like :

CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]

SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0

GROUP BY
preop.Case_ID

View 8 Replies View Related

Report Designer: Need To List Fields From Multiple Result Rows As Comma Seperated List (like A JOIN On Parameters)

Apr 9, 2008



I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.

However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).

For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).

Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac


How can I accomplish this?

I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.

Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?

View 21 Replies View Related

Insert Value List Doest Not Match Column List

Apr 18, 2007

HI...



I need to do a simple task but it's difficult to a newbie on ssis..



i have two tables...



first one has an identity column and the second has fk to the first...



to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!



i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"



here is the script



INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)



what's the problem ??

View 7 Replies View Related

Items In List A That Don't Appear In List B (was Simple Query...I Think)

Jan 20, 2005

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true


In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?

View 3 Replies View Related

Select List Contains More Items Than Insert List

Mar 21, 2008

I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?

Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,

CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,

tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)

View 2 Replies View Related

Insert Only New Items From A List But Get ID's For New And Existing In The List.

Feb 12, 2008

Hi,

I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.

Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'

My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.

performance is a not that great so I'm thinking that my code can be improved.

SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)

TIA





Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO

CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)

--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token

INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0

return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO




View 5 Replies View Related

SSIS Script Task Alters Package Variable, But Variable Does Not Change.

Oct 25, 2006

I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.

I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?

View 1 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

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

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.

Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)

It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.

Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!

Here is the code of the function in the book:

/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0

-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO

View 1 Replies View Related

Scripting: Dumb Q.. How Can I Store A DTS Variable Inside A Script Variable?

Nov 1, 2005

Hi

View 7 Replies View Related

Debug Error - Object Variable Or With Block Variable Not Set -

Feb 15, 2006

I keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is.  I have checked and have no  NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code  Message="Object variable or With block variable not set."  Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()

View 2 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

CREATE TABLE #T(branchnumber VARCHAR(4000))

insert into #t(branchnumber) values (005)
insert into #t(branchnumber) values (090)
insert into #t(branchnumber) values (115)
insert into #t(branchnumber) values (210)
insert into #t(branchnumber) values (216)

[code]....

I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.

View 6 Replies View Related

How To Match The Date Variable And Character Variable

Oct 9, 2001

Hello,
I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check.
The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001.
The field s-date as varchar format. It has the value 2001-10-09.
How can use the where condition ( Select ...... WHERE p-date = s-date.)

Thanks

View 1 Replies View Related

Object Variable Or With Block Variable Not Set (was Frank)

Mar 7, 2005

When trying to upsize an access database to sql server using the upsize wizard, I get the following error:

"Object variable or With block variable not set."

Any assistance is greatly appreciated.

View 3 Replies View Related

Variable Indirection: Choosing Variable At Runtime

Oct 18, 2007

Hello!
I'm using SQL Server 2000.
I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:

DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)

SELECT @operation='DIS'

CREATE table #myTable(value VARCHAR(20))
INSERT into #myTable values('@operation')

SELECT top 1 @parameterValue = value from #myTable
-- Now @parameterValue is assigned the string '@operation'
-- Here I need some way to retrieve the value of the @operation variable I declared before (in fact
-- another process writes into the table I retrieved the value from), in this case 'DIS'

DROP TABLE #myTable



I've tried several ways, but didn't succeed yet!
Please tell me there's a way to solve my problem!!!

Thank you very much in advance!

View 7 Replies View Related

Integration Services :: Variable Value Does Not Fit Variable Type

May 27, 2015

I have an SSIS package that creates a csv file based on a execute sql task.

The sql is incredibly simple select singlecolumn from table.

In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:

The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.

Variables may not change type during execution. Variable types are strict, except for variables of type Object.

If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput

View 8 Replies View Related

Object Variable Or With Block Variable Not Set

Aug 17, 2004

While I was processing the cubes, error "Object Variable Or With Block Variable Not Set" prompt out,
what does it mean ?

Please help !!!

View 1 Replies View Related

Foreach From Variable Using 2 Dim Array Variable

May 15, 2007



Is there any way to use a 2 dimensional array of strings as the Variable Enumerator for the "Foreach From Variable Enumerator". I am trying to copy a collection of files from folder A to folder B. In a script, I would populate, let us say, an array of (2,10), for 10 files, with one column representing the source file and other column representing the target column task. Then I would like to set this string array variable as the "Variable Enumerator" for the "Foreach From Variable Enumerator" and use file system tasks in the foreach loop to perform the tasks. The problem is that the "Foreach From Variable Enumerator" does not let me choose an index, but passes only one index 0, so, I will only be able to pass just one column. How do I let the foreach enumerator let me choose an index. The other foreach enumerators, foreach item and ADO give me the option to select index. I would like the same functionality in the foreach variable.

Note: I cannot use the "For Each File" enumerator, since the files are to be selected by a script only.
Thanks for the help.

View 3 Replies View Related

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

Variable Inside A Variable From Sql TAsk

Sep 28, 2006

I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:

"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""
As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.

[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Where am I wrong?

TIA

View 12 Replies View Related

SQL Variable And IS Variable In Execute SQL Task

Jan 3, 2007

Hi,

 

I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:

 

DECLARE @my_variable int

, <several_others>

SET @my_variable = ?

<do_some_stuff>

 

Of course, I also set up the parameter mapping.

However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.

I have several reasons for wanting to do this:

- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.

- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.

- Passing the variable once also makes it easier to design the script outside IS using Management Studio.

- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.

- I prefer to use the OLE DB connection manager because it's a de facto standard here.

 

Could anyone help me out with the following questions:

- Is the above possible?

- If so, how?

- If not, why not?

- If not, what would be the best way around this problem?

 

Thanx in advance,

Pipo

View 6 Replies View Related

Set A System Variable To User Variable

May 21, 2007

How can I inside a DFT set a System variable, for example "TaskName" to an own created User Variable?



The reason is that I need to use this variable later in the Control Flow.



Regards



Riccardo

View 10 Replies View Related

Passing Variable Value Through Environment Variable

Feb 15, 2008



Hi All!

I have a parent package that contains two children... The second child depends on the succes of the first child.

THe first child generates a variable value and stores it in an Environment variable ( Visibility - All ) ...After the first succeeds, the second will start executing and will pick up the variable value from environment variable( through package configuration setting )...

Unfortunately, this doesn't work...As the second child picks the stale value of the environment variables...Essentially it assigns variable value not after the first child is finished, but right at the beginning of parent execution...

I tried to execute coth children as Out Of Proc as well as In Proc...The same

Would anybody have an idea how to resolve this problem?

Thanks in advance for any help!

Vladimir

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved