All - I am new to SSIS so forgive my ignorance. I am using MS Visualt Studio 2008 with SQL Server 2008. I have an SQL Agent job that runs every 5 minutes looking for a .sql file in a predetermined location. After a .sql file is found the agent job launches an SSIS package that executes the .sql file. I have the SSIS package created with an EXECUTE SQL TASK - and it is working fine. My problem is getting the result set from the query OUT of the variable it is being stored in and somehow returning the sql results either back to the agent job or even better, write the results of the query to a flat text file. I have the task setup with the options listed below. GENERAL TAB: ResultSet: Full result set Connection Type: OLE DB SQLSourceType: File connection FileConnection: myfile.sql PARAMETER MAPPING TAB: I have a single variable defined as follows VariableName: User::Results Direction: Return Value Data Type: LONG Parameter: 0 Parameter Size: -1 RESULT SET TAB: Singe entry as follows: Result Name: 0 Variable Name: User::Results (defined as an OBJECT) If I understand the process correctly, the query is being ran successfully with the result set being stored in my variable User::Results. When I run the package it executes without error - all green - but I cannot figure out how to actually see the query results. Make sense? Thanks - MM

Hi, I think the error is due to use of the parameter User:Results in parameter mapping tab. You already set the task to read the values into User:Results in Result Set tab so there is no need to use it again in parameter mapping tab. To see the results place a data flow task right below execute sql task with the help of a data viewer you can view the results.latha

There is no error Latha - the package runs fine. My problem is I do not know what to do with the data once it is stored in the variable. I still don't know but thanks for the reply. I did remove the Parameter Mapping info though because as you said it is not needed. I think it is a left over setting from my various tests. MM

You can make use of WATCH feature in SSIS: source: http://www.sqlis.com/sqlis/post/The-Execute-SQL-Task.aspx See these: A really cool feature of Business Intelligence Studio being hosted by Visual Studio is that we get breakpoint support for free. In our package we set a Breakpoint so we can break the package and have a look in a watch window at the variable values as they appear to our task and what the variable value of our resultset is after the task has done the assignment. Here's that window now. As you can see the count of employess that matched the data range was 2. latha

Can you describe the contents of the SQL you're executing? My first reaction is that you're using the wrong tools inside SSIS. If you are executing a SQL statement in order to receive a resultset, and want that resultset saved to a file (the only way to communicate it back to Agent), then you should probably be using a Data Flow Task. The Data Flow Task has an OLE DB Source in which you can place your statement. You can then attach a Flat File Destination to route the resultset to a text file. Talk to me now on

Thanks Todd - The SQL is basic select statements, sometimes some joins etc. but it is pretty straight up select statements. Here is the dilemna with the Data Flow task idea. The query itself will change each time a new .sql file appears in the designated repository. I somehow need a method to dynamically execute these queries as they are submitted. Best I could tell with the Data Flow task I would need to hard code the query - something I can't do in this case. The Execute SQL Task appeared to be the route to take in that I could use a file and that file's contents could changeeach time the task was executed.

Just saw and option in the OLE DB Source settings (using the data flow task idea) that allows me to use a variable for the sql statement. Once I figure out how to create and populate that variable with the sql statement contained in the file I will be set - right? Something like: Get .sql file from repository Assign sql statement to a variable. Execute the "variable" Save the results to a file with a Flat File destination. I just need to know how to take the .sql file that appears in the repository, pull out the sql statement and load it into a variable to be executed in the OLE DB source.

Why don't you use in the Execute SQL Task the following approach: Set SQLSourceType property to File connection; Then define this File connection just below it to point the file with the SQL statement to execute. Now regarding the capture of the output variable, I am not quite sure how you want to consume that "long".Arthur My Blog

Looks like OLEDB source might not be able to read from the file but it can read from variable. As far as I know, the only option is to get the object variable inside the script task and get the results out using the datareader and wrtie to a text file using streamwriter. Somthing like this to read object variable: Dim olead As New Data.OleDb.OleDbDataAdapter Dim dt As New Data.DataTable olead.Fill(dt, Dts.Variables("objectvariable").Value) For Each row As Data.DataRow In dt.Rows 'Get the data here using row("columnname1").ToString() End If Next Use Streamwrtier to write the column values to a text file. Happy to help! Thanks. Regards and good Wishes, Deepak.

It's not going to be particularly pretty either way you do it - but the major decider is... will the column metadata change when that SQL command file changes? If the statement(s) contained in the file will cause a different set of columns (number & data types) to be returned, then you don't want to use the Data Flow Task. If you use the Data Flow, then yes, use the OLE DB Source's "from variable" option. You'll need to use a Script Task prior to the Data Flow Task to read the file contents into a string variable. Create an SSIS string variable, edit the Script Task to mark that as a ReadWriteVariable, use System.IO.File.OpenText to read the contents into it. If you use the Execute SQL Task, use the File Connection as ArthurZ suggested, and send the resultset to an SSIS Object variable. You then need to "shred" the recordset in a subsequent Script Task and do what you want with it. Talk to me now on

Uh - what Arthur suggests IS WHAT I AM DOING!!!! :-) Go back to the top and re-read my initial post. I really appreciate the replies. Once I figure this out on my own I will post the solution. Frustrating though.

It'd be nice if you read the rest of my post - including the link there... Talk to me now on