I need to create a While Loop with the FOR LOOP task.
According to my SSIS book, "to set up a While Loop in the For Loop, simply create an expression that tests a variable that can be modified by some workflow either inside or outside the For Loop"
What I want to do is, put an FTP task (whose job it is to receive a file) inside the For Loop.
I want the control flow to go like:
FOR LOOP --> FTP task --> file received? --> NO --> set variable 0 --> FOR LOOP --> check for variable --> is variable 1? --> NO --> FOR LOOP --> FTP task
and so on, until the variable is set to 1, indicating the successful transfer of the file
According to my book, this can be done simply with the use of expressions.
My question is, how do I write the expressions, both for the FTP task (which will set the variable to 0 or 1), and then the expression for the For Loop, which will evaluate the variable in its expression?
So, seems like I need to write 2 expressions, but not sure how.
Dear All! I have a "Foreach Loop Container" to search files in a folder. And I only want to search .txt and .txt.gz file. I set property "FileSpec" as "*.txt,*.txt.gz" but my container dont run. Can you help me? Thanks
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
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 I€™m 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.
When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.
Am I correct? If so... why not?
Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())
I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:
Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)
I have a table CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL) We will use these 3 queries select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage I want to make a method which handles these queries. But at the back end on Stored Procedures We have to write 3 queries Which I don't want to do. I want to minimize the queries and conditions and want to just write one for these 3 Can any one do it?
Hello all, I have come across a road block once again and it is probably something fairly easy to do. I have 3 queries that give counts with different criteria. They are: SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_scores = 'N') GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_asmt = 'N') GROUP BY building ORDER BY building My question is how can I combine these three queries into one so that I only get one record set? Thanks for all the help!
I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
I know that you can make an expression that will make it one color if a certain condition is met and a different one if it is not but is there anyway to make it so that if a number is less than another it's one color, if it's greater it's a different color and if they're equal it's a third color? Thanks for the help.
I am attempting to implement the following case statement BEFORE getting the data in to my destination table but I don't know how to create an expression for it.
In the mapping section of my OLE DB destination component I can only do mapping but I can't actually manipulate the data before it gets to the destination table.
What do I have to do to implement :
case when SOPD.PRICE_TOP_NUMBER is NULL then -8 else SOPD.PRICE_TOP_NUMBER end AS price_top_number,
I have a unusual problem, well it is unusual becaue i cannot understand it.
I am retrieving the following data, Revenue, Sales, Commission, and calculating Commission Rate in the report itself.
I got warnings that a textbox that was attempting to calculate the Commission Rate was trying to divide by zero.
When I analysed the data, I realised that indeed there were instances when Revenue was 0.00 and therefore when calculating the commission rate, commission/revenue it returned an error.
I then thought I'd do the following in the expression field
it does work. In fact if I use any other identifier or use two field thats add, subtract or multiplied with each other it will work. The problem only arises is when I decide to divide two fields together.
Strangely if the condition is satisfied - [Fields!RevenueGbp.Value = 0.00] - it will still try to work out the division even though it should just return Zero!
[CollectTrnsRxaFecPagLog [4712]] Information: The processing of file "\testteamserverMigracionLog\PCKG_BH_COLLECT estteamservermigraciondatamcli010001rxaCollectTrnsRxaFecPagLog.txt" has started.
I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.
I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.
In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:
SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue
which is how the formula reads in the Access view.
However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:
(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue
Which gives me an incorrect value.
Does anyone know why this is happening, or am I just unaware of the right way of doing it?
I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).
Can I use the following expression: GRID_NBR BETWEEN 1 AND 99
Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100
I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.
I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?
Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.
I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).
I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.
Trying to write the below in an expression box, I still have not figured out an equivalent to €œIN€? in SQL syntax, so far I€™ve had to use €œOR€? to get it to work. Anyone know the right way? I€™m trying to avoid writing this in the stored proc
Example:
Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")
Does anybody know a reliable source for common Expressions and Syntax rules used in RS2000 or RS2005? This is my third day chipping away at RS and it's been challenging so far. thx, John
