After several hours of trying, I trow the towel in the ring and come here to ask a question.
Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).
What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable
I try to do this in 2 sql execute tasks:
sqltask 1: "select @.@.DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?
DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
INT64 and UINT64 don't work error message that types for column and parameter are different
STRING works
Then I want to store this variable back in a table of a different data source
sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...
if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ?
if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.
Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?
How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?
tx for any help
Dirk
After some more hours (It just kept anoying me till late in the evening) I finaly found a way to make this work.
Make the variable in SSIS of type string
To store a timestamp as a variable
select Cast(timestampcolumn as bigint) as outputcolumn from controltable
create result set to map outputcolumn to variable
(SSIS wil do the conversion to string itself) Know that I tried to include this in the sql a convert to bigint an then to string but didn't work.
To use the timestamp in your queries or write it to another table
select * from table where timestampcolumn >= CAST(? AS BIGINT)
parameter mapping variable input type VARCHAR
(SSIS wil to the conversion from bigint to binary(8) itself
Got this working.
Point stays that datatypes should be more alligned between all the different places we use them, scripts, expressions, variables, parameters, sql data types...
Anyone got a better way, let me know.
tx
Dirk
|||SSIS can genertae timestamps without an external data source. Is that not an option for you?
-Jamie
|||
Hi Jamie,
Don't think so. Let me explain what the purpose of all this was. The source ERP system uses timestamp columns on the tables. So I would like to use these columns as ModificationTime for my incremental load of new data in the DWH.
My problem was that I needed to get the @.@.DBTS from the source ERP system (hence the first SQL task) so that I would know the timestamp from the moment the upload started (my datapumps have a where clause like WHERE ModificationTimestamp >= TimestampOfStartLastSuccesfullUpload, so it will get all new and changed records since the start of the last upload).
Then I needed to store this Timestamp in my control table of the DWH and that's the 2d SQLtask.
I would have been extremly simple if the controltable was in the source ERP system, I could just insert the @.@.DBTS, but that was not an option. So I needed to pass the timestamp between the 2 SQL tasks. And that was my problem, getting the timestamp from the first select SQL task store it in a variable and pass that variable to the second insert sql task.
Perhaps this would have been easier in a script task, but I'm not that good at cooding. I haven't passed the level of copy, paste and modify some code ;-).
What do you think ? Is there a simpler solution ?
Dirk
|||Ah I understand now. You need to persist the max timestamp between executions. That's a common requirement of course.
I may be mistaken but it seems the format of the timestamp is somewhat proprietary, hence the solution that you have come up with should be the most suitable - it sounds as though it will work fine though.
Question. What does the timestamp value look like? i.e. Can you paste it up here?
Regards
Jamie
|||
Dirk Van der Straeten wrote:
(BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
Yes, there is. Drag the variable into a Watch window within the BIDS environment. When you execute and break you will be able to look at the value of the variable.
-Jamie
|||
Jamie,
Following select on the database and resultset.
select @.@.dbts as timestamp, CAST(@.@.dbts as bigint) as ConvertedInteger, CAST(@.@.dbts as varchar) as ConvertedVarchar
0x00000000000F94C7 1021127 _
The Timestamp is a binary(8) that is non nullable. But you can also interpreted is a an 8 byte integer which is the same as a bigint. Where __ is actualy the blanc of the string. That was the problem that converting directly from timestamp into the string variable did not work. Strangly I also didn't get it to work when I made the Variable a UI64 or I64 and then user LARGE_INTEGER as parameter type. He gave the error that the types where not compatible.
Got the watch thing figured out now.Tx.
/Dirk
