April 12, 2011 at 5:52 pm
Working on Dts to SSIS 2008
i have a Dts package where in one step it executes a store proc and then the results are directed into text file, through "isql" statement.
Store proc is all about loading a table into Cursor and then making sure that the data in the cursor is cleansed (like no spaces in b/w the data ..etc). was going through this store proc in SSMS, excuted it ...after excution all it says is "commands sucessful" but doesn't returns anything on the output window.
But according to Dts package, after Storeproc execution it should write into a text file. (synatax is something different but it looks like this ( isql xxxxx "exec xxxx" /o c:/1.txt"))
Now my question is how can i do it in SSIS. we can do it if there are output parameters mentioned inside store proc ( var1 output.....var z output), by using EXECUTE SQL TASK and then mapping parameters accordingly. But with out any kind of output parameters mentioned in store proc can i still do it. Is there a way in SSIS, that i can catch all the info from store proc
Can any one help me in this .... Do mention if above is not clear
April 12, 2011 at 8:13 pm
When you executed your stored procedure, I am guessing that you opened it (modify?) or scripted it to a query window. Then you clicked on the execute button or hit F5.
This does not run the stored procedure - it executes the code in the query window and that code is the statement to create or alter the stored procedure.
To execute the stored procedure, in a query window you would do this: Execute dbo.MyStoredProcedure
Now, to do this in SSIS is very simple. Add a data flow to your project. In the data flow, add an OLEDB source and configure it to point to your server and database. In the source, you want to select the option for code (can't remember the exact syntax) - and in the code window put in the statement to execute your procedure (e.g. Execute dbo.MyStoredProcedure).
Now, add a flat file destination and connect it to the source. Configure the parameters for the file - file name, location, type of file, etc...
Once you have that, you can debug it to validate it works - test until you get the results you want and you are done.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 12, 2011 at 8:52 pm
Option for code ..
Thank you for replying...
ok when i make sure that in source OLEDB
draged an OLEDB Source task to control flow and got the connection manager. selected
Data acess mode : Sql command
then i gave "execute dbo.my store proc name" now it gives a pop window saying that "no column information was returned from sql command"
But any way i cannot connect to flat file as my destination.... because, as soon as i select my destination as flat file, system ask me to map the columns from source to flat file. As execute store proc command is not returning any columns how can you do this.
April 13, 2011 at 12:13 am
Most likely the reason why this doesn't work is because of the way the procedure is written. If you are using a cursor and not using a straight SQL statement, then SSIS cannot parse the procedure to get the meta-data.
If you post the code here, I am sure we can figure out how to convert that cursor into a set-based procedure that will work.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 13, 2011 at 9:39 am
thank you.. for your time
before bothering you again, i will check with team that can take care of this ...but what should i tell them. Should i explain them that the data in the cursors can't be used
April 13, 2011 at 9:52 am
quillis131 (4/13/2011)
thank you.. for your timebefore bothering you again, i will check with team that can take care of this ...but what should i tell them. Should i explain them that the data in the cursors can't be used
It's tough to tell without seeing the proc.
Does it populate a temp table, perhaps, and then do a SELECT ... FROM #tablename at the end to provide the required output?
If you are using this as the source for a dataflow, you do indeed have a meta data issue with SSIS. But it can be tricked into thinking that there is meta data available - put a dummy select at the beginning of the proc definition:
select
fields
where 1 = 2
[replace fields with your field list]
This will, of course, never be executed. But it might be enough ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 13, 2011 at 11:23 am
i can send you the proc ... but is there any other way that we can make it private
April 18, 2011 at 3:43 pm
Hello Jeffrey Williams-493691,
Actually I am waiting for your reply, Can you help me with my Store proc ( the one I mentioned you above) i can send you the proc ... but want to make it private. Can you help me out this way?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply