What would be the correct syntax if I wanted to add the following lines into a variable using an expression? The lines should be the first two rows before my XML.
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
+@[User::xml_output]
Thanks,
Phil
Hi,
This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).
What I WANTED to do is a straight character check:
@[User::xml_output] == "ABC"
but that wasn't working... so I deceided to try the len function.
However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.
len(trim(DT_WSTR,18,1252)@[User::xml_output])) > 17
len(trim(@[User::xml_output])) > 17
(DT_WSTR,18)@[User::xml_output] > 17
I just want to trim @[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.
Thanks,
Phil
Hi,
I'm having some trouble with this syntax:
@[User::xml_output] != "<ROOT></ROOT>"
I have a variable that I KNOW is = '<ROOT></ROOT>' and I've set up a precendence constraint to only go to the next step if the above not true, correct?
Do I need to convert the right side of the equation to a string or something first? Does anybody know that syntax off hand?
Thanks,
Phil
How would I do a select from a container using the previous container's starttime as a condition in the variable?
select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @[System::ContainerStartTime] + "
Thanks,
Phil
what is the syntax for IN in Expression?
I am tryin to do @ID IN (1,2,3,4) then return 1 else 0...
I have a variable that I'm storing in a database. The variable is a server name "servernamepub". When I watch this variable in the locals it becomes "servername\pub". Is there something going on in the expression/variable syntax which is changing this?
Thanks,
Phil
Hi,
I would like to know how I can indicate a NULL value in a report expression in SSRS / Report Designer.
I am trying to code :
IIF(Value_A = 0, <NULL>, Value_A)
It may look weird but I am trying to return NULL values when Value_A is 0 (zero), in the sample scenario above.
I have tried using the keyword "NULL", but it is highlighted as a syntax error, and suggested to use System.DBNull. I tried it and then it says that components of the System collection cannot be used in an expression, so I am left drawing blanks.
Thanks.
regards,
Kenny
One of my measures is in seconds and I would like to convert it to hours.
It is simply a number in decimal format
the expression I tried is this
[Measures].[OPEN DURATION] /60
and I ge this message
Error 1 Errors in the metadata manager. The measure expression of the OPEN DURATION measure contains the [(60)] operand , which could not be resolved. 0 0
All help is appreciated, thank you in advance. I have tried a couple different syntax's with no luck.
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
View 1 Replies View RelatedI'm getting the error listed above. To create this I used the wizard in VS2005 for the datagrid. The delete works but not the updating. I can't seem to find what's wrong. In the update parameters I've removed eventID and added it, neither works. Delete does work. Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>
Chaps,
apologies for the drip-drip approach......
Is it possible to do pattern matching against a string using FINDSTRING or similar in a derived column expression without recourse to including 3rd party regexp style plugins?
I want to seach for a reference in a string which will have the format ANNNNNAAA ie. an alpha with a certain value followed by 5 digits followed by three alphas with specific values.
eg Z00001YYY or X00022HHH
thanks for your assistance,
regards,
Chris
I am using an oledb source. the query is coming from a variable. The database to which oledb source is connected is Oracle.Mt variable contains the following query:
"SELECT REQUEST_ID FROM COMPLIANCE_REQUEST
where LOAD_TMSTP between (select max(END_TMSTP) FROM BATCH_JOB_LOG) and
TO_DATE("'+RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System:tartTime] ), 2) + "-"+RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System:tartTime] ), 2) + "-" +RIGHT("0" + (DT_STR,4,1252)DATEPART( "yy" , @[System:tartTime] ), 2) + " " +RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System:tartTime] ), 2) + "." +RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System:tartTime] ), 2) +'",'DD-MM-YY HH24.MI.SS')"
I am getting error as :
Error at Data Flow Task [OLE DB Source 2 [2177]]: No column information was returned by the SQL command.
Error at Data Flow Task [OLE DB Source 2 [2177]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4A.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E4A Description: "Command was not prepared.".
Error at Data Flow Task [OLE DB Source 2 [2177]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Hi,
I have a variables in SSIS:
- object MyObj
How can I write an expression that checks if MyObj is NULL or NOT NULL?
Thank you.
Hello,
I have a DateTime variable called CurrentDate that needs to reflect the current date, but the date portion only. I checked several functions in the Expression Builder to use with GETDATE() so that I could just get the date portion, but I didn't see anything that really fit. In a SQL query I would normally use CONVERT to do this.
Any ideas?
Thank you for your help!
cdun2
Hi everyone,
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S1' THEN 'Other sale items'
ELSE 'Not for sale'
END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
Thanks for all your help,
regards,
Chris
Hi all of you,
That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.
Something like that:
'La tabla "' + SUBSTRING( @[System:ackageName], 3,20) + "' se ha cargado correctamente'
TIA for that,
Hi
I have some SSIS variables of type System.Object (they have to be this
type because they are used to hold the results of a single row result
set in an Execute SQL task which is querying an Oracle database.
Although I know the Oracle table columns are Numeric, this was the only
SSIS type that worked).
My problem is that I want to use these variables in expressions, but
can't - I get the error "The data type of variable "User::varObjectVar"
is not supported in an expression".
The only workaround I can think of is to use a script to assign
the numeric values (integers, in fact) that these variables hold to
other variables of type Int32.
Is that my only option, or am I missing something?
thanks
- Jerzy
Hello Friends,
I am new to SSRS and i want to define a Variable to one of the expression of my ssrs report. Can anyone help me to solve this issue.
I am using a matrix in the report and in that matrix in each box i am using some expression to get the value. So i need to define a variable .. Is it possible to define a variable ?
I have been successful using comments (to help remind me that the SQL is coming from a varaible which is set via an expresion) within the variable expression passed to a Source or Destination. I have been unable to get this to work in an ExecuteSQL control flow task when parameters are used within the query. I am not sure what is causing this not to work the fact that it is an ExecuteSQL control flow task or that the SQL has parameters (or perhaps that it also uses an OUTPUT parameter).
Any thoughts to share? Am I missing something?
WORKS (In Data Flow SQL):
"--NOTE: This is defined by an expression on Source_AdventureWorks_Customers_SQL variable
SELECT
[CustomerID],
[TerritoryID],
[AccountNumber],
[CustomerType],
[rowguid],
[ModifiedDate]
FROM
[AdventureWorks].[Sales].[Customer]"
FAILS (in Control Flow ExecuteSQL with parms including Output parm):
"--NOTE: This is defined by an expression on Source_AdventureWorks_Customers_SQL variable
[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = ?,
@Description = ?,
@PackageName = ?,
@PackageGuid = ?,
@PackageVersionMajor = ?,
@PackageVersionMinor = ?,
@PackageVersionBuild = ?,
@MachineName = ?,
@ExecutionGuid = ?,
@LogicalDate = ?,
@StartTime = ?,
@Operator = ?,
@ETLAuditKey = ? OUTPUT"
WORKS (in Control Flow ExecuteSQL with parms including Output parm):
"[audit].[up_LogEtlPackageStart]
@ETLAuditParentKey = ?,
@Description = ?,
@PackageName = ?,
@PackageGuid = ?,
@PackageVersionMajor = ?,
@PackageVersionMinor = ?,
@PackageVersionBuild = ?,
@MachineName = ?,
@ExecutionGuid = ?,
@LogicalDate = ?,
@StartTime = ?,
@Operator = ?,
@ETLAuditKey = ? OUTPUT"
I'm trying to do something very simple, and having a tough time with it.
I've got a Global Variable that gets a string value assigned to it in a Script Task, and then I need to access that value in Execute Process Task Expression. When running, by the time it gets to the Process Task, the global variable's value in the expression is still blank, even though a breakpoint on the task shows that it does have a value.
What am I doing wrong? This seems too simple to give me this much problem.
Greetings my SSIS friends
I am attempting to create an expression as follows:
"Select * from someTable where someColumn >= " + (dt_str, 25, 1252) @[User::DateTimeVariable]
The problem is that my variable is a Datetime field and when I convert it to string, the string will not execute correctly.
How to solve this problem?
First and foremost, I'm officially and thoroughly confused between Variable, Expression and settings. I need help clearify this up for me.
I'm going to use the Flat File Connection as an example.
I have:
> A global variable named: FileToImportFullPath (string) that stores the full path of the file I want to import. Note: the file path will change has the package executes (the location of the file depends on a number of conditions, namely the Date and Time of the package execution)
> A Flat File Connection Manager used by a Flat File source in the package's main Data Flow
So ideally, I want the Flat File Connection Manager's ConnectionString to be set to whatever the value of FileToImportFullPath variable is at the time. To accomplish this, I set the ConnectionString Expression to equal to FileToImportFullPath. First question, if I set the ConnectionString Expression, is it okay for me to leave the ConnectionString property (i.e. in the Editor or the Property Editor) blank? Second, whenever I leave the ConnectionString property blank, I will get a warning stating: "A valid file name must be selected". Since this is a warning, I ignored, but during exeuction, the value really is blank. Also, I'm 100% certain that the FileToImportFullPath variable is set correctly before the Data Flow step is executed.
All in all, I'm just confused if an object's property must be set if there is already an express for it (e.g., in the file move task, the source and the destination properties).
Thanks,
Jason
So if my Parameter value is...
MCCL, MCMA, MCPL, MCPR, MCRE, MCSE, MCSN, MCVA, MCCL, MCEP, MCMA, MCRE out of 43 possible choices,
What is the expression syntax to hide my tablix column based on my parameter value?
=iif(instr(Join(Parameters!ColumnVisibility.Value,","),"MCCL","MCMA","MCPL"...)>0,false,true)
Just not sure of the syntax...or do I have to qualify each one???
= IIf(Parameter!PF1.Value = "MCCL" OR Parameter!PF1.Value = "MCMA"..., TRUE, FALSE)
I have created a variable in a SQL Task and assigned it to a string variable. When I debug the container with a breakpoint, I can see the correct date value being assigned to the variable.
I have an ADO Net source setup to an Oracle connection. I need to pull the Oracle data down that has an updated date greater than the updated date in my ODS.
My issue is that the variable is not being passed through to my expression that I use for an ADO Net source.
"SELECT * FROM BI_EDW.GL_JE_HEADERS WHERE LAST_UPDATE_DATE > To_Date('" + (DT_WSTR, 19) @[User::varLastUpdateDate] + "','yyyy-mm-dd hh24:mi:ss')"
I have tried with this thing for hours and I can't get it to parse.
Trying to get last year so the expression should evaluate today as of "12/31/2014"
I looked all over on Google and couldn't find an expression to copy so I've been playing with it.
Here is what it is in SQL Server:
SELECT DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,GETDATE()),0))
Absolutely could not get it to parse..even with DT tags, etc...
I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.
SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')
This works perfect when running in T-SQL, but when attempting to define a variable in the expression
REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?
Anyone run into a simliar issue or have a suggested work around?
Greetings once again SQL friends,
Sorry to bug you with my silly questions but this little problem has been driving me crazy for the last hour or so!
I am attempting to change the data type of a variable in an expression but I keep getting an error saying that the expression can not be evaluated.
The syntax I am using is as follows :
(dt_str) @[User::MAX_OFFER_PRICE_ID]
My variable MAX_OFFER_PRICE_ID is type int32 and I am attempting to cast it as a string.
What am I doing wrong?!
Thanks for your help in advance.
I have an integer variable which i had to cast as a string in order for it to show as an integer in the sql expression which is a string in order for it to INSERT as an integer? Read that 5 times fast!!!
"INSERT INTO Table( SourceFileName, ProcessStartTime, ProcessEndTime, Processed, RowCountFlatFile, RowCountCleanInsert, RowCountDataConversionErrors, RowCountPKViolation, RowCountDuplicateInvoiceIDInSourceFile, RowCountISNULLCase1, RowCountISNULLCase2, RowCountConditionalSplit)
VALUES( '"+ @[User:aymentFileName] +"',
'"+ (DT_STR, 20, 1252)@[System:tartTime]+ "',
'"+ (DT_STR, 20, 1252)@[System:tartTime]+ "',
1,
"+(DT_STR, 4, 1252)@[User::rcSourceFile]+",
"+(DT_STR, 4, 1252)@[User::rcCleanInserts]+",
"+(DT_STR, 4, 1252)@[User::rcDataConversionErrors]+",
"+(DT_STR, 4, 1252)@[User::rcPKViolation]+",
"+(DT_STR, 4, 1252)@[User::rcExistingInvoiceID]+",
"+(DT_STR, 4, 1252)@[User::rcISNULLCase1] +",
"+(DT_STR, 4, 1252)@[User::rcISNULLCase2] +",
"+(DT_STR, 4, 1252)@[User::rcConditionalSplitErrors]+"
)"
all the variable beginning with rc are Int32 variables being loaded using the Row Count task. they are being inserted into an integer field in the table. Is this whack or what???
I was trying to cast is using DT_I4 but couldn't find any samples nor could I get it to work. The above is successful!!!
I have one package that executes 4 child packages (5 total). All 5 packages are set to log information using a connection string with a variable set at runtime for the location. The child packages use a parent variable to get the log connection string from the parent package.
The parent package logs fine, but a strange behavior occurs with the child packages.
The child packages all log data to the location used in the configuration file variable during runtime (like it's supposed to). However, I would get an error right before the first child package finishes execution saying path not found. Just for giggles, I created the folder I use in my development environment on the production environment. The error goes away, but the log file is created in that folder with no data in it. Subsequently, the log file with the data is created in the location set in the configuration file. Now I have two log files!!!!
Anybody know how to fix this?!
Hi
We have set up an SSIS package which goes to an FTP site and downloads files.
Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.
However, I want the package to continue to run.
Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?
thanking you in advance
David
Hi guys,
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine
WHEN 'R1' THEN 'Road'
WHEN 'M1' THEN 'Mountain'
WHEN 'T1' THEN 'Touring'
WHEN 'S2' THEN 'Other sale items'
ELSE 'Not for sale'
END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
thanks for any assistance you can provide.
regards,
Chris
I am having this error when using execute query for CTE
Help will be appriciated
I have been using SSIS for a while now, originally in SQL 2008 but more lately SQL 2012.
I discovered the GETDATE() function in SSIS so I thought I would use this in a variable expression in a Master/Driver package with the child parameters mapped to this variable. A big mistake. The value is not persisted, it gets updated each time the variable is read, so it's back to setting the variable value using a script task in the Master/Driver package.