For Loop * Sql Task Question About Variables

Oct 18, 2007

I want to iterate through a for loop and run some SQL update statements:

I have a variable wk_day that has a scope of the container

I want it to run this code:

update daily_sls_curr set mtd_ty_sls = (
select sum(ty_sls) from daily_sls_curr ci
where ci.fmonth = (select max(fmonth) from daily_sls_curr)
and wk_day <=@wk_day
and ci.store = daily_sls_curr.store
group by store)
where fmonth = (select max(fmonth) from daily_sls_curr)
and wk_day = @wk_day

repeatedly from the loop for 35 days of the fiscal month

When I parse the query, it gripes at the variable

How do I set up the query to work with the variable?

Thanks
JPS

View 7 Replies


ADVERTISEMENT

Send Mail Task Problem Using A Combination Of ForEach Loop, Recordset Destination, Execute SQL Task And Script Task

Jun 21, 2007

OK. I give up and need help. Hopefully it's something minor ...



I have a dataflow which returns email addresses to a recordset.

I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.



I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.



I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).



The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.



Try

ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)



My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.



part number leadtime

x 5

y 9

....



Does anyone have any idea what I might be doing wrong?

thanks

John

View 5 Replies View Related

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

Sep 4, 2006

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





View 8 Replies View Related

File Watcher Task (Konessans) Vs For Each Loop Task

Dec 6, 2007



HI, I need to trigger some packages upon existance of specific files in a particular directory. Sound lkike the file watcher task (from SQLIS) would do the job but I am wondering what is the difference of using this tool instead of a for each loop container. I mean, If a file exists in a directory, the for each loop container will detect it. Since the file watcher is not a service, the package containing it needs to ne scheduled on a regular basis for the filewatcher to detect the file, right? So, a for each loop container would do the job? So, waht wouldbe the advantage of using the file watcher task?

Thank you,
Ccote

View 11 Replies View Related

Variables In Data Flow In For Each Loop

Sep 8, 2007

how to pass the variables as the data source in data flow in for each loop( ADO enumerator)?

View 1 Replies View Related

Generate Result Using Nested Loop And Variables

Dec 3, 2013

How can I generate the following result using nested loop and variables :

col1col2
15
16
17
25
26
27
35
36
37
45
46
47

View 5 Replies View Related

Logging The Foreach Loop Container's Variables

Apr 23, 2008

Hello

I would like to loop through a folder and then log the folder's content, as in the names of the files, into a database like all the events get logged. Here is what i have done. I put some files into the folder c: emp made a loop through it, set up a variable 'temp' scope is the foreach loop, set the variable raisechangedevent to true. I have also confgigured the loop to put the result of the looping, in the temp variable in the variable mappings tab.

Strangely nothing is recorded into the "execution results" when i test run the package ? Do i need to make a event handler for the onvariablevaluechanged event for the foreach loop container to be able to get the value of the variable logged or is there something other that i'm missing ?

View 5 Replies View Related

Programmatically Loop Over Variables In The Variable Dispenser?

Jun 16, 2006

In my custom task, I would like to loop over the variables in the variable dispenser, and only modify those that are of a certain type. Is this possible?

Thanks!

View 8 Replies View Related

Integration Services :: SSIS Foreach Loop Container Passing Variables To Store Procedure

Sep 8, 2015

I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure.  I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container.  The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure  ?" within the "Execute SQL Task".  The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one.  I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task".  How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?

View 4 Replies View Related

Execute Sql Task Into A ADO Loop

Jul 10, 2007

Hi all,

I got this problem and before post I've read many post similar in the forum (sorry for my english I try to do my best....i'm italian)

This is my scenario.

1) SQL

SELECT

UPPER(SUBSTRING(FileImportazione, 7, 50)) AS str,

Id

FROM Sources

WHERE (TipoImportazione = 0)



This is an SQL task and from this I create a Object variable "ObjDs" on scope package1





2) I introduce a loop (Foreach ADO Enumerator) on my object variable "ObjDs".

Here I mapping two variables "IDsources" and "str" (as you caqn see from my first sql)



Now I set a breakpoint into the beginning of my cicle and really see the dinamic change of the values from "watch" windows.



Now start my problem.

Into this loop I must do "X" operation....but first of all I must Update a table...so I simple SQL TASK...on the dinamic ID of my variable.



Ok, I put a SQL tast into the loop, set SQLsourceType = Variabile, resulset = none, and create an expression like this (SQLstatementsource):

"UPDATE New_pangea.dbo.Sources SET InAggiornamento = "+ (DT_WSTR, 4) @[Utente::IDsources]



If I don't cast (DT_WSTR, 4) I got always an error...

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""UPDATE New_pangea.dbo.Sources SET InAggiornamento = " + @[Utente::IDsources]" failed with error code 0xC0047080.

so I put and validate myexpression.



before my global variable @[Utente::IDsources] change the value to true of EvalutateAsExpression.



Something goes always wrong...When I try debug...this is the error

Errore in Execute SQL Task: Failed to lock variable "UPDATE New_pangea.dbo.Sources SET InAggiornamento = 0" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Errore in Execute SQL Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)





Hope someone can help me, thanks Alen

View 2 Replies View Related

Execute SQL Task With In A For Each Loop

Apr 24, 2006

hi guys,

i'm trying to do a bulk insert through a t-sql which accepts 3 params.

i frist run a SQL statement to get a list of DB's to update through a SQL task, then i save the output to a variable object. which is been used by the For each loop.

i've done the mapping with the object fine. but i want to call a SQL task within for each loop, and parse the above results as parameters. i tried parsing it as

exec mysp3 @[User::fundid], @[User::dbCode], @[User::subfund]

where fundiid, dbcode and subfund are user defined variables that gets updated by the ado recordset.


this fails with the error msg:

SSIS package "SP test.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC @[User::fundid], @[User::dbcode], @[User::subfund] failed with the following error: "Incorrect syntax near 'User::fundid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Shred the contents of the variable: 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 "SP test.dtsx" finished: Failure.

i also tried

exec mysp3 ?, ?, ?

then mapping the inputs still doesn't work

same thing with

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec ConnectODBC ?,?,? 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: Execute SQL Task

View 3 Replies View Related

Can't Use Variables In SQL-Task

Jul 25, 2006

Hi,

I have a problem in my SSIS-package: If I try to execute a SQL-statement which uses a variable, it say "syntax-error". Here is how I tried:

1. a) Declared a variable. Name: tableName; Bereich (Sorry, I use the german version, no clue whats that on english versions): Package2; Type: STRING; Value: staticDB.StaticData_provider.dbo.C0123

b) Assigned this variable on SQL-Task->"Parameterzuordnung" as VARCHAR, parametername= NewParameterName

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM @NewParameterName"

d) Running this results in following error: "Der Parametername wird nicht erkannt" Translation: "Parametername was not recognized."

2. a) see 1.a)

b) see 1.b)

c) Used this on my SQL-Statement, SQLSourceType is directinput. Statement: "DELETE
FROM ?"

d) Running this results in following error: "Syntaxfehler, Berechtigungsverstoß oder anderer allgemeiner Fehler" Translation: "Syntaxerror, permision violation or other common error."

Any ideas?

Regards,

Jan

View 3 Replies View Related

SQL Task With Variables

May 4, 2006

I am trying to get the max of a fields in to a user variable

SELECT User::maxnafseq = MAX(nafseq) FROM naf

The error I get is

[Execute SQL Task] Error: Executing the query "SELECT User::maxnafseq = MAX(nafseq) FROM naf" failed with the following error: "SQL: Column 'MAXNAFSEQ' is not found.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



Can someone help me with this?

Thanks



View 1 Replies View Related

ForEach Loop Container And First Task

Nov 8, 2006

Hi,

My Foreach Loop container has 10 different task inside. I want to execute the first task only one time. I have a variable with increases for each repition. How can I put precedence contstraint on the first task so that it should execute only first time and other task has to execute all the time.

Thanks

View 4 Replies View Related

Loop A Package In Script Task?

Jan 29, 2007

In Dts you can loop a package by creating an Active-X script with something like this ...
"oPackage.Steps("DTSStep_1").ExecutionStatus = DTSStepExecStat_Waiting"
"oPackage.Steps("DTSStep_2").ExecutionStatus = DTSStepScriptResult_DontExecuteTask"

What is the way to do in SSIS? Depending on a condition i want to rerun my package but have not found a smooth way to do it. I guess you can do it in a better way than using the ActiveX Script Task

have a nice day
/Erik

View 4 Replies View Related

How Can I Use The Variables In SQL Execute SQL Task?

Aug 24, 2006

I define a package variables "varOutTable" and "varFromTable".

and I insert a SQL Execute SQL Task into Control Flow

my sql command is

"Select * into @[User::varOutTable] from @[User::varFromTable]"

but the task failed,

it seems that sql task can't get the varOutTable and varFromTable

How can I use the Variables in SQL Execute SQL task?



thanks!!

View 14 Replies View Related

Executing SQL Task And Variables

Nov 20, 2007



Hi,

I'm trying to run a statement against all of my servers - I've got about 10 statements working, but am having trouble with this one. It's in a for loop with a connection that is changed by a recordset which is a list of my servers. Here is the code - In the step before, I've created the tempdb.dbo.DBROLES table.




Code Block
declare @dbname as varchar(200)
declare @mSql1 as varchar(8000)
DECLARE DBName_Cursor CURSOR FOR
select '[' + name + ']'
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @mSQL1 = 'Insert into tempdb.dbo.DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '''+ @dbName +''' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @dbNAme+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+ @dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'
--Print @mSql1
Execute (@mSql1)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor;





failed with the following error: "Must declare the variable '@dbName'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Put dbroles into table

The program '[10320] DBA_Repository_Load (1).dtsx: DTS' has exited with code 0 (0x0).


I'm going to try creating the procedure on each server - that will probably work - I just don't get why it's not recognizing that variable. The line it is referring to is FETCH NEXT FROM DBName_Cursor INTO @dbName. Thanks for any info on this!

View 3 Replies View Related

Script Task Variables

Sep 6, 2006

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

 

View 3 Replies View Related

Variables In Script Task

Oct 27, 2006



I recieve the below error when I attempt to execute a Script Component in a Data Flow Task:

The collection of variables locked for read and write access is not available outside of PostExecute.

This is the code I used:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

Output0Buffer.BillerId = CType(ReadWriteVariables("CustId").Value, Int32)

Output0Buffer.UserAccount = CType(Row.Column0, String)

Output0Buffer.UserSubAccount = CType("001", String)

Output0Buffer.PaymentAmount = CType(Row.Column1, Decimal)

Output0Buffer.DueDate = CType(Row.Column2, String)

Output0Buffer.EmailAddress = CType(Row.Column3, String)

Output0Buffer.ImportDateTime = DateTime.Now

Output0Buffer.FileName = CType(ReadWriteVariables("FileName").Value, String)

Output0Buffer.FileCreationDate = CType(ReadWriteVariables("FileCreateDate").Value, String)

End Sub

End Class



I use the variables "CustId,FileName,FileCreateDate" in the ReadWriteVariables collection. Any ideas would be appreciated as to how to get around this error.

View 19 Replies View Related

How To Use A Script To Break Or Continue A For Each Loop Task?

Jun 14, 2006

Okay, I have a For each loop container that has an enumeration of folder locations. I run a script to filter the files in those folders and prepare them to the next task to be copy. I want the script to "continue" the loop if none of the files meet the criteria. Can someone point me into the right direction? I need some way to advance through the loop when this happens.

Thanks!

View 1 Replies View Related

Execute Package Task In A Loop Works, But...

May 3, 2007

Okay, can someone explain why when I execute children packages within a loop that only a couple of them work?



When I first started this I had Child Package 1 and Child Packge 2 working from the Parent Package 1. It would loop through twice (I had 2 organizations in my outer loop) and pass in the Parent Package variables correctly.



I am using a simple "Foreach Item Enumerator" with a collection of string enumerators that are the Names of the children packages - these are assigned to a variable that is scoped to the outer loop.



Now, when I add a new Child Package 3 and set it up the same as the other 2, when it goes to execute Child Package 3 it fails with the error:



"Error: The connection manager "[My Package Name].dtsx" is not found. A component failed to find the connection manager in the Connections collection. "



What in the world have I done? I was under the assumption that since the first 2 packages worked, that any other packages I added would work the same.

View 3 Replies View Related

Help Batch Script Task Stuck In A Loop

Aug 29, 2006



I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this--->ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:UPLOADdoc.txt
bye
exit

Thanks,

Mike

View 6 Replies View Related

Slow Web Service Task In Foreach Loop

May 9, 2008



Hi I have created a web service task that sits inside of a foreach loop. The loop is a ado enumerator. This loop is going to loop through about 35,000 records and make 35,000 web service calls.

So far through my testing, I have gotten it to read and write everything correctly, but each call to the web service takes about 1-2 seconds and maxes out the CPU. This would take almost all day for this web service to run at 100% CPU power all day. We want to be able to run this a few times a day or at least once a day.

I have disabled all of the other tasks in the foreach loop so it just calls the web service task and found that it's the web service task that takes up 100% of the CPU and takes 1-2 seconds to run. I have also tried creating a mock web service on the server that just sends back the same mock data, so it's extremely fast, but the Web Service Task still maxes out the CPU and takes 1-2 seconds per call.

I have tried this out on 2 different servers, all with a few gigs of ram they're not using. It seems more like it's a CPU thing, or that the Web Service Task has a high overhead that each time in the loop it takes 1-2 seconds to create the task.

Anyone have any advice?

Thanks,
-Will

View 6 Replies View Related

Problem In Recursive Loop In Script Task

Mar 3, 2008



I have written a recursive loop, the function using dataview.rowfilter ="something".

On some servers I am getting a weired issue. when I am interating through rows in dataview its throwing me an error.
"No row at position 1", I am using rowView("ColumnName")

strange thing is the underlying data row is returning me the value but row view is throwing an error.

I thought its an issue with SP1, but after installing SP2 still failing..

View 1 Replies View Related

For Each Loop And Script Task Not Working Properly

Oct 5, 2007

Hi,

I have a FOR EACH LOOP that loops through a bunch of folders in search of particular filename (including path). That part works fine.

Then, INSIDE the FOR EACH LOOP, I have a script task that evaluates the filename and grabs the date portion of the filename. That part works fine too.

Now, here's my dilemma. If the date portion of the file name IS GREATER THAN the the value of another date in a variable, I want it to load the file into a table using a data flow task.

So, this is what my package looks like:

[ FOR EACH LOOP (Script task --- evaluate the date using an expression (see below) ---> Data flow task) ]

My expression looks like this:

@[User::fileDate] > @[User::maxLoadDate]

You think that this would work, right??

However, after the VERY FIRST iteration of the loop, the script task turns green, then it goes right to the data flow task, which then says that the file name is "", and can't be loaded. (I'm not sure why this is occuring)

My question is, is my logic correct? Can I use an expression inside the for each loop and have it work?





View 1 Replies View Related

Data Flow Task Within For Each Loop Not Executing

Aug 22, 2007

All:
I am sure I am missing something really silly but I am not able to figure out what. The For Each Loop uses an ADO Enumerator and passes variable values to a data flow. In executing the package the loop runs fine but nothing is happening to the data flow. When I move the data flow out of the loop it runs fine. What is going on?

Thanks!

desibull

View 14 Replies View Related

Execute Sql Task Inside For Each Loop Containter

Feb 15, 2008

Hi, I'm trying to loop thru a table and insert records into another table in ssis. So far I have been able to get the data using a execute sql task set up to store the full result set into a variable called data. I then drug a foreach loop container out and selected the foreach ADO enumerator and used my variable data as the ADO object source variable. I then set up a new variable under variable mappings with index 0 to get the collection values. How do I take that variable and update another table using another sql task inside the foreach loop container? Is this possible?

thanks,

View 8 Replies View Related

For Loop Container And File System Task

Oct 4, 2007

I am trying to write a SSIS to distribute files to our client directories. In my source directory ( C:ClientPending ) i have a variable amount of file with variable file names and extensions. I need to sort these and place then in to different cient direcories. Each file name will contain the clients Referance Number ( ie Q123456.007 , A123456.exe, S123456.523 etc ). this clients directory would be C:ClientOutbound123456

Im presuming that i need a for loop container and a system file task, and a variable that i can parm in the ClientRef, i just dont know how to configure it.

Can anyone shed some light on this?
ta

View 1 Replies View Related

DTS Task Properties And Global Variables

Mar 2, 2004

Hey all,
I have a stored procedure, which need one variable as parameter. I am trying to call this stored procedure from my DTS Task and my parameter is defined as the Global Variable in DTS. here is the SP call within my DTS Task

declare @id int
select @id = DTSGlobalVariables('ClientId' ).value
exec sp_Update_DayPart @ClientId= @id


it gives me an error that DTSGlobalVariables function not defined. In this case how can i pass the value of Client Id which is my global variable to my SP.

Thanks in Advance

View 1 Replies View Related

Execute SQL Task - Passing Variables

Aug 28, 2007

how can you pass variables from one 'Execute SQL Task' to another?

View 9 Replies View Related

Execute SQL Task To Update Variables

Jun 30, 2006

Hi there

I'm attempting to update a variable using the Execute SQL task, I've read a lot of posts on this and seems reasonably simple but obviously not. The first time I ran it the variable was updated correctly, I then manually changed the variable value and since then it doesn't work.

I have a task with the following properties;

Resultset: SingleRow

SQLStatement: SELECT MAX(Player_Daily_Data_Pull_Date) as 'PlayerDaily' From Job_Control

On the resultset tab I have the resultsetname = PlayerDailyand the variable I want to update.

The variable has a type of datatime and it's scope is the container that I'm running the sql task within.

Any help would be appreciated.

Derek





View 5 Replies View Related

File Task Moving With Variables...

Apr 25, 2007

I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.



Source Path & Filename:

@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]

Outputs:

\umrdwh2FTPCLAIMDAILY.DATA



Destination Path Now: (Filename not specified per another thread.)

@[User::WorkingFolder]



Original Destination Path: (Which did not work.)

@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]



I receive validation errors:

Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0





How do I overcome this without hard coding the path in the task?












View 11 Replies View Related

Problem With Variables In Execute SQL Task

May 2, 2007



I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.



when I run the package i get the following error:



Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".



543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.



Please help i have very little hair left!!

View 16 Replies View Related







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