November 30, 2016 at 12:36 pm
Does anyone know of a good article on Outputting a Full Result Set to a Table?
I have the SQL Task created but is about it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 2:08 am
This is quite a vague question, and solutions can vary depending on what you're doing and where the data is coming from. Could you give a little more information about what your aims and goals are, and what you're trying to achieve? Even a broad sweep of something like "I have 20 Excel files, which are delivered on a weekly basis, need to be transferred into a database and relationship between keys achieved. What's the best way of getting all the data into SQL tables, most likely staging, and I will do the relational building from there" would give us at least some idea of what you want to do.
Thanks ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2016 at 8:38 am
Do you mean a stored procedure that returns to 'with results set' in SSIS?
- Damian
December 1, 2016 at 8:41 am
You mean like using a query/stored procedure as source in the Data Flow task?
That's part of SSIS 101.
December 1, 2016 at 9:05 am
ok, I'm sorry for not being clear.
The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets">
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 9:22 am
That article there is talking about returning a single row and then inserting those values into variables. Is that your aim here? Your title says that you want to put a full result set into a table, ergo Move a dataset from one table to another. You wouldn't really need SSIS to do this, depending on your circumstance. For example, this would simply achieve your goal on a very simple matter:
USE DevTestDB;
GO
IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'CurrTables') GO
DROP TABLE CurrTables;
END
GO
SELECT t.name
INTO CurrTables
FROM sys.tables;
GO
Can you provide more information please.
Are you actually looking to loop through an entire dataset and load each row/column to a varaible, so you can achieve something else?
Cheers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2016 at 9:25 am
Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets">
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.
December 1, 2016 at 9:28 am
Luis Cazares (12/1/2016)
Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets">
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.
OK I'm using an Execute SQL Task with a select query.
Please refer to the article.
I assume that I did not do it correctly.
How do I move forward?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 9:33 am
Welsh Corgi (12/1/2016)
Luis Cazares (12/1/2016)
Welsh Corgi (12/1/2016)
ok, I'm sorry for not being clear.The following article is great but it does it drops off on returning full record set.
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets">
https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets
I need to get the result set into a table.
Thank you!:-)
Edit correct URL
You don't need an Execute SQL Task, you need a Data Flow Task and use a Query as the Source.
OK that is what I'm doing.
How do I move forward?
Stand up, put on foot in front of the other, do the same with the other foot and repeat as necessary.
If that's not what you need, be sure to explain yourself CLEARLY.
December 1, 2016 at 9:34 am
AS Luis said, you don't need an execute, you need a dataflow. Place a dataflow task on your contro,l flow, open it up. Place an OLE DB source, and an OLE DB Destination. If you need any transformations, place those as well and then, just like the control flow, join up the nodes with the lines 🙂
We really can't give you any more than that as we don't know what you're trying to achieve.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2016 at 9:37 am
I just ask for guidance.
I think that it is pretty clear what I want.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 9:39 am
Welsh Corgi (12/1/2016)
I just ask for guidance.I think that it is pretty clear what I want.
I'm sure that you've been guided to this article before, but this is an example on how it's not clear.
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
I also shared with you an article that guides you through the process in my previous post.
December 1, 2016 at 9:41 am
Welsh Corgi (12/1/2016)
I just ask for guidance.I think that it is pretty clear what I want.
All you've asked is "How do you get all the rows from a table in SSIS". That doesn't give us any information, as I said. What is your goal? What are you doing with all these rows? Are you putting them into another table? Extracting them into CSV files? Looping round them and sending emails?
Every one of these has a different answer.
Your question is about as clear as "How do I cook?". Without knowing what it is you want to cook, someone would find it very hard to tell anything helpful.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 1, 2016 at 9:45 am
Thom A (12/1/2016)
That article there is talking about returning a single row and then inserting those values into variables. Is that your aim here? Your title says that you want to put a full result set into a table, ergo Move a dataset from one table to another. You wouldn't really need SSIS to do this, depending on your circumstance. For example, this would simply achieve your goal on a very simple matter:
USE DevTestDB;
GO
IF EXISTS (SELECT [name] FROM sys.tables WHERE [name] = 'CurrTables') GO
DROP TABLE CurrTables;
END
GO
SELECT t.name
INTO CurrTables
FROM sys.tables;
GO
Can you provide more information please.
Are you actually looking to loop through an entire dataset and load each row/column to a varaible, so you can achieve something else?
Cheers.
I believe that you are right. I do not need output to an SSIS Full result Set when I can simply use T-SQL.
Thank you for your courteous response. 😎
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 1, 2016 at 10:04 am
With ssis you can
Create an sp that performs your select.
Use the sp as the source using with result sets followed by all the columns you require along with their type.
Then map to a destination.
I've used this quite a bit as you can include temp tables without any issues.
- Damian
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply