TXT file for each table
-
i need to output all the tables but 2 from the database to a TXT file using SSIS? how can i achieve it?
-
If it is a once off, use BCP. The work required to build a package like that is not worth it.
If you really wanted to, You would have to create a package programatically, dynamically adding the source table and the desitnation. Also not work it for a once off.
If it is a many use thing, create a package with many data flows from source table to text file destination.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
can I use ssis to extract all the tables including data and put them into a multiple txt files using their respective names? its a incremental extract. DAta from yesterday only. I need to do it daily.
I used a foreach loop container and dataflow task to create the TXT files with the table names. But there is no data. I need to know how to put the data in as well. any tutorial or input will be helpful
-
You can accomplish what you are trying to do. That is what SSIS was built for....
A forloop is not needed though. You cannot loop over all the tables and extract the data. You need to create a data flow for each table.
As for tutorials, here are 40,400 of them. A little leg work goes a long way..
Start off with a SQL source, and a text file destination.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thats insane I have 1000 tables. IT would take me an eternity to finish 1000 tables. So foreach loop wont do it? remember i also have to rename the txt files with the table names.
-
If the tables have different schemas then no, you cannot use a loop. SSIS does not cater for runtime changes.
If however you have 1000 of the same table, then you can use a loop.
Renaming the files is simple, look at using expressions on the connection managers.
If you have 1000 differnt tables, write a batch script that uses BCP, there will not be a quicker way.
You might try using the export wizard and then save that as a SSIS package.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
You could do what you want programmatically from an application platform (eg., .net, classic asp, vb, c) as long as it has the ability to access SQL Server data - you could probably do it in VBA from Access or even Word. You could get a list of tables from sys.tables, then iterate over the list - a simple forward-only recordset (select * from tablename where DateUpdated = yesterday), then write to a text file. It certainly could take a while, especially if the recordsets are pretty big, but you would not have to hard-code a list of tables.
Or, maybe I didn't understand the question...
Eric
[font="Tahoma"]Eric Flamm, Flamm Consulting[/font]
-
I BCP THE tables out + data. Now how can I do incremtanl extract like using changeDate = Yesterday?
-
What options do you have with BCP? Query possibly?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
What the heck is the matter with using Replication?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
bcp is not very format friendly.
how do i extract data from yesterda using replication ? whgat about the format? and i want it to extract to txt files.
bASICally i just want to export the data from yesterday from my 40 tables. Can ssis do this?
or should i just write individual codes for every table? your input will be appreaciated?
I remember I seen this process in the company i worked before and they did incremental extract by writing individual code for every table. So should i follow this or can ssis do this? I was able to just create the files with the 40 table names using ssis but the data part is hard to get it done. the data is not coming with the txt files.
-
What defines a friendly format? Delimited by fluffy bunnies and pink flowers?
If you did a bit of reading on BCP, you would find you can format it with whatever you want. (Excluding aforementioned bunny rabbits)
Replication - I am not sure how you would dump that to text file. Seems like a sledge hammer to crack a nut but there you go.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
bluepaperbag (8/15/2008)
I BCP THE tables out + data. Now how can I do incremtanl extract like using changeDate = Yesterday?Sorry... I didn't read all of the previous posts on this thread. Like Crispin suggests, you can use the QUERYOUT ability of BCP to do this. If you can (uses xp_CmdShell), you could do this from a stored procedure that looks up the tables...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
pleasehelpme (8/17/2008)
bcp is not very format friendly.how do i extract data from yesterda using replication ? whgat about the format? and i want it to extract to txt files.
bASICally i just want to export the data from yesterday from my 40 tables. Can ssis do this?
or should i just write individual codes for every table? your input will be appreaciated?
I remember I seen this process in the company i worked before and they did incremental extract by writing individual code for every table. So should i follow this or can ssis do this? I was able to just create the files with the 40 table names using ssis but the data part is hard to get it done. the data is not coming with the txt files.
Sorry, my mistake... I didn't read most of the previous posts on this thread.
Replication isn't going to work for exports,.
Are your 40 tables identical column wise or are they all different? You say you want to export to txt files, but you didn't say what format in the text file you need...
And what do you mean that you were able to create the files but no data? What are you using for that?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Mr. Jeff,
Yes, the 40 tables have different columns but they all have the same createdate and changedate columns which helps for the incremental extract.
The format has to be pipe delimited with a comma. Something like:
|col1|,|col2|,|col3|...
|row1|,|row1|,|row1|...
And what do you mean that you were able to create the files but no data? What are you using for that?
I used SSIS. Dataflow->Foreachloop and a Dataflow inside the Foreachloop. Im just capturing table names with a user variable with the Dataflow and pass it to another map variable then use the Foreachloop to create the .txt files with these names. But seems like SSIS can only create TXT files and it doesnt allow dynamic export of data for individual tables. I read that if the source gets change in the package, the columns remains the same. So I have to recreate a new package for a new table. Basically SSIS won't let me do dynamic export from several tables to TXT files. I'm guessing I will have to use BCP. But BCP is not very format friendly.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply