I'm trying to use an SSIS Variable USER::iYYYYMM as an int in a dataflow task. I sue the query SELECT iAutoRebillID, iYYYYMM, iAccNum, vcProcStat, sdtProcStatDate, dtInitTime, iUpdUserID, dtUpdTimeFROM tbAutoRebill WITH (NOLOCK)WHERE (iYYYYMM <' @[USER::iYYYYMM]') but get the following message TITLE: Microsoft Visual Studio------------------------------ There was an error displaying the preview. ------------------------------ADDITIONAL INFORMATION: Conversion failed when converting the varchar value ' @[USER::iYYYYMM]' to data type int. (Microsoft SQL Native Client) ------------------------------BUTTONS: OK------------------------------ If I Take the single quotes out it tells me I must define @ Any ideas? Thanks

Use parameter mapping for this query to work... SELECT iAutoRebillID, iYYYYMM, iAccNum, vcProcStat, sdtProcStatDate, dtInitTime, iUpdUserID, dtUpdTimeFROM tbAutoRebill WITH (NOLOCK)WHERE (iYYYYMM < ?) and map Param0 to the variable User::iYYYYMM For more ... http://msdn.microsoft.com/en-us/library/ms141696.aspx Hope this helps, -Senthil

I don't think you can use the variable this way. Instead, you can: Create a new variable called SourceSQL Open up the properties pane for SourceSQL variable (by pressing F4) Set EvaluateAsExpression=TRUE Set Expression="SELECT * FROM YourTable WHEREiYYYYMM < " + (DT_WSTR, 100)@[USER::iYYYYMM] For your OLE DB Source component, open up the editor Set Data Access Mode="SQL Command from variable" Set VariableName = "SourceSQL" The (DT_WSTR, 100) is used to cast your int variable to a stringwith length set to 100. You can also change the data type of user var iYYYYMM to string without doing the cast.

When I set it up this way I receive this error TITLE: Microsoft Visual Studio------------------------------ Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command. ------------------------------BUTTONS: OK------------------------------ The value for User::iYYYYMM is 200406

Bulldog61 wrote: When I set it up this way I receive this error TITLE: Microsoft Visual Studio------------------------------ Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command. ------------------------------BUTTONS: OK------------------------------ The value for User::iYYYYMM is 200406 Yeah, you can't parse the SQL if you are using parameters. Just trust that it works.

OK, Just went to run it and received this TITLE: Microsoft Visual Studio------------------------------ There was an error displaying the preview. ------------------------------ADDITIONAL INFORMATION: No value given for one or more required parameters. (Microsoft SQL Native Client) ------------------------------BUTTONS: OK------------------------------

SQL should be using a "?" for the placeholder. (Assuming you're using an OLE DB connection.) Then, in the parameter mapping tab, you should set ParameterName to the number zero. Also, ensure that the SSIS variable you are mapping exists at the proper scope and that it has a value. I prefer the other technique mentioned above using expressions on a string SSIS variable for things like this... Parameters can be a bit challenging.

OK, tried the other method and received this error TITLE: Microsoft Visual Studio------------------------------ Error at Archive tbAutoRebill [OLE DB Source [28]]: An OLE DB error has occurred. Error code: 0x80040E14.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.". ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------BUTTONS: OK------------------------------ This is the string: "SELECT * FROM tbAutoRebill WHERE iYYYYMM < " + (DT_WSTR, 100)@[USER::iYYYYMM] This is going to probably be a sever case of sloped forehead syndrone

Bulldog61 wrote: OK, tried the other method and received this error TITLE: Microsoft Visual Studio------------------------------ Error at Archive tbAutoRebill [OLE DB Source [28]]: An OLE DB error has occurred. Error code: 0x80040E14.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Incorrect syntax near '+'.". ------------------------------ADDITIONAL INFORMATION: Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------BUTTONS: OK------------------------------ This is the string: "SELECT * FROM tbAutoRebill WHERE iYYYYMM < " + (DT_WSTR, 100)@[USER::iYYYYMM] This is going to probably be a sever case of sloped forehead syndrone Make sure you are setting the Expression property on the string variable with that string, not the "value" property that you see in the variables window.

why dont u try this. create store proc as follows. CREATE PROC dbo.Details @iYYYYMM VARCHAR(10 ) AS BEGIN SELECT iAutoRebillID , iYYYYMM , iAccNum , vcProcStat , sdtProcStatDate , dtInitTime , iUpdUserID , dtUpdTime FROM tbAutoRebill WITH (NOLOCK) WHERE (iYYYYMM < @iYYYYMM) END inside data flow task use the follwoing thing. exec dbo.Details @iYYYYMM = ? and in the parameter mapping in the paramete list give @iYYYYMM and in the variable list give your user variable(@[USER::iYYYYMM])