March 16, 2009 at 12:34 am
I have a SSIS package that consists of a number of dataflows that extract data from the SQL server database to fixed width text files. Each of the dataflows uses an OLE DB Source, which uses a stored procedure to extract the data. All of a sudden, I'm getting external metadata synchronisation errors.
The stored procedure select is as follows:
SELECT
CAST(EMPLY.MI_EMPLOYEE_NUMBER AS VARCHAR(10)) AS [EMPLOYEE_NUMBER]
,CAST(EMPLY.MI_GIVEN_NAME AS VARCHAR(30)) AS [GIVEN_NAME]
,CAST(EMPLY.MI_SURNAME AS VARCHAR(30)) AS [SURNAME]
,CASE
WHEN EMPLY.MI_EMAIL_ADDRESS_2 LIKE '%hnehealth%'
THEN CAST(EMPLY.MI_EMAIL_ADDRESS_2 AS VARCHAR(50))
ELSE CAST(EMPLY.MI_EMAIL_ADDRESS_1 AS VARCHAR(50))
END AS [EMAIL_ADDRESS]
,'EOL' AS [EOL]
FROM
dbo.MI_EMPLOYEE EMPLY
WHERE
(EMPLY.MI_DTTM_EMPLOYMENT_TERM IS NULL
OR EMPLY.MI_DTTM_EMPLOYMENT_TERM >= @TODAY_DTTM)
AND(EMPLY.MI_EMAIL_ADDRESS_1 LIKE '%hnehealth%'
OR EMPLY.MI_EMAIL_ADDRESS_2 LIKE '%hnehealth%')
AND(EMPLY.MI_EMPLOYEE_ARCHIVE_FLAG = 'N' OR EMPLY.MI_EMPLOYEE_ARCHIVE_FLAG IS NULL)
ORDER BY
CONVERT(NUMERIC,EMPLY.MI_EMPLOYEE_NUMBER)
When I open the package in BIDS, the dataflow that uses this stored procedure has a validation warning, and when I go to edit the component, I get a warning that says the external metadata column collection is out of sychronisation with the data source columns, and that each of the columns must be removed from the external metadata column collection. If I elect to fix the errors, all the columns are removed, but none are added. This means there's no columns to write to my data destination.
I've tried deleting the data flow and recreating it. I've tried junking the stored procedure and starting again from scratch. I've tried saving the stored procedure to another name. None of the other data flow tasks in the SSIS package have this problem, and they are all the same sort of thing - stored procedure data source, writing to a fixed width flat file.
March 16, 2009 at 4:16 am
generally this kind of errors go away when we open the data flow task in the BIDS automatically. don't know why its not happening in this case.
Try using SQL script instead of a stored proc. It might do the trick.
March 16, 2009 at 4:40 pm
The problem seems to be due to a pair of SET statements appearing before the select:
DECLARE @TODAY_DATE AS VARCHAR(20)
DECLARE @TODAY_DTTM AS DATETIME
SET @TODAY_DATE = CONVERT(VARCHAR,GETDATE(),103)
SET @TODAY_DTTM = CONVERT(DATETIME,@TODAY_DATE)
If I comment these out, the stored proc works fine.
I tried cutting and pasting the SQL into the OLE DB source SQL command, but I don't like embedded SQL in SSIS packages - it's a pig to maintain.
March 16, 2009 at 11:55 pm
The statement
SET @TODAY_DTTM = CONVERT(DATETIME,@TODAY_DATE)
is erroring out with the below message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
I don't understand why are you first converting a datetime into varchar and then re-converting into datetime.
What is the datatype you desire?
Post the whole stored proc explaining what exactly you are trying to achieve?
Thanks
Pankaj
March 17, 2009 at 5:46 pm
I've fixed this - it had to do with the metadata for the stored procedure being read by the SSIS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply