T-SQL (Proc) Conversion to SSIS!

Question

T-SQL (Proc) Conversion to SSIS!

Abrar Ahmad_

SSCarpal Tunnel

Points: 4244
More actions
July 23, 2012 at 4:56 am

#259292

[font="Verdana"]Hi,
I have a T-SQL procedure which can transfer data from branch office database to centralized database, with respect to their specific constraints/environment!
A sample dynamic SQL written and executed by the procedure is here
INSERT INTO DESTINATION.DBO.applicant ( [applicant_id], [first_name], [full_name], [is_arrested], [is_guilty], [is_special_permit], [last_name], [middle_name], [name_suffix_id], fo_applicant_id, fo_station_id)
SELECT ROW_NUMBER() OVER (ORDER BY M.applicant_id) + 688 /*DYNAMIC-GENERATE IDENTITY VALUES OF DESTINATION TABLE*/ , M.[first_name], M.[full_name], M.[is_arrested], M.[is_guilty], M.[is_special_permit], M.[last_name], M.[middle_name], M.[name_suffix_id] , M.applicant_id /*THE ID OF SOURCE DATABASE*/, 1 /*STATION ID*/
--Dynamic Joins Section --------------------
FROM SOURCE.DBO.applicant M
--Dynamic Joins Section --------------------
WHERE M.applicant_id > 500 /*DYNAMIC VALUE TO FETCH VALUES FROM */
AND NOT EXISTS (SELECT 1 FROM DESTINATION.DBO.applicant J WHERE J.fo_applicant_id = M.applicant_id AND J.fo_station_id = 1 ) -- DUPLICATION CHECK
Its simple insert from source to destination tables. Also note that the source and destination structure is almost identical, other than the extra columns of latching "Station_id" and "Transaction IDs of the source table".
The procedure builds 20-30 dynamic SQLs(including Source/Destination columns list and servers as well) and execute them!
Now the trick is if we need to migrate the same logic to SSIS package due to
1. Security features it offers
2. Dynamic deployment on different servers.
etc if u guess any other required for the current business problem.
One solution is to call the procedure from the SSIS package, and all done! Is it wise to do so? please guide!
Thank you![/font]

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

@Abrar,

Not knowing much about SSIS, I have no idea and personally think such a conversion would be a waste of time but can't wait to see a solution from some of the SSIS folks on this forum. Think of this as a "bump" for your post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way to do this based on "security" since it is not really any more secure than performing this within SQL Server.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 3

SQLRNNR (7/23/2012)
I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way to do this based on "security" since it is not really any more secure than performing this within SQL Server.

It's funny. I have some folks telling me how I won't be sorry if I spend a lot of time doing a deep dive on SSIS to learn it really well but I keep seeing things like this from dozens of people who use SSIS. So far, I've been able to do everything I need to do without going near SSIS and I'm just not seeing the incentive to go there. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Jeff Moden (7/23/2012)
SQLRNNR (7/23/2012)
I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way to do this based on "security" since it is not really any more secure than performing this within SQL Server.
It's funny. I have some folks telling me how I won't be sorry if I spend a lot of time doing a deep dive on SSIS to learn it really well but I keep seeing things like this from dozens of people who use SSIS. So far, I've been able to do everything I need to do without going near SSIS and I'm just not seeing the incentive to go there. 🙂

Like any tool, it has it's appropriate uses. IMHO, a proc that uses dynamic sql to build queries is not a good thing to refactor into SSIS. Just use what is currently built in this case :hehe:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

Thanks for the response, Jason. Do you have any good examples of where SSIS might outshine T-SQL? Please don't say "anything that needs xp_CmdShell". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

I have used it a lot for File ftp tasks, credit card charging, xml importing from flat files

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

SQLRNNR (7/23/2012)
I have used it a lot for File ftp tasks, credit card charging, xml importing from flat files

Thanks, Jason. Although I've easily done all three from T-SQL, the "XML Importing From Flat Files" has me interested. Is there anything in SSIS that says "Here's an XML flat data file and I know nothing about it. Build me a flat table of all of the data without me having to know anything about it." ?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Yes. XML Source

http://msdn.microsoft.com/en-us/library/ms140277.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Forgot to mention, the credit card charging was to use soap requests across the internet. I am curious if the CC charging you have done is similar or different.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

SQLRNNR (7/23/2012)
Forgot to mention, the credit card charging was to use soap requests across the internet. I am curious if the CC charging you have done is similar or different.

No... no SOAP. It was so long ago that I know for sure that SSIS didn't exist and I don't believe SOAP did, either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 11

SQLRNNR (7/23/2012)
Yes. XML Source
http://msdn.microsoft.com/en-us/library/ms140277.aspx

Ok... we have a winner! I've recently done mostly the same thing in T-SQL (nothing with generating an XSD file and nothing with the multiple outputs, yet) but XML source does make it look quite easy. That may be worth the price of a deep dive.

Now I just need to understand why they didn't make something like that easily available in T-SQL. Heh... what am I talking about??? MS still hasn't seen fit to build a decent splitter function into T-SQL but there's one in SSIS that I know of. Handles quoted identifiers as well from what I understand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Abrar Ahmad_ SSCarpal Tunnel Points: 4244 More actions · Answer 12

SQLRNNR (7/23/2012)
Jeff Moden (7/23/2012)
SQLRNNR (7/23/2012)
I would just call the stored procedure from within SSIS if you must convert it to SSIS. I'm not sold that SSIS is the right way to do this based on "security" since it is not really any more secure than performing this within SQL Server.
It's funny. I have some folks telling me how I won't be sorry if I spend a lot of time doing a deep dive on SSIS to learn it really well but I keep seeing things like this from dozens of people who use SSIS. So far, I've been able to do everything I need to do without going near SSIS and I'm just not seeing the incentive to go there. 🙂
Like any tool, it has it's appropriate uses. IMHO, a proc that uses dynamic sql to build queries is not a good thing to refactor into SSIS. Just use what is currently built in this case :hehe:

[font="Verdana"]Yes, good comments. Thank u both![/font]

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 13

Jeff Moden (7/23/2012)
Thanks for the response, Jason. Do you have any good examples of where SSIS might outshine T-SQL? Please don't say "anything that needs xp_CmdShell". 😉

How would you assess row level error handling with TSQL? For example, in SSIS you can stream data in the dataflow and you have a component that converts strings to integers (because the datasource was dirty and everything was stored as strings). If a conversion fails, you route the offending row to a flat file for further inspection.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

Koen Verbeeck (7/24/2012)
Jeff Moden (7/23/2012)
Thanks for the response, Jason. Do you have any good examples of where SSIS might outshine T-SQL? Please don't say "anything that needs xp_CmdShell". 😉
How would you assess row level error handling with TSQL? For example, in SSIS you can stream data in the dataflow and you have a component that converts strings to integers (because the datasource was dirty and everything was stored as strings). If a conversion fails, you route the offending row to a flat file for further inspection.

There are two ways. In T-SQL, I'd simply prevalidate the rows and mark the ones that had an error in them in a set-based fashion. If it's an import, both BCP and BULK INSERT (as of 2005 for Bulk Insert) have the ability to "automagically" route bad rows to an errata file for further inspection. Rumor has it that SSIS uses the same Bulk Insert engine.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)