SSIS: Convert datatypes in a SOQL query for a SSMS validation table

Question

I am working on an EDW that is using SSIS to bring in SalesForce tables. I'm in the process of writing a validation query on the CData Salesforce Source that will return CURRENT_DATEIME() for when it was loaded as well as Sum of the Column values, in order to use later in my validation strategy. I have the results of these column importing into a source validation table in SSMS. The problem I am running into is CData is defaulting the columns to STRING datatypes when I need NUMERIC on both, but you cannot Parse, Convert or Cast in a SOQL query, apparently. What would be the best way to convert these?

Here is my Query thus Far.....

SELECT TABLE_NAME, SOURCE_COLUMN_VALUE, COLUMN_NUMBER, LOAD_DATE, LOAD_DATETIME 
FROM (
      SELECT 'TABLE' AS TABLE_NAME, COUNT(*) AS SOURCE_COLUMN_VALUE, 0 AS 
      COLUMN_NUMBER, CURRENT_DATE() AS LOAD_DATE, CURRENT_TIMESTAMP() AS 
      LOAD_DATETIME  
      FROM [TABLE] UNION ALL

      SELECT 'TABLE' AS TABLE_NAME, SUM(CASE WHEN Id IS NULL THEN 0 ELSE 1 
      END) AS SOURCE_COLUMN_VALUE,  1 AS COLUMN_NUMBER, CURRENT_DATE() AS 
      LOAD_DATE,CURRENT_TIMESTAMP() AS LOAD_DATETIME FROM [TABLE]
)

I need to convert the selected columns SOURCE_COLUMN_VALUE and LOAD_DATETIME to NUMERIC using SOQL.

I have tried TO_NUMBER() and NUMERIC.PARSE() already

This is the error I keep getting

Error: 0xC020901C at BASS_USER_VALIDATION, OLE DB Destination [29]: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[LOAD_DATE] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at BASS_USER_VALIDATION, OLE DB Destination [29]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB Destination Input]" 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.

Error: 0xC0047022 at BASS_USER_VALIDATION, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (29) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (42). 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.

Put the data into a staging table. Inspect the data for convertibility. Convert the data to the needed type. Perhaps you need to modify the values to do so. Besides, you did not post what errors you get --> that would help expedite the matters. Lastly, the logic you implement is flawed to me because it does not account for uniqueness and also may be slow or bloating for the use of the UNION clause. — Arthur, Apr 17 '19 at 20:15
Updated my post with the error. This is only a small piece of my validation. I am summing numbers of a numeric column and the varchar and date are being sum by how many not NULL input fields there are. The purpose of the date being a numeric is we are also bringing in oracle, excel, and googledocs with SSIS so we are trying to make this as reuseable as possible. The dates are then validated by comparing the sum of columns from the destination tables by the sum of the columns based on source tables from using the max datetime of all the loads we have done for validation. — CFJohnston, Apr 17 '19 at 20:53

score 1 · Accepted Answer · edited Apr 18 '19 at 17:25

1

We ended up solving the issue using a Derived Column where we were able to take the string of the datetime, parse that down to what was needed, and converted that into a numeric type. Not the most efficient I'm sure but it is doing what needs to be done! (SalesForce tables will be the death of me)

(DT_NUMERIC,12,4)(DT_DATE)(SUBSTRING(LOAD_DATETIME,1,4) + "-" + SUBSTRING(LOAD_DATETIME,FINDSTRING(LOAD_DATETIME,"-",1) + 1,2) + "-" + SUBSTRING(LOAD_DATETIME,FINDSTRING(LOAD_DATETIME,"-",2) + 1,2) + " " + SUBSTRING(LOAD_DATETIME,FINDSTRING(LOAD_DATETIME,"T",1) + 1,2) + ":" + SUBSTRING(LOAD_DATETIME,FINDSTRING(LOAD_DATETIME,":",1) + 1,2))

edited Apr 18 '19 at 17:25

Hadi

36,233
13
65
124

answered Apr 18 '19 at 15:46

CFJohnston

93
6

You have to define a number formula field and use the expression mentioned at the end of the other answer. check the following link: https://stackoverflow.com/questions/5915584/soql-type-conversion-salesforce-com – Yahfoufi Apr 20 '19 at 12:15

SSIS: Convert datatypes in a SOQL query for a SSMS validation table

1 Answers1