March 10, 2016 at 11:59 pm
Hi,
I have a TXT File with 20 Select statements.
Requirement is Execute each Select statement and Email the output in CSV or xls file format.
can i get an idea how I can proceed with this to create a SSIS package
thanks in advance.
Regards
Avi
March 14, 2016 at 6:57 am
Why do you need a text file for your SELECT statements? Do they change at all?
March 14, 2016 at 7:13 am
Hi Tarvin,
I mean to say,Document having multiple select statements.
Each select statement output should be sent to Email in xls or csv format.
Presently I created jobs separately for each select statement.
I am looking for the help,to do this in a single SSIS package,which will execute each select statement and send output to email in xls or csv format.
Thanks
March 14, 2016 at 7:22 am
Why do you need a document with SELECT statements?
Why can't you create a bunch of stored procedures and then use Data Flow Tasks with each stored procedure as your Source and then the flat file as your destination?
March 14, 2016 at 10:39 pm
App team provided select statements and want output of select statement as report in xls or csv format to thier email ID
March 15, 2016 at 3:54 am
aavi1011 (3/14/2016)
App team provided select statements and want output of select statement as report in xls or csv format to thier email ID
Is this a one off run or something that will happen regularly?
If it's a one off, just use the Export Wizard. You'll have to do it once for each query.
If it's a regular thing, then do as I suggested earlier. Create Stored Procedures as your Source, then use the flat files as your Destination.
March 15, 2016 at 4:02 am
This need to happen daily.
March 15, 2016 at 4:08 am
aavi1011 (3/15/2016)
This need to happen daily.
Do you know how to create an SSIS package?
March 15, 2016 at 4:18 am
Yes I know,But creating Sp's in production,DBA need to get more permissions,Need to take this to other level.
March 15, 2016 at 5:38 am
aavi1011 (3/15/2016)
Yes I know,But creating Sp's in production,DBA need to get more permissions,Need to take this to other level.
It sounds as if you've never used Data Flow Tasks given your responses to my advice. I suggest you play with them and see what options you have available for this project.
March 16, 2016 at 7:34 am
Brandie Tarvin (3/15/2016)
aavi1011 (3/14/2016)
App team provided select statements and want output of select statement as report in xls or csv format to thier email IDIs this a one off run or something that will happen regularly?
If it's a one off, just use the Export Wizard. You'll have to do it once for each query.
If it's a regular thing, then do as I suggested earlier. Create Stored Procedures as your Source, then use the flat files as your Destination.
Why not just put the Select statements in the source instead of calling a SP? If you don't have permission to set up a SP on the production server this may be the way to go.
You would then need to set up a dataflow for each select you need to do.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
March 17, 2016 at 2:01 am
The solution can be made generic and automated:-
1) Keeping text file as source (1 column) Load it in a recordset destination using dataflow
2) Loop over the record set and store each query in a variable
3) Inside this Loop use a dataflow with OLEDB (variable as query def) as source and csv as destination
4) use a email task to mail the csv
Another solution could be to just use a script task and do all of above 🙂
March 17, 2016 at 6:00 am
manoj.kumar-1078069 (3/17/2016)
The solution can be made generic and automated:-1) Keeping text file as source (1 column) Load it in a recordset destination using dataflow
2) Loop over the record set and store each query in a variable
3) Inside this Loop use a dataflow with OLEDB (variable as query def) as source and csv as destination
4) use a email task to mail the csv
Another solution could be to just use a script task and do all of above 🙂
Part (3) is extremely difficult to handle in a 'generic' way, because the record set structures for source and destination need to be defined at design time.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 21, 2016 at 7:32 am
below86 (3/16/2016)
Brandie Tarvin (3/15/2016)
aavi1011 (3/14/2016)
App team provided select statements and want output of select statement as report in xls or csv format to thier email IDIs this a one off run or something that will happen regularly?
If it's a one off, just use the Export Wizard. You'll have to do it once for each query.
If it's a regular thing, then do as I suggested earlier. Create Stored Procedures as your Source, then use the flat files as your Destination.
Why not just put the Select statements in the source instead of calling a SP? If you don't have permission to set up a SP on the production server this may be the way to go.
Overhead. Security issues. Ease of maintenance.
below86 (3/16/2016)
You would then need to set up a dataflow for each select you need to do.
Nope. One Dataflow Task can be used for all (if you're into self-torture). It's just a matter of cramming in all the sources and destinations into the DF. I've done it before. The agonizing thing about this is you can't disable any of the transformations within the Dataflow task for troubleshooting purposes (hence the self-torture comment).
March 21, 2016 at 7:37 am
manoj.kumar-1078069 (3/17/2016)
2) Loop over the record set and store each query in a variable
Depending on how big the query is, this may not work. Variables in SSIS have a limit of how many characters they can store. Also, the OP doesn't sound like (s)he has enough SSIS experience to set up a simple SQL Query Source, so I'm not sure that (s)he'll be able to do something as complicated as what you're suggesting.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply