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

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

How Can We Put More Than One Evaluation In The For Loop Containter?

Apr 18, 2008

Hi, experts,

I found it annoying that I have not being able to put more than one conditions in the Evaluation Expression within the For Loop Containter? e.g. iCounter<N , also I want to put another condition there like IsHoliday=1. How can I achieve that?

I have tried to used the LOGICAL AND operator && to combine more than one condition, but it did not work.

Please shed me some light on that.

Thank you very much and I am looking forward to hearing from you shortly.

WIth best regards,
Yours sincerely,

View 5 Replies View Related

XML Task Inside Foreach Loop Container Had To Fail!!!

Aug 9, 2007

Hi,

I am using an XML task for validating the XML data against the Schema XSD. I have more XML Files with same the schema. I have to used a for loop container which has an XML task for validate XML. The loop container gets the XML File into variables name "XMLFileName" which the loop current file, in turn, used by XML Task for validation.

XML task is configured in the option "Validate". I have provided the XML Data in variable name "XMLFileName" also get the name from the loop container and XSD file content File Connection. XML Task stored the result in the another variable name "OKFormat". FailOnValidationFail property set to false.

It had the error when I run the package, the error msg as below:

Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".

Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.

Task failed: XML Task

After that, I had to change the source type from Variable to File Connection, and had to test fixed to some xml file it had ok for validate the XML file againt XSD.

I don't know this is the wrong setting or bug of SSIS, anyone can guide me through.

View 7 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

Integration Services :: Parameterize TOP Clause Inside Execute Task

Jun 23, 2015

in a SSIS 2012 pkg, I'm trying to specify a SELECT TOP ? myColumn FROM myTable inside an Execute SQL task, but unsuccessfully.Is it possible to parameterize the TOP clause?

View 6 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

SSIS - For Each Loop With Insert In Execute Task Failing

Oct 19, 2015

I have ForEach Loop using Foreach File Enumerator. Within this loop I have SQL Task containing an Insert statement. When I run the Insert statement in query builder the transaction inserts data into a table as expected.

However, when actually running the process I am getting the error message:

Executing the query "INSERT INTO dbo.TEST_TABLE
..." failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I currently have the ResultSet to "None" and have defined the parameter I am using. Where the process seems to joke is on my file_Name variable will I am trying to insert only part of the file name.

My insert statement looks as follows:

INSERT INTO dbo.TEST_TABLE
(IID, AN8, File_Type, IB_OB, File_Name, Processed_Flag, ReceiptDateTime)
VALUES
(
'1111',
'123',
'888',
'IB',
RIGHT(LEFT(?, LEN(?) - 4), LEN(?) - 24),
'I',
GETDATE())

View 0 Replies View Related

Insert Failure While Using ForEach Loop + Execute SQL Task.

Sep 10, 2007

Hello All

I was trying to insert some row from one table to another of different database.
I was using Execute SQL task along with Foreach loop container.

In my execute SQL task I am using this query

SET IDENTITY_INSERT dbo.Table1 ON

INSERT INTO dbo.Table1

SELECT * FROM DB2.dbo.Table2
WHERE TableKey = ?

When executed I get this error:
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.

While the same query when executed in Management Studio Its successful.

The properties I set

For Each Loop Editor Settings:
1) Collection: a) Enumerator Set to ForEach ADO Enumerator
b) ADO Object Source Variable: User:bjectVariablename
c) Checked Rows in the first table
2) Variable Mapping: New Int Variable2 and Index = 0 to set it to first colunm.
3) Expression: Left blank

Execute SQL Task Editor:
1) General: a) Timeout : 0
b) CodePage: 1252
c) Result Set: None
d) SQLSourceType: Directinput
e) SQL Statement: SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1 SELECT * FROM DB2.dbo.Table2 WHERE TableKey = ?
f) BypassPrepare: False
2)Parameter Mapping: Variable Name : New Integer variable2 selected
Direction: Input
DataType: Long
ParameterName: 0

Can somebody help me in this regards.

Reference:
a) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

View 10 Replies View Related

Capture Files With ForEach Loop And Execute Sql Task

Feb 27, 2008

Hi all,

I'm trying to capture the file name and insert to the Database with Loop Container and Execute Sql task...However when I run, I get error with the input parameter.

In my Sql Task, the parameter mapping:
Variable name: user::variable,
direction: input,
Data Type: Varchar,
parameter name: @xVariable,
parameter size: -1

connectiontype: OleDB
sourcetype: direct input
statement : insert into xtable(xcolumn) values(@xVariable)

Anyone have any other suggestion how to capture the filename and input in the database?

thanks

View 4 Replies View Related

Execute Process Task To Unzip In Foreach Loop

Jan 8, 2008

Hi,

I am trying to use SSIS.Execute Process Task within Foreach loop . The task should unzip a a variable gzip file provided to it from the previous task

to execute the command from cmd C:Program FilesGnuWin32ingzip.exe -d -k c:folderfilename
My input in the Execute Process Task as follow:

The command line: C:Program FilesGnuWin32ingzip.exe

Argument: -d -k c:folder

Variable: User::var_filename
keep getting error

The process exit code was "1" while the expected was "0".
Thanks

View 3 Replies View Related

Foreach Loop Xmla Analysis Services Execute Ddl Task

Mar 21, 2007

Hi:

I am a R data miner who is new to SQL and SSIS and would appreciate any help.

I wanted to automate the process of creating and processing decision tree models for every county in the Country. I wanted to use the foreach loop for iterate through all the counties. I wanted the foreach enumerator to be used by the XMLA code that creates the model so it would append it to the name of the model and i would get a different model for every county. I am not sure how to have the XMLA code accept foreach loop enumerator.

Any help would be greatly appreciated and if you could direct me to a previously done example that would greatly benefit me.

Thank you
avneet

View 1 Replies View Related

Using User Defined Function In Execute SQL Task Within Foreach Loop

Jul 19, 2007

hi

I am trying to execute a UDF in an Execute SQL Task within a Foreach loop.

I have an Execute SQL task to get the dataset (Firstname, Lastname, CompanyID) which is going into a full result set called @PendingPar. The Foreach Loop is set to a Foreach ADO Enumerator against @PendingPar and I have created 3 variables at the Foreach Loop scope level called @Firstname (String), @Lastname (String) and @CompanyID (Int32). These are mapped in the Foreach Loop as index 0,1 and 2.

Within the Foreach Loop I have an Execute SQL Task with the sql command

select dbo.udf_GetParTitle (?,?,?)

In the parameter mapping I have Variable Name = User::Firstname Direction = Input Data Type = VARCHAR and Parameter Name = 0, User::Lastname, Input, VARCHAR, 1 and User::CompanyID, Input, VARCHAR,2

Finally the UDF returns a @NewPar value so i have the result set in the execute sql task to Single Row which goes into variable @NewPar. This @newPar will be used later but I cannot get the first bit to work yet.

the error message i get back is

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "select dbo.udf_GetParTitle (?,?,?)

" 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 1

the initial execute sql task where the dataset is selected works fine but it is within the foreach loop that it fails.

any help gratefully recieved

View 6 Replies View Related

SSIS: Fpreach Loop Container- Execute SQL Task With ReturnValue Parameter

Jun 9, 2006

Here's the set up:

ForEach Loop Container:

Collection: Foreach File Enumerator

Variable Mappings: Variable = User::DailyFile, Index = 0

Execute SQL Taks:

Connection: OLEDB

ResultSet: None

SQLStatement: EXEC spGetFile ?

ParameterMapping: VariableName = USER::DailyFile, Direction = Input, DataType=VARCHAR, Parameter = 0

This works great it iterates through a file and looks at all the files checks to see if they have been loaded into the db table, if not it loads the file.

My spGetFile has RETURN 1 if a file is loaded and RETURN 0 if the file is not loaded.

Now I add a new variable:

Step 1: add to Foreach Loop Container

ForEach Loop Container Name = Return, Scope = ForEachLoop, Data Type= Int32 Value=0

Step 2: Add to Execute SQL Task:

VariableName = User:Return, Direction = ReturnValue, DataType = Long, ParameterName =1

This produces the following error:

~~"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.~~

I tried with an ADO.Net connection ... works until I add the ReturnValue parameter ...

Any ideas??

Thanks

View 4 Replies View Related

