May 21, 2008 at 8:54 am
Hello,
I am trying for a few days to export some data from my MS SQL database with no luck.
Can you please help?
I want to extract a number of fields from a number of tables into a txt file.
because I need to export the data from a number of tables and to a number of files I need to program something flexible:
I am using :
USE [Wind]
DECLARE @ThisMonth AS int
DECLARE @ThisYear AS int
SET @ThisMonth = 8;
SET @ThisYear = 2006;
select EventTime,PCTimeStamp,T_ave,P_ave,U_ave,Count_TimeReady,Count_TimeConnectedToGrid,Count_TimeReady,Count_T1,Count_T2,Count_T3,Status_ErrorCode,CountSecondsInThis10MinPer,CountSecondsEventCodeNotZero from [wind].[17182]
where DatePart(MONTH, EventTime) = @ThisMonth
and DatePart(YEAR, EventTime) = @ThisYear
select EventTime,PCTimeStamp,T_ave,P_ave,U_ave,Count_TimeReady,Count_TimeConnectedToGrid,Count_TimeReady,Count_T1,Count_T2,Count_T3,Status_ErrorCode,CountSecondsInThis10MinPer,CountSecondsEventCodeNotZero from [wind].[17184]
where DatePart(MONTH, EventTime) = @ThisMonth
and DatePart(YEAR, EventTime) = @ThisYear
....
and then i manually select export to file, and I select the appropriate file. Because i need to do this approximately 300 times, it would take me a lot of time to do!
So I tried to perform this with the bcp command but I was unsuccessful in all my tries...
can anyone suggest a way of doing this?
May 21, 2008 at 9:19 am
Have you looked at using SSIS to do this? You could define a package that contains all the selects and text file destinations and use package variables for your year and month to grab.
If the field names are the same you can use a variable for your select as well where you just change your table name in each dataflow task. Or, if the table names are incrementing numbers you could use a for loop container to increment the table name and the text file name.
The idea is to create package variables for Year, Month, SelectStatement, WhereClause, Query, TableName, TextFileName then the loop would be like this (pseudocode):
WhereClause = Where "EventYear = " + Year + " and EventMonth = " + Month
for TableName = x to n
Query = SelectStatement + TableName + WhereClause
TextFileName = TableName + Year + Month
Run Dataflow task where the Source Query is the Query variable and the destination is TextFileName
next
This should give you an idea anyway.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 9:23 am
SSIS could be used for this type of extraction.
May 21, 2008 at 1:25 pm
Or just use good ole' fashioned bcp.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 24, 2008 at 4:22 am
Thank you all,
I tried the SSIS option and I have set it up to export all the data I want into approximately 50 excel sheets (one for each month's worth of data). Since the data-set is too large I have split it in two SSIS procedures (exporting 25 and 25 excel sheets each into the same excel file).
I have done this successfully for the data from 1 table but I need to do this for 40 or so tables. Is there an easy way of doing this? (If only I could see the source code and edit it instead of making modifications in the graphical interface, I would just copy the SSIS code I have written 40 times and just change the name of the source table in each of the copies).
And one last thing. I run the SSIS files by double clicking the files in the folder I created them in. Is there any way I could run them through a query or something? (I cannot write a standard cmd (DOS stile) script because when you run the SSIS file it opens a window and you need to press execute).
I hope I am not asking too many questions (hopefully these are my last ones!!)
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply