April 13, 2012 at 10:42 am
I have the following statement which has a parameter
SELECT
CustomerID
,TerritoryID
,AccountNumber
,CustomerType
,ModifiedDate
FROM
Sales.Customer
WHERE
ModifiedDate > CONVERT(DATETIME, ?, 121)
When I click on Preview for Get Updated Customers I get an message box 'No value given for 1 or more required parameters?
When I click on parameters it displays 0 and a variable LastExtractDateTime.
What am I missing?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2012 at 11:22 am
Does your variable have a hard-coded value before execution, or is the value only assigned during execution?
April 13, 2012 at 12:35 pm
The OLE DB Source which contain the Select with a paramater in my OP and the Destination task faile and I'm getting the following errors:
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "Get Updated Customers" (1) returned error code 0xC02020C4.
The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
[Put Updated Customers in Staging [56]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (69)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (69)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Put Updated Customers in Staging" (56) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (69). 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. There may be error messages posted before this with more information about the failure.
[Get Updated Customers [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Get Updated Customers" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2012 at 1:00 pm
Martin Schoombee (4/13/2012)
Does your variable have a hard-coded value before execution, or is the value only assigned during execution?
The variables are set during runtime.
I checked the variables when I executed that package and they have values.
I believe the issue os with the OLE DB SOurce.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2012 at 1:04 pm
Welsh Corgi (4/13/2012)
Martin Schoombee (4/13/2012)
Does your variable have a hard-coded value before execution, or is the value only assigned during execution?The variables are set during runtime.
I checked the variables when I executed that package and they have values.
I believe the issue os with the OLE DB SOurce.
If that is the case, then your original post doesn't make sense...you posted that you received an error when clicking on the preview button, which would make sense if the source query contains a parameter but the parameter has no value.
You may also have some conversion issues if the data type in the source is not compatible with your variable's type.
April 13, 2012 at 1:19 pm
I removed the WHERE Clause that included the input parameter and I still get the error.
When the Task executes the parameter does not have a value.
I did not mean to confuse anyone. If the task had completed it would assign the value to the Variable.
I'm experimenting with the sample SSIS PAckage from the following URL:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 13, 2012 at 1:23 pm
Welsh Corgi (4/13/2012)
I removed the WHERE Clause that included the input parameter and I still get the error.When the Task executes the parameter does not have a value.
I did not mean to confuse anyone. If the task had completed it would assign the value to the Variable.
I'm experimenting with the sample SSIS PAckage from the following URL:
Can I assume that your source is a SQL Server database? If so, check your data types and resultset attributes. Also check your connection manager and recreate it if necessary.
April 13, 2012 at 1:40 pm
I created the destination by a SELECT INTO Destination Table FROM the Source Table
WHERE 1 = 2
It had NOT NULL Constraints in the destination and that was the issue.
Thank you very much!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply