Variable/Expression/Property Confusion
Oct 9, 2006
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
View 4 Replies
ADVERTISEMENT
Jan 22, 2008
Hello,
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
Does anyone have a clue as to what is going on??
Mike
View 1 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. 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.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
May 7, 2008
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
View 10 Replies
View Related
Feb 8, 2007
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
"C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls
as connectionString works
However - I'm trying to deploy the package - and trying to use expression:
@[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls"
which causes the same error to occur
(Same error with other Excel source also:
Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
)
View 4 Replies
View Related
Jan 10, 2006
Hi,
I am trying to use an OLE DB Command to run a different SQL command for each row in the data flow. I have a script component that builds the SQL command and puts it in a data flow variable, and a property expression in the data flow mapping [OLE DB Command].[Sql Command] to that variable.
The problem is that the OLE DB Command has a validation error, saying that "the command text was not set for the command object", and it doesn't run.
Did I miss anything? should I tell the OLE DB Command that the SQL command would come from expression? or is it a bug?
Thanks.
View 5 Replies
View Related
Oct 30, 2007
I have run into what appears to be a strange bug (or I am expecting too much) in SQL Reporting Services.
I have a field with a dollar amount. If the dollar amount = 0, then the users want "NA" to display. I have this set-up correctly.
I have now found that if the dollar amount is displayed, they want the text box right aligned, and if the "NA" is displayed, then they want the text box to be center aligned.
I put the following expression in the "TextAlign" property setting...
Code Block
=IIF(Field.value = 0, Center, Right)
Now the IIF function is available in my expression window, and so is "Center" and "Right". But when I try to run the report, I get the error that "Center" is not declared.
So maybe this just is not possible in SQL Reporting Services, but I wanted to check before I told the users "no can do".
BTW, I tried "Left", "Right", and "General" in place of "Center" and got the same error.
Thank you,
T.J.
View 5 Replies
View Related
Nov 14, 2006
Hello,
We're trying to launch a SSIS package throw a webservice. This WebService loads a configuration file with "myPackage.ImportConfigurationFile(pathConfig)" and assigns values to some variables with "myPackage.Variables.Item(Parameters.Keys.Item(i)).Value = Parameters.Item(i)". Seems to work.
I we execute the webmethod of the WebService, this works but only when we execute first time, the SSIS finished succesfully. But when we execute a second time the Webmethod, we've a lot of failure with messages:
The result of the expression "@[User::Email]" on property "ToLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.The result of the expression ""The Import of " + @[User::ExcelFile]+ " is successful"" on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Thx for your help
View 1 Replies
View Related
Feb 13, 2007
Hi,
I want to change expression of a variable but dont see ellipses icon in expression. The variable property EvaluateAsExpression is assigned TRUE.
I have reinstalled SSIS...i am sure there was a icon to open Property Expression Editor for a variable...dont see it anymore.
-Amar
View 3 Replies
View Related
Apr 11, 2008
Hi all,
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
Thanks!
View 6 Replies
View Related
May 16, 2007
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.
View 1 Replies
View Related
May 16, 2007
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated.
Thanks.
View 6 Replies
View Related
Jul 18, 2007
[Microsoft follow-up]
Hello,
I think I've found a bug.
I have an expression set on the 'Disable' property of a data-flow task. When the expression evaluates to true the data-flow still executes. That seems wrong to me.
I ahve a demo package here: http://folders.live.com/self.aspx/VQ9oHa1TJjc/Public/SSIS%20Stuff/ExamplePackages/20070717%20Expression%20on%20Disable%20property%20has%20no%20effect/Package1.zip
Please reply letting me know whether this is indeed a bug or not.
I'm on SP1 by the way.
Regards
Jamie
View 9 Replies
View Related
Jun 7, 2007
I have a matrix table with a rectangle in the data cell. The rectangle has an image and textbox. The textbox has an expression in it's Hidden property based on the column name. The report renders fine on screen. When the report is exported to Excel, CSV, XML the textbox contents are not output (the images display as expected). I've tried setting the DataElementOutput to Output/Yes with no success. Exporting to TIFF, PDF, Web Archive/MTHML is fine.
Here is a sample RDL which exhibits the issue:
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Gemini50DataSource">
<DataSourceReference>Gemini50DataSource</DataSourceReference>
<rdataSourceID>bb03313c-48a4-4e40-af99-ed584847ca20</rdataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="ImagePath">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>c:</Value>
</Values>
</DefaultValue>
<Prompt>Image Path</Prompt>
</ReportParameter>
</ReportParameters>
<rdrawGrid>true</rdrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rdnapToGrid>true</rdnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>3.75in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.75in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="PicIndex">
<rdefaultName>PicIndex</rdefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!PicIndex.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_PicIndex">
<GroupExpressions>
<GroupExpression>=Fields!PicIndex.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ColumnName">
<rdefaultName>ColumnName</rdefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ColumnName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnName">
<GroupExpressions>
<GroupExpression>=Fields!ColumnName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Width>5.5in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rdefaultName>textbox1</rdefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.55208in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.30208in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Image Name="image1">
<Sizing>AutoSize</Sizing>
<Left>0.25in</Left>
<MIMEType />
<ZIndex>1</ZIndex>
<Visibility>
<Hidden>=IIF(First(Fields!ColumnName.Value = "image"), Len(Fields!CellValue.Value)=0, true)</Hidden>
</Visibility>
<Width>0.3in</Width>
<Source>External</Source>
<Style />
<Value>="file:" + Parameters!ImagePath.Value + Fields!CellValue.Value</Value>
</Image>
<Textbox Name="textbox2">
<Left>1.625in</Left>
<DataElementOutput>Output</DataElementOutput>
<rdefaultName>textbox2</rdefaultName>
<Visibility>
<Hidden>=IIF(Fields!ColumnName.Value <> "image", False, True)</Hidden>
</Visibility>
<Width>1.875in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>PeachPuff</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>=Fields!CellValue.Value</Value>
</Textbox>
</ReportItems>
<Visibility>
<Hidden>=IIF(True, False, True)</Hidden>
</Visibility>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</Rectangle>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.625in</Height>
</Body>
<rd:ReportID>a944d20c-558a-4805-9d4c-aecc9757f678</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>SELECT 1 as PicIndex, 'image' as ColumnName, 'image1.jpg' as CellValue
union
SELECT 2,'image','image2.jpg'
union
SELECT 3,'image','image3.jpg'
union
SELECT 4,'image',null
union
SELECT 5,'something else',null
union
SELECT 6,'another column', 'display my text!'</CommandText>
<DataSourceName>Gemini50DataSource</DataSourceName>
</Query>
<Fields>
<Field Name="PicIndex">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>PicIndex</DataField>
</Field>
<Field Name="ColumnName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ColumnName</DataField>
</Field>
<Field Name="CellValue">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CellValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5.625in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
View 2 Replies
View Related
Oct 10, 2007
how do you store a datareader propety to a variable? Below is my current code. I have already declared my connectionString and sqlComm objects, as well as the userName and such. I need to store the value from the dr to the variables, UserName, UserPass, and serverName. ThanksTry
sqlCon.Open()
dr = sqlComm.ExecuteReader
While dr.Read
userName = dr("uName").ToString
LogInfo("userName = " & userName)
userPass = dr("uPass").ToString
LogInfo("userPass = " & userPass)
serverName = dr("sName").ToString
LogInfo("serverName = " & serverName)
End While
dr.Close()
Catch obug As Exception
LogEvent("Credentials Error: " & obug.Message)
Finally
sqlCon.Close()
End Try
View 3 Replies
View Related
Jul 4, 2007
I have a text file that is being insert into a table in a remote db. I have a dev server named dbname_trunk and a production server named dbname. The dataflow task refers to
[dbname_trunk]..[temp_ActivitiesAccessControlListUsers]
On the production version I would want the above to be:
[dbname]..[temp_ActivitiesAccessControlListUsers]
Is it possible to put the following into the OpenRowset property of the DataFlowComponent's custom properties?
"[" + @[User:bName] + "]..[temp_ActivitiesAccessControlListUsers]"
Actually, I know that doesn't work. What I need to know is what would work to accomplish my purpose. Ultimately, I would like to put the value in the configuration file.
Thanks
View 1 Replies
View Related
Jul 23, 2015
Where do you set an SSIS EvaluateAsExpression property of the variable to TRUE?
[URL]
View 3 Replies
View Related
Apr 2, 2007
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
View 5 Replies
View Related
Jan 12, 2012
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.
View 6 Replies
View Related
Jun 14, 2007
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.
View 6 Replies
View Related
Aug 1, 2007
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
View 4 Replies
View Related
Feb 27, 2007
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
View 5 Replies
View Related
Jul 19, 2007
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,
View 1 Replies
View Related
Jan 25, 2006
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
View 6 Replies
View Related
Mar 10, 2008
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 ?
View 4 Replies
View Related
Nov 16, 2007
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"
View 7 Replies
View Related
Jul 20, 2007
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.
View 4 Replies
View Related
Jun 7, 2006
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?
View 3 Replies
View Related
Aug 16, 2006
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
View 3 Replies
View Related
May 22, 2014
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')"
View 2 Replies
View Related
Aug 4, 2015
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...
View 9 Replies
View Related
Mar 27, 2007
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?
View 7 Replies
View Related
May 18, 2006
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.
View 7 Replies
View Related
