February 22, 2012 at 9:42 am
Having been a long-time T-SQL (and VB.NET) programmer, I have been immersed in SSIS for a while now. I am trying to clearly understand some basic overview issues:
In the control flow, it's pretty easy to use T-SQL to manipulate data. In these tasks, you use connections that you have set up. Typically, these are flat file or SQL database and table connections.
Once the control flow calls a data flow task, and you're in the data transformations, when you have data records and data columns "flowing" in and out, the scripting is limited to the weird SSIS expression language, which I think is a hybrid of some other languages. (Just what we need: Another programming language to learn that is different from all the rest!)
If all of that is correct:
Is there any way to use the T-SQL language in a data transformation, such as a derived column transformation? T-SQL is not the world's best programming language (it's a data manipulation language), but in one reent case, I wanted to create a new output column by calling T-SQL Hashbytes() on an existing column in a data flow.
I can call a similar function in C# or VB, but it's not as straightforward... and I wanted to be clear in my own mind whether there was any way to use T-SQL against the columns in a data flow.
Let me know if I'm missing anything obvious. Thanks!
David
February 22, 2012 at 12:00 pm
David Walker-278941 (2/22/2012)
Is there any way to use the T-SQL language in a data transformation, such as a derived column transformation? T-SQL is not the world's best programming language (it's a data manipulation language), but in one reent case, I wanted to create a new output column by calling T-SQL Hashbytes() on an existing column in a data flow.
No, you can't. You might be able to do the equivalent in a T-SQL script transformation task (which allows you VB.NET and C# calls), but they're unwieldy until you're used to them.
I personally would dump the data and then run an update statement post-delivery.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 23, 2012 at 10:20 pm
Thanks for the reply. (The word "reent" in my question should have been "recent".)
SSIS expression language is a horrible language! I had to write some VBA code today, after not having written any for over a year, and I was reminded how VBA is actually a pretty good programming language.
SSIS expression language is nowhere near as good -- not even close. Why couldn't VBA, or VB.NET, or C#.NET have been used instead, since they all already existed? Why create a new language? (Or did it come from somewhere else?) Would putting VB.NET or VBA have been too much internal code, or what? I hate SSIS expression language. End of rant...
February 24, 2012 at 12:31 am
David Walker-278941 (2/23/2012)
Thanks for the reply. (The word "reent" in my question should have been "recent".)SSIS expression language is a horrible language! I had to write some VBA code today, after not having written any for over a year, and I was reminded how VBA is actually a pretty good programming language.
SSIS expression language is nowhere near as good -- not even close. Why couldn't VBA, or VB.NET, or C#.NET have been used instead, since they all already existed? Why create a new language? (Or did it come from somewhere else?) Would putting VB.NET or VBA have been too much internal code, or what? I hate SSIS expression language. End of rant...
The SSIS expression language was intended to do simple tasks. If the expression becomes too difficult, you're supposed to resort to the script component which uses VB.NET or C#.NET.
Anyway, I usually do with Craig does: dump it in a staging table and update it with TSQL. (especially if there are datetime conversions. SSIS really sucks at this)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply