June 13, 2011 at 6:40 pm
I am trying to update a legacy Sybase Table where the primary key is a DateTime datatype. My source is a SQL Server 2005 table, that mirrors the Sybase table. I am using an OLE DB Source as the Source and an OLE DB Command as the Destination. The SQL statement in the Command is simple: UPDATE dbo.rsltn SET code_rsltn = ?, code_rsltn_cat = ?, dt_rslvd = ?, ind_rslvd = ? WHERE id_rsltn = ?.
If I hard code the WHERE parameter exactly as it comes out of Sybase, such as '6/9/2011 9:36:01.923 AM', it works fine. If I let the WHERE parameter get it's values from the Source, it cannot find the record to update.
The columns in both Source and Destination are of the DT_DBTIMESTAMP datatype. I have tried converting the Source column into a String and then let the Destination do an implicit conversion. I have tried doing explicit conversions using a Data Conversion Flow Item. I have tried numerous other things.
Any ideas on how to equate the datatypes in these two columns so they will return the correct records when used in a WHERE statement?
Thanks,
Robin
June 14, 2011 at 4:40 am
Thank you for the quick reply. I checked the timestamps and they are the same. As for the error, the is not one. The SSIS Package runs Green, but the destination record is not updated when the WHERE statement uses a parameter and the mapped fields are this Datetime datatype. As a test, if I hardcode in the WHERE parameter value, it works fine and the destination is updated correctly.
June 14, 2011 at 5:18 am
Ah I see. In which case I would put a data grid viewer before your UPDATE command and verify what exact data is going in. Take a few of these timestamps and see if they match accordingly.
Is there any column other than a timestamp that you could use as a PK? It's quite unorthodox. If you have identical data (in terms of number of rows) then perhaps you could generate a PK using ROW_NUMBER() beforehand....
June 14, 2011 at 6:09 pm
Thanks again for your help. Unfortunately, there are no other columns to use as a natural PK. This is a 25 yo legacy Sybase database. Every table uses a DateTime datatype as the PK.
I created the DataViewer's. The Source PK is showing as: '2011-06-11 16:34:29.123' and the Destination PK is showing as: '6/11/2011 4:34:29.123 PM'. While the format is different, the values are the same, so you would think they equate, but no luck. It's difficult to distinguish what is the display format and what is the underlying value. In SSIS, both columns appear to be a DT_DBDATETIME datatype.
Can someone help me using T-SQL to convert this value: '2011-06-11 16:34:29.123' into this format '6/11/2011 4:34:29.123 PM' (this is the legacy format). Then maybe I can compare the string values.
Thank you.
June 15, 2011 at 3:15 am
This is fairly convoluted and there may be a simpler way, but it will get you the result you are after:
CASE WHEN LEFT(CONVERT(CHAR,GETDATE(),101),2)<10 THEN
STUFF(RTRIM(CONVERT(CHAR, GETDATE(), 101)),1,1,'')
ELSE
RTRIM(CONVERT(CHAR, GETDATE(), 101))
END
+' '+STUFF(RIGHT(CONVERT(CHAR, GETDATE(), 109),18),13,0,' ')
Just replace the GETDATE() above with your date.
June 15, 2011 at 7:09 am
yayomayn, thanks for your help. I will try out the query statement this afternoon and post back my results.
June 15, 2011 at 8:28 am
robin 86662 (6/14/2011)
Can someone help me using T-SQL to convert this value: '2011-06-11 16:34:29.123' into this format '6/11/2011 4:34:29.123 PM' (this is the legacy format). Then maybe I can compare the string values.
Wait. The Source and Destination are using DBTimestamp, but the table is using a string value?
Do I understand that correctly?
June 15, 2011 at 8:56 am
yayomayn, Your t-sql statement was very close. Could you please tweak it so there is a decimal between Seconds and Milliseconds instead of a colon? Your query results are: '6/15/2011 10:51:40:280 AM' and the destination format is: '6/11/2011 6:07:52.556 PM'
Thank you.
June 15, 2011 at 9:08 am
Thank you Brandie for responding. The Source (SQL Server table) column is defined as Datetime. The Destination (Sybase table) is also defined as Datetime. In SSIS, using a OLE DB Source for the source and a OLE DB Command for the destination and using an UPDATE statement with parameters as described above, the properties windows shows both columns as DT_DBDATETIME. This particular column on the Sybase side is the PK. When I use this column in the WHERE clause and use a ? parameter, the values between Source and Destination do not equate, so no records in the Destination are updated. Microsoft believes the Sybase Driver may be involved. I am using the Adaptive Server Enterprise, 15x Sybase driver.
I am exploring ways to equate these two columns. Converting them to a string and transforming the Source string into the same format as the Destination is one approach I am trying. I have not gotten far enough to know if this will work or not.
Any suggestions?
Thanks,
Robin
June 15, 2011 at 9:20 am
Can you please tell us how you have your Source set up to use the parameters?
Also, tell us what the parameter sources are. (I just want to verify something).
June 15, 2011 at 9:31 am
It's the Destination that uses the parameters. The Source is a straight table. In the SQL Command property of the OLE DB Command, I am using the following t-sql: UPDATE dbo.rsltn SET code_rsltn = ?, code_rsltn_cat = ?, dt_rslvd = ?, ind_rslvd = ? WHERE id_rsltn = ?. In the Edit Destination window, the parameters are mapped to the Source columns.
In a prior test, I hard coded values in place of the ? parameters, including a value in the WHERE parameter and the sample record was updated correctly in the Sybase table. I then put the ? parameters back, except for the WHERE parameter, leaving that hard coded. Again, the sample record in Sybase was updated correctly. So I know the parameters are being populated correctly.
However, when I use a parameter for the WHERE clause, the SSIS package can no longer find the sample record in Sybase. This tells me the value coming from the Source (SQL Server) does not equate to the value in the Destination (Sybase).
Thanks,
Robin
June 15, 2011 at 9:37 am
robin 86662 (6/15/2011)
It's the Destination that uses the parameters. The Source is a straight table. In the SQL Command property of the OLE DB Command, I am using the following t-sql: UPDATE dbo.rsltn SET code_rsltn = ?, code_rsltn_cat = ?, dt_rslvd = ?, ind_rslvd = ? WHERE id_rsltn = ?. In the Edit Destination window, the parameters are mapped to the Source columns.In a prior test, I hard coded values in place of the ? parameters, including a value in the WHERE parameter and the sample record was updated correctly in the Sybase table. I then put the ? parameters back, except for the WHERE parameter, leaving that hard coded. Again, the sample record in Sybase was updated correctly. So I know the parameters are being populated correctly.
Actually, hard coding the values does not necessarily correlate to the parameters being populated correctly. But yes, now that I better understand the issue, I think I can see why the columns aren't matching up.
I'm going to test something on my side, but really quick, are you sure that date parameter is the only one that's giving you issues?
June 15, 2011 at 9:46 am
Thanks Brandie, yes the date parameter when used in the WHERE clause is the only parameter causing an issue. The other date parameter, the one where the data is being updated, is not an issue. It's only the WHERE clause, because the two values do not equate, there are no records returned to update.
June 15, 2011 at 11:48 am
For the record, OLE DB SQL Command is a horrendous transformation to use. It updates records on a row-by-row basis (RBAR) instead of by a set based solution. This causes performance issues on huge tables and could give you many headaches down the road.
Let's start over from the top. You have two tables. A Sybase table and a SQL table. The SQL table is your source for data and the Sybase table is what you're chucking everything into. You've added an OLE DB Source object which leads to the OLE DB Command transformation.
The update statement is stuck in the second tab of the transformation object, under SQL Command, and the Data Viewer was put on the precedence constraint between the two objects. Correct?
I think I know what your problem is. The OLE DB Command object isn't a destination (which I just realized you'd said on your very first post). It is a RBAR version of the Execute SQL Task. It transforms data before sending the data on to the next task.
You need to add a Destination object to your Data Flow Task underneath the Command object. But, as I said, I don't like the Command object. I advise doing your update in an Execute SQL Task in the control flow, then sending THAT data through your Data Flow task or, instead of using a table source, use a SQL Command that pulls and converts the data in the SELECT statement before sending it off to Sybase.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply