January 16, 2014 at 10:34 am
Hi Team, my client provides a text file which contains multiple sql statements.
I need to loop through all of them execute the sql statements, and output the results to excel file sheets.
For Ex:- If client text file contains,
Select * from Employee
Select * From Customer
Select * From CustomerMaster
all 3 excel sheets should be populated with the output of these sql statements,
Please provide your inputs!
January 16, 2014 at 11:13 am
skunapuli 98471 (1/16/2014)
Hi Team, my client provides a text file which contains multiple sql statements.
Whut? Why? ... but alright.
I need to loop through all of them execute the sql statements, and output the results to excel file sheets.
Besides the obvious dynamic sql injection issues, you've basically laid the doors open for any malicious activity they could want... as long as you're aware of that...
High level:
A) You need to extract the statements into a local object variable and then feed that into a for each loop.
B) You'll need to programatically create a new sheet in the Excel file, and then assign that sheet name to a variable for the Excel target's expression settings.
C) In said for-each loop you'll need a dataflow, and the source for that being the database in question with an EXEC( ?) statement and the parameter being the current sql statement from the object in the for each loop.
D) Hook that to the excel target, let it rip.
That said, there's some serious issues here. First: Unless the schema for the excel target is completely standardized and the connections (column names, used columns, etc) are the exact same, you're SOL. You can't change metadata expectations on the fly.
At that point, you're programatically sending the command down to SQL so that you can have a dynamic set (like in .NET code), and creating a dynamic result drop into Excel (again, like in .NET code)... to the point where this should be built in .NET.
Avoid this. SSIS is not the right tool for this task unless the stars are aligned.
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
January 16, 2014 at 11:17 am
I wonder why someone would provide this in a text file (or even in a database table - i've seen this). Is there a better way to do the same thing, ie store and run queries ?
January 16, 2014 at 11:21 am
blasto_max (1/16/2014)
I wonder why someone would provide this in a text file (or even in a database table - i've seen this). Is there a better way to do the same thing, ie store and run queries ?
Yup. Procedures. If you need them run in a particular order create a header procedure that will then run them in sequence, or a job that calls them in a particular sequence.
The most common cause I've seen for this is when front end developers don't want to 'lock their code into the database' so that it dovetails directly into continuous integration with the components that require the calls. There are exceptions, of course, but most of those can be avoided as well.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply