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
ADVERTISEMENT
Jul 29, 2015
Send Mail task not working properly when it configured in Precedence Constraint (Failure)...
Scenario:
I'm using multiple containers in my ssis package. Also I have configured mail task for capturing error.
I'm getting mail only when error occurs in foreach loop container. If error occurs in other containers it doesn't works.
Actually I would like to receive mail notification on error when it occurs in any of the containers or DFT.
View 9 Replies
View Related
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
Jan 17, 2007
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick
View 1 Replies
View Related
Apr 21, 2008
Hello friends, I have developed an ASP.NET 2.0 application. The problem is that the IIS server returns the next error:
SQL Server does not allow remote connections
Sometimes returns:
SQL Server does not exists.
My question is if there is any known bug with ASP.NET and SQL Server. My sql server have activated the Remote Connections (its working properly on other web page).
Its a problem of the number of connections??
Its a problem ofbad configuration??
Its a IIS problem??
Please bring me some help.
View 8 Replies
View Related
Dec 25, 2007
Hi,
I am exporting a table to a fixed width flat file using "Ragged Right" format. Though i specify carriage return for the last column, the text file is not taking it up.
I am doing the following for connection
cmFlatFile.properties("Format").SetValue(cmFlatFile,"RaggedRight")
cmFlatFile.properties("RowDelimiter").SetValue(cmFlatFile,vbCrLf)
and while adding the column i am setting up the width as follows
vColumns = flatFileDestination.InputCollection(0).GetVirutalInput().VirtualInputColumnCollection
For cols = 0 to vColumns.Count -1
Dim col As IDTSConnectionManagerFlatFileColumn90 = ff.Columns.Add()
if cols = vColumns.Count - 1 Then
col.ColumnDelimiter = vbCrLf
end if
cols.ColumnType = "FixedWidth"
cols.Columnwidth = 20
......
Next cols
Still the rows are exported next to each other just like when we use FixedWidth.
View 4 Replies
View Related
Oct 2, 2007
Hi,
I have several packages where I set up a SQL Server log provider. The packages are all called from jobs.
However, for some reason, only ONE of the packages is logging to the sysdtslog90 table in my specified database.
All of packages have the identical set up for the logging. Here are my settings under logging:
PROVIDERS AND LOGS:
Name: SSIS Log Provider for SQL Server (box is checked)
Configuration: ServerName.DBName (I use this same connection for all the packages)
DETAILS:
OnError is checked
CONTAINERS:
All items are checked
So what's going on here? I have successfully logged to this table using test cases in BIDS, and as I mentioned, ONE of the packages is actually logging to the table when it's called from the job.
Thanks
View 5 Replies
View Related
Oct 11, 2007
Hi all,
I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it.
Any idea
Best regards
www.ITCubo.net
View 1 Replies
View Related
Oct 4, 2004
We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:
UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.
Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.
What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.
trigger code:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Please help...
View 1 Replies
View Related
Mar 28, 2008
Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me.
My data:
“lname�, “fname�, “addr�, “phone�, “lang�
My command:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '","')
The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to:
BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:operationsdatafilesGEFTPINGE_RCF_POS_EXPORT.txt'
WITH (FIELDTERMINATOR = '"')
But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes?
View 4 Replies
View Related
Dec 3, 2005
Hi all...I have a stored proc that works fine except I can't get thepaging property to work in ASP. It seems that because I'm building theSQL withing the SP is causing the problem. If I don't build the SQL asa string paging works. In my ASP page, the pagecount property returns-1 for some reason.Here's the proc:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER Procedure LS_DisplayAllCategoriesBoolean@aSections varchar(255),@field varchar(255)Asdeclare @sql varchar(1000)SET NOCOUNT ONset @sql = 'SELECT DISTINCT ls_product_catalog.id,ls_product_catalog.ItemNumber, ls_product_catalog.Name,ls_product_catalog.[Descriptor], ls_product_catalog.Price,ls_product_catalog.Shade_Name, ls_product_catalog.Sale_Price,ls_product_catalog.Sale_DescFROM dbo.ls_brand INNER JOINdbo.ls_product_catalog ON dbo.ls_brand.nID =dbo.ls_product_catalog.CategoryID INNER JOINdbo.ls_product_category ON dbo.ls_product_catalog.id =dbo.ls_product_category.nProductIDWHERE dbo.ls_brand.bVisible = 1 anddbo.ls_product_category.nCategoryID in (Select IntValuefrom dbo.CsvToInt(''' + @aSections + ''') ) 'if @field = 'bNew'beginset @sql = @sql + ' AND bNew = 1 AND(dbo.ls_product_catalog.bGiftOnly is null ordbo.ls_product_catalog.bGiftOnly = 0)'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGift'beginset @sql = @sql + ' AND bGift = 1 or bGiftOnly =1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'bGiftOnly'beginset @sql = @sql + ' AND bGiftOnly= 1'set @sql = @sql + ' order byls_product_catalog.[Descriptor]'endif @field = 'sale_price'beginset @sql = @sql + ' AND (sale_price is not null ORsale_desc is notnull)'set @sql = @sql + ' order by ls_product_catalog.Name'endexec(@sql)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ON
View 1 Replies
View Related
May 25, 2008
Can someone please help me figure out why this trigger causes a "Timeout expired" error after the first try? What I'm trying to do is create an incrementing default value for the projectid field. I have a unique index on the field and for some reason, the INSERT statement won't run inside the TRY block, even though the WHILE loop creates a unique id. It's acting like the loop isn't incrementing, but the PRINT statements prove that the loop does work, but the INSERT statement doesn't work after a succesful first try (i.e. NEWPROJ-1 is inserted correctlly). Anyways, any help would be appreciated.
Code Snippet
GO
/****** Object: Trigger [Projects].[CreateID] Script Date: 05/25/2008 14:16:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Projects].[CreateID]
ON [Projects].[tblProjects]
INSTEAD OF INSERT
AS
DECLARE @projectid varchar(25)
DECLARE @projectname varchar(100)
DECLARE @projectepskey int
DECLARE @maxcount int
SELECT @maxcount = 1
BEGIN
SET NOCOUNT ON;
SELECT @projectname = projectname
FROM inserted
SELECT @projectepskey = projectepskey
FROM inserted
SELECT projectid
FROM Projects.tblProjects
WHERE (projectid = 'NEWPROJ')
IF (@@RowCount = 0)
BEGIN
SELECT @projectid = 'NEWPROJ'
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
END
ELSE
BEGIN
WHILE @maxcount >= 1
BEGIN
BEGIN TRY
SELECT @projectid = 'NEWPROJ-' + CONVERT(varchar, @maxcount)
PRINT @projectid
INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname)
VALUES
(@projectepskey, @projectid, @projectname)
BREAK
END TRY
BEGIN CATCH
SELECT @maxcount = @maxcount + 1
PRINT CONVERT(varchar, @maxcount)
CONTINUE
END CATCH;
END
END
END
View 4 Replies
View Related
Oct 21, 2015
I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose. Â The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.
The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date. Â That is now the new Actual, and we need to sum up all transactions from then to the end date.
My problem lies in the fact that I can't get the query to add up the numbers.
The query is here:
SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON
[Code] ...
The return data looks like this:
3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0
[Code] ....
I want it to look like this:
3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0
View 8 Replies
View Related
May 30, 2006
Hi,
I have an SSIS Package to get the data from an Excel Source. For that I am using WMI Event Watcher to find when the file drops into a specific folder. It is working fine when I check any folders in the local machine.
We have a separate WebServer and Database Server for our application. The application is deployed in the Web server and the SSIS package is deployed in the Database server.
Since the files are dropped through application, it is dropped in the Webserver. I have mapped that drive in my Database Server and I am trying to link that folder in the SSIS package, but the event is not firing.
Please provide me some solution for this and revert for any clarifications.
Thanks & Regards,
Prakash Srinivasan
View 13 Replies
View Related
Sep 14, 2006
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
View 2 Replies
View Related
Jan 7, 2008
Hello,
I have a SqlDataSource that is not doing my inserts properly. even if the user is logged in (UserName!=""), it always inserts a null in the UserName field.
SqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:imLLConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserName]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserName)" SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserName] FROM [tblDiaryEntries] WHERE [UserName]=@UserName" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate WHERE [DiaryEntryID] = @DiaryEntryID"> <DeleteParameters> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserName" Type="String"/> </InsertParameters> </asp:SqlDataSource>
and from code behind, i do:
protected void Page_Load(object sender, EventArgs e) { if (!this.IsPostBack) { SqlDataSource1.SelectParameters.Add("UserName", this.User.Identity.Name); SqlDataSource1.InsertParameters.Add("UserName", this.User.Identity.Name); } }
Any ideas/suggestions? Thanks!
View 9 Replies
View Related
Oct 20, 2007
Hi,
I have an Execute Process Task that calls a batch file.
I need to pass in a date value to the batch file.
If I enter the value 20071018 in the "Arguments" section, it works fine.
However, I use the "Standard Input Variable", User::MyDate (which gets assigned in a previous Execute SQL Task), it bombs. The query I am using returns the date back in the format yyyymmdd. It gets assigned to a string variable, called MyDate.
e.g.)
SELECT TOP 1 CONVERT(VARCHAR, MyDate, 112) AS MyDate
FROM myTable
which result is mapped to User::MyDate
Is there any obvious reason why this doesn't work?
Thanks
View 3 Replies
View Related
Feb 21, 2008
Hi All
I have a parameter @User givning a dropdown list from a dataset:
SELECT FullName FROM tvf_GetListOfUsers ('LDAP://OU=Business
Users,OU=Users,OU=Production,DC=MRP,DC=NET,DC=NZ','Subtree')
ORDER BY FullName
The parameter is passed to a second dataset when the User is chosen:
SELECT GroupName FROM tvf_GetUserMembership('LDAP://cn=' + @User +
',ou=business users,ou=users,ou=production,DC=MRP,DC=NET,DC=NZ')
ORDER BY GroupName
This works fine in VS but when I deploy it only works the first time.
That is, I enter the username and run the report - it brings back the
desired results. But when I enter a different username it just redisplays the
old data from the first username I entered.
Any ideas?
Thanks
Pete
PS The funcions above called in the datasets are for querying Active
Directory but I think this is not the cause of the problem.
View 1 Replies
View Related
Sep 28, 2007
hi,
i have a table having two column say columnA and columnB.columnA(text datatype) contains the data that is to written in a file and columnB(nvarchar datatype) contains the filepath.
but while configuring the export column tranformation, in the export column field i am getting the list of both the colums. here i am choosing columnA but in the file path name column i am not getiing the name of the column which contains the file name.
can anyone help me regarding how to configure the file path name column in export column transformation. from where we ll get the file path name.
in control flow, i have used only dataflow component and data flow tab i have used oledb source-->export column transformation-->oledb destination.
please help in configuring the export column transformation.
thanks a lot !!!!!!!!!!!!!!!!!!!!!!!
View 2 Replies
View Related
May 24, 2008
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CommerceLibOrdersGetWashingtonState]
(@ShippingStateProvince VARCHAR(50),
@ShippingCountry VARCHAR(50),
@StartDate smalldatetime,
@EndDate smalldatetime)
AS
SELECT OrderID,
DateCreated,
DateShipped,
Comments,
Status,
CustomerID,
AuthCode,
Reference,
ShippingCounty,
ShippingStateProvince,
ShippingCountry,
ShippingID,
TaxID,
ShippingAmount,
TaxAmount
FROM Orders
WHERE (DateCreated BETWEEN @StartDate AND @EndDate)
AND (ShippingStateProvince = @ShippingStateProvince)
AND (ShippingCountry = @ShippingCountry)
ORDER BY DateCreated DESC'
END
View 4 Replies
View Related
Aug 8, 2007
hi,
i have installed SQLEXPRESS (SQL Server 9.0.3042) on my windows ultimate laptop. of late am facing this issue of database. this database is from SQl 7.0 Desktop edition(the one that i use in my office machine with Xp SP2). As such it works fine in SQL 2000 server after attachment (for test).
i have run SQLEXPRESS as administrator to make/attach new database. i can attach the database easily and can even view tables and other stuffs inside database in administrator mode. the issue pops up when i run SQLEXPRESS in normal mode. when i try to open and view the same DB's content, error pops up stating
"The database XXX is not accessible. (Micorosoft.sqlserver.express.objectExplorer)"
i cannot access this DB via VB 6 form as it dosnt show up at all in database list when tried to connect via ADODC.
i want to make this DB accessibility as normal operation, not previliged only to administrator as application run on normal mode only.
the issue dosnt arise when i locally make a new database as it can be accessed when run in normal mode also. why does this happen to database that is attached and not to ones locally made.
pls guide me as i usually do my works at home and making whole database again will surely take lots of pain. why not it just attach then start working, not asking for preveliged mode.
thanks in adv
Nyaken
View 3 Replies
View Related
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
Feb 9, 2006
Hi all,
Drill through bar charts work great in the report designer preview, but as soon as I deploy the report, and view the report in a webpage, drill through does not work smoothly.
I have to move the mouse around to be able to find a place on the bar to click through to the next report. Sometimes, I am able to click on the bar to go to next report, but then sometimes I have to click on the label, and other times somewhere in the vicinity of the label.
Any help will be much appreciated.
Thanks,
Kanwal
View 1 Replies
View Related
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
Jul 8, 2007
Ive got a gridview of options that the user can select. It displays all the networks that the user is in. When the user clicks "submit" button my code (below) does a loop for every gridview row in the gridview.It finds the checkbox, and the label (the name of the network) if the checkbox is not checked it does nothing (or is supposed to) if it is checked it is meant to add a network link to my database, when the check box is checked the item is entered into the database fine but when the user unchecks the checkbox is still enters it. Any ideas whats going wrong? thanks si! foreach (GridViewRow row in GridView1.Rows) { CheckBox chk = row.Cells[0].FindControl("CheckBox1") as CheckBox; Label lbl = row.Cells[0].FindControl("Label7") as Label; if (chk.Checked == false) { //do nothing cos they arent going in that network. } else if(chk.Checked == true) { //do something cos its going in the selected network.... //get the network id SqlCommand TypeCommand2 = new SqlCommand("SELECT NetworkID FROM Networks WHERE Name = '" + lbl.Text + "'", Connection); Reader = TypeCommand2.ExecuteReader(); Reader.Read(); networkid = (int)Reader.GetValue(0); Reader.Close(); SqlCommand AddNetworks = new SqlCommand("INSERT INTO Networks (ItemID, NetworkID) VALUES ('" + ItemID + "','" + networkid + "')", Connection); AddNetworks.ExecuteNonQuery(); } }
View 6 Replies
View Related
May 10, 2007
Evening everyone. I am having problems with my WHILE loops.
When I paste the following into the MS SQL query window I get an error message saying there is a problem near 'CURSOR'.
Code:
SELECT Course_ID, Course_Name
INTO CursorTable
FROM NorcatCourses
WHERE (Course_Name LIKE 'XNMM%') AND (Course_Name NOT LIKE '%Presentation')
ORDER BY Course_Name;
/* Declare the cursor */
DECLARE Cursor_original CURSOR
SCROLL
FOR
SELECT Course_ID, Course_Name
FROM CursorTable;
/* Declare the temporary cursor variables
* to be used
*/
DECLARE @Course_ID int;
DECLARE @Course_Name nvarchar(50);
/* Declare tepmorary variables for the cursor copy */
DECLARE @Course_ID_copy int;
DECLARE @Course_Name_copy nvarchar(50);
/* Open the cursor and fetch the first record */
OPEN Cursor_original;
FETCH NEXT FROM Cursor_original INTO @Course_ID, @Course_Name;
WHILE (@@FETCH_STATUS = 0) /* Outer Loop */
BEGIN
/* Declare a second copy of the cursor to be used
* for comparison
*/
DECLARE Cursor_copy CURSOR
SCROLL
FOR
SELECT *
FROM CursorTable;
/* Open Cursor_copy and fetch the first record */
OPEN Cursor_copy;
FETCH NEXT FROM Cursor_copy INTO @Course_ID_copy, @Course_Name_copy;
WHILE (@@FETCH_STATUS = 0) /* Inner Loop */
BEGIN
IF (@Course_Name LIKE '%' + @Course_Name_copy + '%') AND
(@Course_ID < @Course_ID_copy)
BEGIN
DELETE CursorTable
WHERE CURRENT OF Cursor_original;
END;
FETCH NEXT FROM Cursor_copy INTO @Course_ID_copy, @Course_Name_copy;
END; /* Inner Loop */
/* Clean up */
CLOSE Cursor_copy;
DEALLOCATE Cursor_copy;
FETCH NEXT FROM Cursor_original INTO @Course_ID, @Course_Name;
END; /* Outer Loop */
/* Clean up */
CLOSE Cursor_original;
DEALLOCATE Cursor_original;
/* DROP TABLE CursorTable; */
According to all of the tutorials I've read my syntax for a WHILE loop appears to be correct. Where am I going wrong?
Thanks.
View 1 Replies
View Related
Nov 19, 2007
Hi
I am using a foreach loop container and Data flow task dropped on it.
According to the logic the dataflow task should be executed twice and for eacl loop fetches 2 records in the variable used in the sql in Data flow task. It works fine except that it does not iterate twice.It simply executes once.
Please help
View 2 Replies
View Related
Apr 13, 2008
This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T (@PartNo varchar(20), @Wkorder varchar(10), @Setup datetime, @Line smallint, @TT integer, @Tester smallint, @LT1 integer, @LT2 integer, @LT3 integer, @LT4 integer, @LT5 integer, @LT6 integer, @LT7 integer, @LT8 integer, @LT9 integer, @LT10 integer, @LT11 integer, @LT12 integer, @LT13 integer, @LT14 integer, @LT15 integer, @LT16 integer, @LT17 integer, @LT18 integer, @LT19 integer, @LT20 integer, @LT21 integer, @LT22 integer, @LT23 integer, @LT24 integer, @LT25 integer, @LT26 integer, @LT27 integer, @LT28 integer, @LT29 integer, @LT30 integer, @LT31 integer, @LT32 integer, @LT33 integer, @LT34 integer, @LT35 integer, @LT36 integer, @UnitFound integer OUT, @parameters_LamType varchar(50) OUT, @parameters_Shunt real OUT, @parameters_ShuType varchar(50) OUT, @parameters_Stack real OUT, @parameters_Steel varchar(50) OUT, @Partno11 varchar(20) OUT, @Wkorder11 varchar(10) OUT, @Partno12 varchar(20) OUT, @Wkorder12 varchar(10) OUT, @Partno24 varchar(20) OUT, @Wkorder24 varchar(10) OUT, @Partno29 varchar(20) OUT, @Wkorder29 varchar(10) OUT, @Partno34 varchar(20) OUT, @Wkorder34 varchar(10) OUT, --@DL1 integer OUT, --@DL2 integer OUT, --@DL3 integer OUT, --@DL4 integer OUT, --@DL5 integer OUT, --@DL6 integer OUT, --@DL7 integer OUT, --@DL8 integer OUT, --@DL9 integer OUT, --@DL10 integer OUT, @DL11 integer OUT, @DL12 integer OUT, --@DL13 integer OUT, --@DL14 integer OUT, --@DL15 integer OUT, --@DL16 integer OUT, --@DL17 integer OUT, --@DL18 integer OUT, --@DL19 integer OUT, --@DL20 integer OUT, --@DL21 integer OUT, --@DL22 integer OUT, --@DL23 integer OUT, @DL24 integer OUT, --@DL25 integer OUT, --@DL26 integer OUT, --@DL27 integer OUT, --@DL28 integer OUT, @DL29 integer OUT, --@DL30 integer OUT, --@DL31 integer OUT, --@DL32 integer OUT, --@DL33 integer OUT, @DL34 integer OUT) --@DL35 integer OUT, --@DL36 integer OUT)ASSET @Tester = 1WHILE @Tester < 36 BEGIN Set @Line = (Select Line from dbo.location where Tester = @Tester) IF @Line = 453 BEGIN If @Tester = 1 BEGIN SET @LT1 = 453 END If @Tester = 2 BEGIN SET @LT2 = 453 END If @Tester = 3 BEGIN SET @LT3 = 453 END If @Tester = 4 BEGIN SET @LT4 = 453 END If @Tester = 5 BEGIN SET @LT5 = 453 END If @Tester = 6 BEGIN SET @LT6 = 453 END If @Tester = 7 BEGIN SET @LT7 = 453 END If @Tester = 8 BEGIN SET @LT8 = 453 END If @Tester = 9 BEGIN SET @LT9 = 453 END If @Tester = 10 BEGIN SET @LT10 = 453 END If @Tester = 11 BEGIN SET @LT11 = 453 END If @Tester = 12 BEGIN SET @LT12 = 453 END If @Tester = 13 BEGIN SET @LT13 = 453 END If @Tester = 14 BEGIN SET @LT14 = 453 END If @Tester = 15 BEGIN SET @LT15 = 453 END If @Tester = 16 BEGIN SET @LT16 = 453 END If @Tester = 17 BEGIN SET @LT17 = 453 END If @Tester = 18 BEGIN SET @LT18 = 453 END If @Tester = 19 BEGIN SET @LT19 = 453 END If @Tester = 20 BEGIN SET @LT20 = 453 END If @Tester = 21 BEGIN SET @LT21 = 453 END If @Tester = 22 BEGIN SET @LT22 = 453 END If @Tester = 23 BEGIN SET @LT23 = 453 END If @Tester = 24 BEGIN SET @LT24 = 453 END If @Tester = 25 BEGIN SET @LT25 = 453 END If @Tester = 26 BEGIN SET @LT26 = 453 END If @Tester = 27 BEGIN SET @LT27 = 453 END If @Tester = 28 BEGIN SET @LT28 = 453 END If @Tester = 29 BEGIN SET @LT29 = 453 END If @Tester = 30 BEGIN SET @LT30 = 453 END If @Tester = 31 BEGIN SET @LT31 = 453 END If @Tester = 32 BEGIN SET @LT32 = 453 END If @Tester = 33 BEGIN SET @LT33 = 453 END If @Tester = 34 BEGIN SET @LT34 = 453 END If @Tester = 35 BEGIN SET @LT35 = 453 END END SET @Tester = @Tester + 1 ENDSELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS A INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF WHERE B.PARTNO = @PARTNO SET @UnitFound = @@rowcountIF @UnitFound = 0 BEGIN SELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS WHERE PARTNO = @PARTNO SET @UnitFound = @@rowcount END --IF @LT1 = @Line BEGIN SET @DL1 = 1 END --IF @LT2 = @Line BEGIN SET @DL2 = 1 END --IF @LT3 = @Line BEGIN SET @DL3 = 1 END --IF @LT4 = @Line BEGIN SET @DL4 = 1 END --IF @LT5 = @Line BEGIN SET @DL5 = 1 END --IF @LT6 = @Line BEGIN SET @DL6 = 1 END --IF @LT7 = @Line BEGIN SET @DL7 = 1 END --IF @LT8 = @Line BEGIN SET @DL8 = 1 END --IF @LT9 = @Line BEGIN SET @DL9 = 1 END --IF @LT10 = @Line BEGIN SET @DL10 = 1 END IF @LT11 = 453 BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END --IF @LT11 = @Line BEGIN SET @DL11 = 1 END IF @LT12 = 453 BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END --IF @LT13 = @Line BEGIN SET @DL13 = 1 END --IF @LT14 = @Line BEGIN SET @DL14 = 1 END --IF @LT15 = @Line BEGIN SET @DL15 = 1 END --IF @LT16 = @Line BEGIN SET @DL16 = 1 END --IF @LT17 = @Line BEGIN SET @DL17 = 1 END --IF @LT18 = @Line BEGIN SET @DL18 = 1 END --IF @LT19 = @Line BEGIN SET @DL19 = 1 END --IF @LT20 = @Line BEGIN SET @DL20 = 1 END --IF @LT21 = @Line BEGIN SET @DL21 = 1 END --IF @LT22 = @Line BEGIN SET @DL22 = 1 END --IF @LT23 = @Line BEGIN SET @DL23 = 1 END IF @LT24 = 453 BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END --IF @LT25 = @Line BEGIN SET @DL25 = 1 END --IF @LT26 = @Line BEGIN SET @DL26 = 1 END --IF @LT27 = @Line BEGIN SET @DL27 = 1 END --IF @LT28 = @Line BEGIN SET @DL28 = 1 END IF @LT29 = 453 BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END --IF @LT30 = @Line BEGIN SET @DL30 = 1 END --IF @LT31 = @Line BEGIN SET @DL31 = 1 END --IF @LT32 = @Line BEGIN SET @DL32 = 1 END --IF @LT33 = @Line BEGIN SET @DL33 = 1 END IF @LT34 = 453 BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END --IF @LT35 = @Line BEGIN SET @DL35 = 1 END --IF @LT36 = @Line BEGIN SET @DL36 = 1 ENDGO
View 1 Replies
View Related
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
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
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
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