CODEWITHSUNDEEP
sql-servert-sqlssis
user2038230
user2038230

Reputation:

Can I use DT_STR in T-SQL

I was using SSIS in which I have code that I use for parsing files with year / month and day so in Expression Builder I use:

(DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) gets the month
(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) gets the day
(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) gets the year

However, when I am coding, I like to paste my code into a sql server query window as well, but this DT_STR seems to be specific to SSIS ?

Is there an equivalent replacement, or why can't I use this in T-SQL?

EDIT:

Ok, I see now that this code is not all all portable to t-sql , nor is it used correctly with the month and day never being a length of 4

(DT_STR, 4, 1252) YEAR (DATEADD ( "dd", 0, getdate())) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) MONTH (DATEADD ("dd", 0, getdate()))), 2) + RIGHT ("0" + LTRIM ((DT_STR, 4, 1252) DAY (DATEADD ("dd", -1, getdate()))), 2) + ".TXT"

Upvotes: 1

Views: 2780

Answers (1)

billinkc
billinkc

Reputation: 61201

The SSIS Expression language is not TSQL. You might as well ask why can't you paste Motorola 68k assembly code into an R compiler? DT_STR is an Integration Services Data Type. The approximate SQL Server data type would be varchar(N).

If your question is how do you verify your expressions are correct, assign them to a variable and then you can inspect them during package execution by setting a breakpoint and then looking at the Locals window. The Variables window you use during development is not going to contain the run time values. Complex expressions built in a Task's properties is a nightmare for troubleshooting as there are no facilities to provide inspection.

Finally, your code above assigns a length of 4 to entities that will never be 4 spaces in length: month and day.

Upvotes: 2

Related Questions