Getting Access Denied To FileName Error When Using The Execute Sql Task (With File Connection) Into A Foreach Loop Container.

Jan 18, 2007

Hi ALL,

Getting Access Denied To FileName Error When Using the Execute Sql Task (With File Connection) into a Foreach Loop Container.

Please Note :

I have a folder containing .sql files. I have to dynamically loop through the files and send them as a File connection Folder to the Execute Sql Task.

When I run this Package I am getting the follwoing error :

[Execute SQL Task] Error: An error occurred with the following error message: "Access to the path 'C:ProjectsFuzzy Lookup DataFlow ExampleScripts' is denied.".

Also I have logged in to the machine as Administrator and to Sql Server with sa.

Please help.

Thanks.

Regards,

Salil

View 1 Replies View Related

Loop Inside SP

Feb 1, 2004

hello,

anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?

ur help is much appreciated!

thanks,

View 2 Replies View Related

Loop Inside Of A Cursor

Oct 23, 2000

I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated

declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate

END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor

View 2 Replies View Related

Loop Inside View

Apr 4, 2007

Hello,

is it possible to build a loop for the following statement?

CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)

I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!

View 5 Replies View Related

For Each Loop Container Do Nothing Inside

Aug 27, 2006

Hi,

A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?

Your help is much welcome. Thanks in Advance.

João Cruz

View 2 Replies View Related

Problem With Transaction Inside Loop

May 6, 2008

Hi,

When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.

Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest

Please let me know the reason for this.

Thanks in advance

Regards,
K. Manivannan

View 1 Replies View Related

Insert Into Table Inside For Loop

Feb 6, 2015

I wanted to insert values in columns as explained in below ex.

I am having a table that contains Column1,Column2,Column3,......,Column10.

Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.

My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.

View 3 Replies View Related

Variable Inside A Nested Loop

Jul 20, 2005

I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Problem With Union Inside A While Loop

Feb 16, 2008

im trying to do this

declare @count int

set @count=0

while @count<4

begin

set @count=@count+1

select *

from dbo.Categories

where CategoryPID=-1

union()

end

and i get a error
this is not the original code but i want to union all select statements
please help !!!

View 10 Replies View Related

How To Check Out EOF Inside A Foreach Loop Container?

Apr 12, 2007

Hi everyone,

I've got a Foreach loop container which uses a Foreach ADO Enumerator and works fine.

But problem comes when I launch inside the general loop another Sql Task (select) which sometimes has rows and sometimes hasn't. When it have rows everything is fine the workflow follows fine but when it has not.

I obtain this error (obvioulsy)

[Execute SQL Task] Error: An error occurred while assigning a value to variable "GZon": "Single Row result set is specified, but no rows were returned.".

How to deal with that?

Thanks indeed

View 5 Replies View Related

Pass Filename To Flatfilesource Inside Foreach Loop

Jul 26, 2007

Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

Please note, this is a different question to my other post.

Many Thanks

View 6 Replies View Related

Data Flow Inside For Each Loop - Error Handling

Jun 23, 2006

Hopefully this is an easy question:

Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.

I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.

View 4 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

DB Engine :: Memory Table Not Cleared If Created Inside A While Loop

Oct 29, 2015

If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.

But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.

This can be reproduces with the following code:

DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)

[Code] ....

/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2

Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).

View 2 Replies View Related

SSIS - How To Set ServerName And DatabaseName At Run Time Inside FOREACH LOOP?

Jan 14, 2006

I am new to SSIS world, so my question is very basic.

Setup:

In a company I work for we have 12 SQL servers each running between 1 and 3 databases with anywhere between 10 to 20 tables. I need to query some of these tables and merge results to the destination database.

The list of all these tables is stored in the separate table <SOURCES> of the following format [ServerName,DatabaseName,TableName]. Tables of my interest have identical structure (same columns) accross servers and databases.

Question:

How can I loop over servers and databases specified in <SOURCES> to run otherwise identical query against these tables?

I can easily retrieve [ServerName,DatabaseName,TableName] from <SOURCES> as string variables using FOREACH loop. The problem is now - how do I use string variables to set up Server, Database and Table name at run-time?

Thank you

View 4 Replies View Related

Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query

Dec 6, 2006

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

View 5 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.

FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related