I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).
Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).
Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.
Thanks for any pointer
Thibaut Barrère
Once built your packages should be error free, unless something changes. If this is a feature, then SSIS is not going to ber a good solution. BCP can be better because it is simple enough to just dump a table without knowing structures in advance. Bulk Insert Task offers this as well, but there is no Bulk Export Task. I wrote one for DTS, because I liked the way you did not have to manage the changes, just keep source and destination in synch.
(Where did the Xml Source come from, you mean CSV I assume as that was the export format you said.)
For now I'd use BCP, and probably the raw format as well.
|||Thibaut Barrère wrote:
Hi! I'm looking for the most convenient way of copying 4 tables between two databases which cannot connect to each other directly (I have to go through a filesystem and ftp).
Most of the time I use the CSV import / export from SSIS, but found out that it can be error-prone (have to carefully pick-up the same culture, text delimiter, and configure the XML source on the other side - I don't have many metadatas here except for the column names, so it leads to truncation errors etc sometimes).
Is there a better way to achieve this under SSIS ? So far I didn't actually invoke bcp, as I was looking for a more 'ssish' way of doing this.
Thanks for any pointer
Thibaut Barrère
SSIS isn't really a tool for managing objects, only data. Hence I like to rely on SQL scripts for deployig the objects (Very easy, just generate the script in SSMS, change the connection, and hit execute) and use the Import/Exprt wizard to pump data between them.
Or try Darren's method!
-Jamie
|||
This could well be part of a script based deployment scenario, and in that scenario the data Import/Export does not cut it for me. You would have to manually maintain or at least run the Wizard, and in this case twice, since we need to stage in files, as you cannot do direct. Makes sense to allow you to version control it as well.
Using BCP to build the data "script" works rather well here, and is just easier to maintain and faster compared to other methods like the Wizard, or generating scripts.
|||Hi!
thanks for your answers first. Actually I'm not designing a backup strategy or a deployment of some kind : I have a consolidation process on a production machine, and I want to take benefits of a couple of tables which are handled outside the production site (inhouse tables), to achieve clean-up, lookups etc.
What I'm looking for is the easiest way of using this bunch of tables in production, as data sources for the consolidation process.
Following your advices, I've tried bcp and it works just perfectly to export the data. But when calling it for import, sometimes it doesn't insert some rows, but won't return a non-zero errorresult either (I've googled and saw that it seems to happen to others) ! This is quite embarrassing - did you meet such an issue ?
I've also tried the import/export method - but is it supposed to work if I have relationships and constraints between my source tables ?
cheers
Thibaut
