Getting The List Of Expressions From A Package
Jul 10, 2007
Does anyone have any ideas on quickly getting a list of all expressions used in a package? For the BIDSHelper add-in (http://www.codeplex.com/bidshelper) we need to get a list of all the expressions used in the package and update the list whenever an expression is added through the IDE. Currently, we are iterating through the package, and checking every property of every task to see if it has an expression. On big packages, this is slow, as you might imagine.
Any suggestions would be appreciated. Maybe one of our MVPs could flag this for Microsoft follow-up, to see if they have any suggestions.
View 4 Replies
ADVERTISEMENT
Jan 19, 2008
Hi,
Is there any way to group variables present in a select statement.
My code:
SELECT @var1=something, @var2=something_else
FROM ...
GROUP BY @var1
I wanted to group by 'something' hence I used a variable to assign it to. But I'm unable to group it.
Any thoughts?
Thanks,
Subha
View 1 Replies
View Related
Mar 6, 2007
Hi there,
sorry if the message seems a bit garbled i cannot see the textbox properly with ie7/ff
anyways. the situation.
childpackage contains a loop-container. in there i use an ole-db-source with a variable
based on an expression. ie. "select * from foo where bar=" + len(@[bar]) == 0 ? "initial" : @[bar] + " and etc=1"
where bar is the variable that is set by the loop.
this works great.
however i need to call this package several times, only the expression is a tad different.
so i need a scripttask that sets the expression correctly, then i call the childpackage
and map the current-expression to the expression in childpackage.
how do i do that? or am i doing it wrong?
my script-task looks something like:
dts.Variable("theVar").Expression = " ""select * from foo where bar="" + len(@[bar]) == 0 ? ""initial"" : @[bar] + "" and etc=1"" "
in the childpackage i have a package-conf that maps thevar to thequery with target-object expression.
View 1 Replies
View Related
Jun 24, 2007
Hi,
I have encountered some strange output when testing an SSIS package I've been working on.
Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.
The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.
However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.
I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!
I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.
Note:
I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).
Questions:
What's happening here, have I made a conceptual error of how this should work?
On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui??
Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem
Thanks for your support!!
View 2 Replies
View Related
Dec 11, 2007
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
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- 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
go
View 1 Replies
View Related
Dec 15, 2006
Because packages are not edited in msdb, there exists a chance that I might not always remember to re-import a modified package. So I would like to include "something" in the package that would write the date last changed of the package to the package log.
Do you have any suggestions that I could use now?
I tried the Script example in BOL and it did not write anything to the package log.
When the log entry is written for each step, include the elapsed time since the package started.
While the csv format is now the default, is it possible to have any other format?
I seem to remember in DTS there were some messages that were displayed in
Name:Value pairs. Sometimes this is more helpful and requires less horizonal scrolling.
Thanks,
IanO
View 1 Replies
View Related
May 16, 2006
While experimenting I got this message:
[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"
I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?
TIA,
Barker
View 1 Replies
View Related
Apr 11, 2008
I am getting an error when I try to create a Step in a SQL Agent Job and select the Type "SQL Server Integration Services Package". As soon as I select this type in the drop down box, I get the error message:
The specified module could not be found. (Exception from HRESULT: 0x8007007E) (SqlManagerUI)
It then takes me back to the Step window, but I do not get the option to select packages.
I was able to do this in the past, and I do have jobs set up to run packages, so this may be something related to "permissions" or recent upgrades. I am a sysadmin on the SQL Server, but I have limited rights on the Windows server (I'm not a Network admin). OR, could this be related to recent service packs or windows upgrades?
Any help appreciated.
View 18 Replies
View Related
Feb 14, 2008
Hi everyone!
I have a question regarding ASP.NET 2.0 reading available packages (calling "GetDtsServerPackageInfos" on the "Application"-Object which returns "Microsoft.SqlServer.Dts.Runtime.PackageInfos") from a remote SSIS server (not running them). It seems to me that I am experiencing either a security/delegation issue or that I am trying to do something that is not possible...
The setup is the following.
I have two machines, A and B. A runs IIS 6/ASP.NET 2.0 and B runs SQL Server and SSIS. They belong to the same domain. IIS runs the application pool with the webapplication in question under the "Network Service" account and A is trusted for delegation to B. At the moment its trusted when using Kerberos and for MSSQLSrvc and rpcss; but I tried "all" and many more as well.
I configured IE to use Windows Authentication and set the URL to belog to the Intranet (to enable kerberos authentication).
My ASP.NET webapplication now tries to read (in the sense explained above) the available packages on the SSIS machine (B). The packages are stored on the filesystem, not in mdsb. But I feel that this does not matter, since ASP.NET is accessing them through "the SSIS service" which can access packages in msdb or/and on the filesystem?
What happens now is that
a) everything works, when I fire up IE on A and access the webapplication. The user I have on A is a local admin.
b) I get "E_ACCESSDENIED" when I start IE as regular User on A
c) I get "E_ACCESSDENIED" when I access the webapplication from any other machine than A (IE6 or IE7)
My main question is: is it possible to do what I want to do? The other question is, why does everything work when I access the webapplication from A, but not from any other machine? The second thing makes me think it could be a delegation issue (double hop and SSIS don't seem to go togethe too well). But still, I could not find any resource telling me that this is impossible And frankly, I do not fully understand delegation and/or Kerberos.
I would greatly appreciate if anybody had a pointer or something for me.
Regards
scherand
Btw: Thanks to Caddre for his (her?) first help and pointing me to this forum.
View 20 Replies
View Related
Oct 26, 2006
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
View 6 Replies
View Related
May 26, 2015
I have an SSIS package created on SQL Server 2005. I have moved this to a SQL Server 2008 R2 server and amended the package on this new server to point at the correct databases.
The package runs manually. However, I cannot see the package when trying to schedule a job. The dropdown list does not contain the package.
I imported the package by right clicking on MSDB and selecting the package from the file system. The package then appears under this folder (SQL Server Integration Services). I then create the SQL job but cannot see the package I just created.
View 2 Replies
View Related
Dec 13, 2013
When I am trying to insert to data from SQL ssis package to SharePoint list people or group column I am getting below error.[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SharePoint List Destination" (25) failed with error code 0x80131500 while processing input "Component Input" (34). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
View 8 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Apr 18, 2007
HI...
I need to do a simple task but it's difficult to a newbie on ssis..
i have two tables...
first one has an identity column and the second has fk to the first...
to each dataset row i need to do an insert on the first table, get the @@Identity and insert it on the second table !!
i'm trying to use ole db command but it's not working...it's showing the error "Insert Value list doest not match column list"
here is the script
INSERT INTO Address(
CepID,
Street,
Number,
Location,
Complement,
Reference)Values
(
?,
?,
?,
?,
?,
?
)
INSERT INTO CustomerAddress(
AddressID,
CustomerID,
AddressTypeID,
TypeDescription) VALUES(
@@Identity,
?,
?,
?
)
what's the problem ??
View 7 Replies
View Related
Jan 20, 2005
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
View 3 Replies
View Related
Mar 21, 2008
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
How can I do this?
Insert Query:
insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE)
SELECT DISTINCT
tri_IDENT.IDA AS PPID,
tri_Ldg_Tran.CLM_ID AS PTicketNum,
tri_ClaimChg.Line_No AS PLineNum,
tri_Ldg_Tran.Tran_Amount AS PAmount,
CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT'
THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier
ELSE 0 END AS PPatPay,
tri_Ldg_Tran.Create_Date AS PDepositDate,
tri_Ldg_Tran.Tran_Date AS PEntryDate,
tri_ClaimChg.Hsp_Code AS PHCPCCode,
tri_Ldg_Tran.Adj_Type,
tri_Ldg_Tran.PRS_ID,
tri_Ldg_Tran.Create_Time,
tri_Ldg_Tran.Adj_Group,
tri_Ldg_Tran.Payer_ID,
tri_Ldg_Tran.TRN_ID,
tri_ClaimChg.Primary_Claim,
tri_IDENT.Version
FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN
qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc =
qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN
tri_Ldg_Tran RIGHT OUTER JOIN
tri_IDENT LEFT OUTER JOIN
tri_ClaimChg ON tri_IDENT.Pat_Id1 =
tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID =
tri_ClaimChg.PRS_ID AND
tri_Ldg_Tran.Chg_TRN_ID =
tri_ClaimChg.Chg_TRN_ID
AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN
tri_Payer ON tri_Ldg_Tran.Payer_ID
= tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type
= tri_Ldg_Tran.Pay_Type AND
qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type
WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg')
AND (tln_PaymentTypeMappings.PTMClientCode = 'SR')
AND (tri_ClaimChg.Primary_Claim = 1)
AND (tri_IDENT.Version = 0)
View 2 Replies
View Related
Feb 12, 2008
Hi,
I have a a table that holds a list of words, I am trying to add to the list, however I only want to add new words. But I wish to return from my proc the list of words with ID, whether it is new or old.
Here's a script. the creates the table,indexes, function and the storeproc. call the proc like procStoreAndUpdateTokenList 'word1,word2,word3'
My table is now 500000 rows and growing and I am inserting on average 300 words, some new some old.
performance is a not that great so I'm thinking that my code can be improved.
SQL Express 2005 SP2
Windows Server 2003
1GB Ram....(I know, I know)
TIA
Code Snippet
GO
CREATE TABLE [dbo].[Tokens](
[TokenID] [int] IDENTITY(1,1) NOT NULL,
[Token] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Tokens] PRIMARY KEY CLUSTERED
(
[TokenID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tokens] ON [dbo].[Tokens]
(
[Token] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[SplitTokenList]
(
@TokenList varchar(max)
)
RETURNS
@ParsedList table
(
Token varchar(255)
)
AS
BEGIN
DECLARE @Token varchar(50), @Pos int
SET @TokenList = LTRIM(RTRIM(@TokenList ))+ ','
SET @Pos = CHARINDEX(',', @TokenList , 1)
IF REPLACE(@TokenList , ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Token = LTRIM(RTRIM(LEFT(@TokenList, @Pos - 1)))
IF @Token <> ''
BEGIN
INSERT INTO @ParsedList (Token)
VALUES (@Token) --Use Appropriate conversion
END
SET @TokenList = RIGHT(@TokenList, LEN(@TokenList) - @Pos)
SET @Pos = CHARINDEX(',', @TokenList, 1)
END
END
RETURN
END
GO
CREATE PROCEDURE [dbo].[procStoreAndUpdateTokenList]
@TokenList varchar(max)
AS
BEGIN
SET NOCOUNT ON;
create table #Tokens (TokenID int default 0, Token varchar(50))
create clustered index Tind on #T (Token)
DECLARE @NewTokens table
(
TokenID int default 0,
Token varchar(50)
)
--Split ID's into a table
INSERT INTO #Tokens(Token)
SELECT Token FROM SplitTokenList(@TokenList)
BEGIN TRY
BEGIN TRANSACTION
--get ID's for any existing tokens
UPDATE #Tokens SET TokenID = ISNULL( t.TokenID ,0)
FROM #Tokens tl INNER JOIN Tokens t ON tl.Token = t.Token
INSERT INTO Tokens(Token)
OUTPUT INSERTED.TokenID, INSERTED.Token INTO @NewTokens
SELECT DISTINCT Token FROM #Tokens WHERE TokenID = 0
return the list with id for new and old
SELECT TokenID, Token FROM #Tokens
WHERE TokenID <> 0
UNION
SELECT TokenID, Token FROM @Tokens
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @er nvarchar(max)
SET @er = ERROR_MESSAGE();
RAISERROR(@er, 14,1);
ROLLBACK TRAN
END CATCH;
END
GO
View 5 Replies
View Related
Jun 7, 2006
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:
(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)
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.)
Any thoughts?
View 6 Replies
View Related
May 25, 2007
How do I add comments to expressions I create in Reporting Services?
View 3 Replies
View Related
Aug 7, 2007
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?
View 2 Replies
View Related
Oct 31, 2007
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!
View 3 Replies
View Related
Jun 24, 2008
Hi, the Microsoft SQL Server version is 2000
Basically I want use a basic regular expression but can't seem to get the syntax right.
i want to compare ID_short = ID_Long
ID_short is a truncated version of ID_Long, and I want to search on the beginning only (hence I can't use the 'LIKE' comparative on it's own).
What is the syntax to use Reg Expressions (or if anyone knows a non RegExp way of searching the beginning please let me know).
Thanks
View 5 Replies
View Related
Apr 16, 2007
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] + "')"
What would the code look like?
View 4 Replies
View Related
Jun 5, 2007
Hey everyone,
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.
-Keith
View 1 Replies
View Related
Apr 16, 2007
Good Morning all.
Is it possible to put multiple expressions in one cell.
Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))
=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))
Desired output will look sim to this in one cell
Y = 5
N = 3
NA = 0
Thanks,
Rick
View 4 Replies
View Related
Jun 16, 2007
Is there any way to write C# code in SQL Server Reporting Services?
View 1 Replies
View Related
May 23, 2006
Greetings!
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,
before it goes to the destination column?!
Thanks for your help in advance.
View 11 Replies
View Related
Jun 30, 2007
Hello,
When i select jan month i should display december and previous year using expressions. plz advice me.
View 1 Replies
View Related
Nov 12, 2007
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
=iif((Fields!RevenueGbp.Value = 0.00), "Zero", (Fields!Commission.ValueGbp/Fields!RevenueGbp.Value))
However this does not work. However if i tried the following Boolean query
=iif((Fields!RevenueGbp.Value = 0.00), "Zero", Fields!CommissionGbp.Value)
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!
Anyone help?
View 4 Replies
View Related
Jan 14, 2008
Hi,
I have the following expression:
@[User::varLogFolder] +"\"+ "CollectTrnsRxaFecPagLog.txt"
but when I evaluated it I get:
\testteamserverMigrationLog\CollectTrnsRxaFecPagLog.txt
As you can see I´m trying to create a folder but it keeps the double slash, how can I fix it?
thanks.
well the real expression is:
@[User::varLogFolder] +"\"+ @[System:ackageName] +"\"+replace(replace(@[User::varFilesCollect],"\","" ),".","")+"\"+"CollectTrnsRxaFecPagLog.txt"
and the error is because:
[CollectTrnsRxaFecPagLog [4712]] Information: The processing of file "\testteamserverMigracionLog\PCKG_BH_COLLECT estteamservermigraciondatamcli010001rxaCollectTrnsRxaFecPagLog.txt" has started.
the yellow characters are the conflict!!
View 3 Replies
View Related
Sep 19, 2007
Hello there,just thinking something.I have a matrix which have values retrieve from a database.I had make the value goes like this
=Sum(Fields!Allocated.Value) & " [" & Sum(Fields!Allocated.Value) & "]"
But now,i am curious if ever theres a way where i can make the value look like this in the matrix cell
Actual Value -> 0.5 [1.3] Is this possible??(It is in the same cell)
Thanks Guys!!
View 2 Replies
View Related
Apr 11, 2007
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.
Any help appreciated.
Thanks
View 35 Replies
View Related
