Hi,
I want to create an select query and loop it thru , process couple things like finding maximum,count values
for every row...
it is easy to do it with sprocs , I can open couple cursors and do it but How can I do it SSIS transformations?
thanks,
J
I am having trouble with this statement. I am returning multiple rows because I am doing the select statement within the loop. I need to keep the loop somehow because of the where clause of the select statement:
'AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @start between sh_istart and sh_istop'
Is there anyway that I can maintain the ability to use the loop but not do mutiple select statements like below:
Also I'm trying really hard not to use temp tables in this example
Result from select statement below
sh_serial
-----------
53565
53597
sh_serial
-----------
53565
53597
sh_serial
-----------
sh_serial
-----------
53588
53597
Desired results:
sh_serial
-----------
53588
53597
53565
Syntax:
declare @start int
select @start = 580
declare @stop int
select @stop = 900
while @start <= @stop
begin
select sh_serial,
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and (sh_serial <> 600000)
and sh_serial in (53565,53588,53597)
and sh_serial in
(select distinct sh_serial
from casemas, schilin
WHERE (schi_shser = sh_serial)
and (schi_itemno = '004852')
and sh_serial in (53565,53588,53597)
AND @start not in (select sh_istart from casemas where sh_istart in (select sh_istop from casemas where sh_serial in (53565,53588,53597)))
and @start between sh_istart and sh_istop
group by sh_serial
having (sum(schi_qty) + 1 < 4 ))
select @start = @start + 1
end
I'd appreciate any help. Thanks! :o
What I wanted to do is I want to loop through each select result and at the same time use the result to do something while in a loop.
While (select field1,field2,field3 from table 1)
BEGIN
select count(*) as field4 from table1 where field1(current_record)>3
update table1 set field2(current_record)=field4
END
something like that...How do I do this?
i have a stored procedure with one coming id parameter
ALTER PROCEDURE [dbo].[sp_1]
@session_id int
...
and a view that holds these @session_id s to be sent to the stored procedure.
how could i call this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records.
Hi everyone, not sure if a this topic has been covered yet (a have been looking all day), but as I am still very new to this, my problem is as follows:
In the Try .. Catch block below, data is posted from a form and the SqlCommand.ExecuteScalar() statement returns a Unique Job ID.
I am attempting to populate a subordinate table for qualifications which are selected from a ListBox, but rather than using qualification titles, I am using the values.
My problem is that only one value (the first) gets posted multiple times, when multiple values are selected.
Looking at the For Each loop in the inner Try Catch block, I am wondering whether there is some sort of Index pointer that needs to be incremented, in order to establish new values further down the list.
I have seen no evidence that this is the case, save for the fact that the value stalls on just the first.
Any help would be appreciated.
===== CODE ===
Try
C4LConnection.Open()
JobPostingID = SqlJobPost.ExecuteScalar()Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
Try
' Multiple Qualification EntriesSqlQualPost.Parameters.Add(New SqlParameter("@JobPostingID", SqlDbType.Int))
SqlQualPost.Parameters("@JobPostingID").Value = Int32.Parse(JobPostingID)SqlQualPost.Parameters.Add(New SqlParameter("@QualificationID", SqlDbType.Int))
SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
If Qualifications.SelectedIndex > -1 ThenFor Each Item In Qualifications.Items
If Item.Selected ThenResponse.Write("<br />SelectedItem Value: " & Qualifications.SelectedItem.Text)
QualPostingID = SqlQualPost.ExecuteScalar()SqlQualPost.Parameters("@QualificationID").Value = Int32.Parse(Qualifications.SelectedItem.Value)
Response.Write("<br />Selected Item: " & Qualifications.SelectedItem.Value)
End If
Next
End IfCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Could not add qualifications <br />" & Exp.Message
End Try
failJobPost = FalseCatch Exp As SqlException
failJobPost = True
lblError.Visible = TruelblError.Text = "Error: could not post job to database <br />" & Exp.Message
Finally
C4LConnection.Close()
End Try
Hello All,
Below is a simple Select statement performing a Lookup into a SQL database and returning the columns (associated with the Row) in to Cells on an eForm. The issue I have is there are 42 rows (which go up and down) and do not feel like writing 42 select statements.
select RiskDescriptor, RiskImpactLowDescriptor, RiskImpactMediumDescriptor, RiskImpactHighDescriptor
from [Risk Descriptors]
where [RiskDescriptor ID] in (1)
order by [RiskDescriptor ID];
<<1@Cell104>>
<<2@Cell105>>
<<3@Cell106>>
<<4@Cell107>>
I would like to add a loop, adding 1 to the RiskDescriptor ID and 4 to the Cells. So on second pass in the loop:
RiskDescriptor ID = 2
<<1@Cell108>>
<<2@Cell109>>
<<3@Cell110>>
<<4@Cell111>>
Third pass in the loop:
RiskDescriptor ID = 3
<<1@Cell112>>
<<2@Cell113>>
<<3@Cell114>>
<<4@Cell115>>
and so on.
The Until portion of the loop can be hardcode (42 in this example) but would rather use an EOL or Query the DB for the total number of RiskDescriptor ID. This way when the DB changes (ID's go up or down) the SQL Statement does not need to be notified.
It is a JDBC call from within the eForm.
I would appreciate any help on how to format a loop in a SQL Statement
I have two tables that I wish to return values for and populate a text file with the returned values. This part works well, however, I get strange values when I rung the Select statement against both tables.
For example, if I run this statement:
Select count(page_count), sum(Page_count) from Contracts
this will return the values I expect
If I were to add another table to this query then things go crazy. For instance, if I took the same query and added the Maps table to it as such:
Select count(page_count), sum(Page_count) from Contracts, Maps
I get crazy numbers that are many times more then the actual numbers
My goal is to get the page count, number of records for Contracts and number of records from Maps all in one query
Can I safely use top n select/delete in a while loop? For example:
declare @FieldVal int
while (select count(*) from @MyTempTable) > 0
begin
select top 1 @FieldVal = FieldVal from @MyTempTable
-- process @FieldVal then delete the row
delete top 1 from @MyTempTable
end
I like the simplicity of the above approach as long as it's reliable and there aren't any gotchas that I may not be aware of.
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
Hi,
I am a newbie in SSIS.
Can anybody please help me in the following.
Here is the task that I want to achieve:
1. continously poll a db table every 1 minute,
if the value of a paticular cell in the table has changed since last poll,
then initiate the second task
2. do a select query that picks about 10,000 new rows off another db table,
the 10,000 rows should then be stored in a in-memory dataset.
Every time the poll initiates a new select query, it should insert the new rows to the existing in-memory dataset.
thus if the select runs for 2 times in 2 minutes, the the in-memory dataset would contain a maximum of 20,000 rows.
3. Then I want to apply a set of transformations on the dataset and then finally update some db tables, push some records to the ssas database. (push mode incremental processing)
which sub tasks can be achieved and which cannot.
if not, Is there a workaround?
Please do provide some specific links that accomplish some of these similar tasks.
I have tested some functionality, like
doing a full processing of a ssas database.
reading from a database table and inserting into a flat file.
I tired to use the ExecuteSQLTask, and i also assigned the resultant to an user:variable. the execution completed succesfully but I am not able to see the value of the variable change. also I am not able to use the variable to figure out a change in previous value and thus initiate a sql select. or use the variable to do anything.
Regards
Vijay R
Hi All. How do i loop over to extract data out of a xml parameter in order to insert that into a table along with other input. For example if the xml is <Id>1</Id> <Id>2</Id> <Id>3</Id> I want to input values into table (1,data2,data3) and (2,data2,data3) and (3,data2,data3). How do i do that?
View 6 Replies View RelatedCurrent Code
SELECT [Patient Identifier], Date, [Operator Index], Time
FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
ISNULL(t9.Date, t8.Date)
ORDER BY c.Time) AS RowNum
FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 90
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 2) AS t9 FULL JOIN
(SELECT [Patient Identifier], Date, 4 AS [Rows]
FROM [First Step]
WHERE [Operator Index] >= 80
GROUP BY [Patient Identifier], Date
HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
WHERE d .RowNum <= d .[Rows]
Current Input
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:41 89
5170000417OCT200611:50 93
5170000417OCT200611:52 91
5170000417OCT200612:00 93
Current Output
Patient IDDATE Time Operator Index
0517_0000318OCT200611:48 91
0517_0000318OCT200611:50 100
0517_0000417OCT200611:41 89
0517_0000417OCT200611:50 93
It should be
Patient IDDATE Time Operator Index
5170000318OCT2006 11:48 91
5170000318OCT200611:50 100
5170000417OCT200611:50 93
5170000417OCT200611:52 91
The data is organized by patient id, date, time (ascending)
For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?
I want to, for each month of the year 2014 say, to create a loop that will enter data into a table.
Right now I have:
Select [Member Number],
sum(case when [Receipt Date]='2014/01/01' then Amount else 0 end) as [Rec 2014/01/01]
From [Receipts Table]
Group by [Member Number]
Insert into [Receipts 2014/01/01]
[Code] ....
Instead I would just like to do something like…
Declare i date
For i=2014/01/01 to 2014/12/01
Select [Member Number],
sum(case when [Receipt Date]=i then Amount else 0 end) as [Rec +i]
From [Receipts Table]
Group by [Member Number]
Insert into [Receipts + i]
Don’t know if this is at all possible?
how to pass the variables as the data source in data flow in for each loop( ADO enumerator)?
View 1 Replies View RelatedIn my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.
Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.
This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?
Thanks,
Gary
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedMy insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50)
CREATE TABLE #ActiveUsers
(
ID INT IDENTITY NOT NULL
,EmployeeID varchar(50)
,processed int
)
Create Table #Data
[Code] ....
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
I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.
drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint
For some reason I cant not get 2015 data .
Hi Folks,
I am trying to build a Package that selects a list of Uniqueidentifiers by "Execute SQL-Task" and then loops through the ResultSet of the query using ADO.Net Enumerator to do something with this GUID Value, namely deleting all entrys with this PK in a different DataBase.
The main Problem I am facing is that you cant select the type "GUID" for a user Variable in "Execute SQL-Task". All the Datatypes are there except for GUID!
This leads to the following error:
Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ImagicID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Is this a bug? Or am I doing something terribly wrong?
Best Regards and thanks in advance for your help
Chucker
Hi, I am attempting to create SSIS packages to extract data from our AS/400 DB2 databases and populate tables for analysis and reporting in SQLServer. We have recently installed a new 2005 SQL Server to replace our existing 2000 databases. I have several DTS packages setup to connect to the 400s and copy the needed data to SQL2000 and these work great but do not transition to SSIS very well. I can get my SSIS packages to work if I create a seperate data flow task for each table but that does not seem appropriate for a tool like SSIS. I would think that what I am attempting is a very common thing to do. Maybe other enumerators could be used but I have not been able to get it to work.
I am trying to use a foreach loop, currently with a forech item enumerator, to first truncate the table on the SQL Server database using an Execute SQL task, and the to use a Data Flow task to refresh the data using an ole db source and destination. I have the truncate portion working, but when I try to setup the Ole db source using a variable it tells me I have no input columns. I have a variable from the forech loop that loops through the table names, this variable is used in a second variable which is set to EvaluateAsExpression with the expression set to the follwoing: "select * from " + @table_name
I have set delayvalidation to true and validateexternalmetadata to false. What do I do in order to get around this issue? Also, how should the old db destination be set so that it load the data into the table specified in the variable?
Thanks for any help you can provide.
Keith C
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.
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
We are having folder table and team table as like below structure.
Folderlist (F)Table: (
==============
id ,folder_name, parent_id
1, c, 101
2,b,202
3,c,203
Teamlist table (T)
============
team_id, Team_name, Parent_folderid
101 , mobile,101
202 ,Tab,200
200, Phone,200
203,apple,205
205,nokia,208
208,samsung,208
If F.parent_id(101)=T.team_id(101) and T.team_id(101)=T.parent_folder_id (101)
then output should come as 'Mobile/c' (this is for f.parent_id=101)
If F.Parent_id=T.team_id and T.team_id!=T.parent_folder_id
then parent_folder_id have to start search on team_id column where it got match and pick the Team_name from that corresponding id
Ex: F.parent_id=202 is matching with T.Team_id (202) but this T.team_id(202) is not matching with T.parent_folderid(200) , so this T.parent_folderid (200) have to search on T.id (200) ,if now T.id(200) is matching with T.Parent_folder_id(200) then it have to give the names from the starting hirache
like phone/tab/b (this is for F.parent_id=202)
Hi,
I want to do the following with a ssis package:
INPUT:
A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS:
I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Thanks
Danny
I have a package that runs fine by itself. But when I run it inside a Foreach Loop container on a parent package, I got a buffer error after a few loops. Here are a couple of the error lines:
A buffer failed while allocating 49085616 bytes.
The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
I already played around with the Data Flow tasks DefaultBufferMaxRows and DefaultBufferSize properties, and I am still getting the error. Just wondering if there is a memory leak or something with the Foreach Loop task. I havent install SP1. Maybe SP1 fixes this issue?
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
Greetings!
I come before you seeking assistance on a package that basically flows very much like the "Table Driven foreach Loops" example provided by Kirk Haselden at http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx
I am presently encountering the following exception:
SSIS package "DW.CUST_CNCL_ORDhardcodedate (1).dtsx" starting.
Error: 0x3 at Shred the contents of the variable: Variable "User::FullResultSet" does not contain a valid data object
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.
Warning: 0x80019002 at DW CUST_CNCL_ORDhardcodedate: 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 "DW.CUST_CNCL_ORDhardcodedate (1).dtsx" finished: Failure.
Does anyone in the community have any ideas as to what may be the cause of this exception?
I look forward to your reply. Thank you and Grand weekend to you and a very Blessed Thanksgiving.
Thanks,
Carlos
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
Suppose if I have a “Foreach Loop Container” that iterates over a list. Is it possible to execute different data flow tasks based on the input?
Example : List contains elements L1, L2 & L3.
ForEach Loop Container checks the input. If its L1 then it should execute DF Task1, If L2 then execute
DF Task2 and similarly for L3.
Is it possible to achieve this?
I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.
I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.
Any solution to this? anyone experienced anything similar
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what Im trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.