April 27, 2022 at 12:56 pm
I have SSIS Package , in the source query i am using parameters to pull data greater than from that date.
Process is here.
step 1 - Created Variable StdDate with datatype String.
Step 2 - execute sql task to get the date column with datetime2(7) datatype from control table. Sample value looks like
2022-04-26 02:07:04.0579712
this value will be assigned to variable StdDate
step 3 - Data Flow Task. where i used this StdDate as Param in the source query.
where i am using in the where condition like
select name, email
from student where dtkey > ? and createddate > ?
Problem is dtkey is BigInt and createddate is the datetime2(7). When i run the package it keeps on giving "Invalid character value for cast specification".
Its not accepting for bigint. How can we convert or cast dtkey to get this work.
April 27, 2022 at 5:35 pm
What date does DtKey mean and why do you need to filter it as well as the createdate. ? How does DtKey represent a date?
Does it join to a calendar table (see above) ? Does it look like a date e.g. 20220427? Is it the number of days since the birth date of the person who created the database (This happened).
If you can't join to a calendar table to convert DtKey to datetime2 then you could convert StDate to a bigint and the way you do that depends on what the DtKey values look like.
Once you have that logic you can either create another ssis variable and set it using an expression and pass it to the source query, or you can declare a sql variable in the data source query and set it in sql. I feel like doing it in an ssis expression would be cleaner, but I dislike ssis expression syntax and the sql alternative is probably easier. A third option is that it sounds like you are populating StDate with the results of a query. You could change that query so it also returns the date in a format that works for a bigint variable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply