October 2, 2018 at 1:31 pm
I have to Export data from ServerA and generate .csv files and then move files to other department for warehousing activity and import data to ServerB etc.
For this purpose, I have tried to generate .fmt files which only works when a table is provided for BCP "out" and it works perfect when importing the .csv files back to warehousing database tables.
I have sometimes queries not full tables like delta (today's data), when I generate .fmt files by providing "SELECT col1, col2, ... FROM Table" queryout to generate .fmt file, it generates the files, but when BCP queryout is used with SELECT by providing the format .fmt it won't follow the format to export data as mentioned in .fmt file and do not import data to tables, In short, when full table name is provided and generated .fmt it works well for import. But when data export using SELECT with its .fmt it do not import data back to other tables. I have no schema issue at all. Its test and i used same server, database, two tables with same schema same columns just different table names. BCP with SELECT queryout generated data files won't import.
Anyone faced similar issue and resolved, please share solution.
Regards.
October 2, 2018 at 1:51 pm
I would not try to build this process manually using BCP - I would utilize SSIS to manage the workflow through all steps. In SSIS you can use a Multicast transformation to send the data from ServerA to separate destinations.
This would allow you to extract the data from ServerA and multicast to a file connection (for the CSV file) and an OLEDB destination (for ServerB). No need for format files - or batch files to run separate processes and the creation of the file and loading to the data warehouse tables happen at the same time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 2, 2018 at 11:58 pm
Your response drive me to other direction, we used SSIS too. But it is somehow requirement as I asked earlier.
We need to create a separate process as OFFLINE, for data movement to other systems. we are not allowed to engage the live system for long so we must separate required data as soon as possible and release the database systems for their 24/7 online activity.
Background of my query is, I am working on a small c# application which reads some details above SourceDB as defined in table like (Oracle, SQL Server, DB2 etc.) and read their corresponding queries mentioned against SourceDB (in a separate table) and then execute "BCP" by making command parameters/pick all queries and run one by one. This way it will LOG results and we then show the results of export list on application dashboard.
When we have new tables to export, we just have to make an entry of those new tables in our "DataExportDB" database, the application read those names and pass to execute method (BCP), we can mark any queries to active/in-active, if we do not need to export in future. In short we can control/manage our Data Export System as we want. Some of the systems will be running daily to export data and some queries we may want to schedule for one time only, depending on our needs.
I only see the BCP can do faster then any other utility, so I used it in my POC. Hence, I am stuck here why BCP has format issue because the exported .csv files after all they will be going to import in some database (Either Oracle, SQLServer DB2 or MySQL etc.) depending on the business requirements. We need to provide a solution that can handle "IN" and "OUT" from any source to any destination. Managed by application. End users can run them easily and they can see the results of EXPORT/IMPORT FROM and TO as they want.
Users can fire any export/import based on their own as needed.
I just want to know if someone tried what I asked and resolved the matter, how? except SSIS or can SSIS do such management easily. I think for each table to export or import, I have to develop it in SSIS twice. There are thousands of tables. We are not buying any third party, we have to implement such management in-house. Please guide.
October 3, 2018 at 2:38 pm
shamshad.ali - Tuesday, October 2, 2018 11:58 PMYour response drive me to other direction, we used SSIS too. But it is somehow requirement as I asked earlier.
We need to create a separate process as OFFLINE, for data movement to other systems. we are not allowed to engage the live system for long so we must separate required data as soon as possible and release the database systems for their 24/7 online activity.Background of my query is, I am working on a small c# application which reads some details above SourceDB as defined in table like (Oracle, SQL Server, DB2 etc.) and read their corresponding queries mentioned against SourceDB (in a separate table) and then execute "BCP" by making command parameters/pick all queries and run one by one. This way it will LOG results and we then show the results of export list on application dashboard.
When we have new tables to export, we just have to make an entry of those new tables in our "DataExportDB" database, the application read those names and pass to execute method (BCP), we can mark any queries to active/in-active, if we do not need to export in future. In short we can control/manage our Data Export System as we want. Some of the systems will be running daily to export data and some queries we may want to schedule for one time only, depending on our needs.
I only see the BCP can do faster then any other utility, so I used it in my POC. Hence, I am stuck here why BCP has format issue because the exported .csv files after all they will be going to import in some database (Either Oracle, SQLServer DB2 or MySQL etc.) depending on the business requirements. We need to provide a solution that can handle "IN" and "OUT" from any source to any destination. Managed by application. End users can run them easily and they can see the results of EXPORT/IMPORT FROM and TO as they want.
Users can fire any export/import based on their own as needed.
I just want to know if someone tried what I asked and resolved the matter, how? except SSIS or can SSIS do such management easily. I think for each table to export or import, I have to develop it in SSIS twice. There are thousands of tables. We are not buying any third party, we have to implement such management in-house. Please guide.
You'll have to provide details on exactly what didn't work, what error messages were received, and details of the tables where this occurs. It might be something you forgot to account for in the export process, but with no details, we're not going to do any better than a wild guess...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2018 at 4:55 pm
First of all, if your target is another SQL Server, stop using a CSV format. Use the "Native" format. It will be faster to export and faster to import.
Shifting gears back to the original problem... why are you doing all of this? Is this some attempt at "replication" to keep Server B in sync with Server A?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2018 at 8:47 pm
As mentioned earlier, I have to provide a solution in c# application which can accept any source db and can Export data in files format. Then those files can be Imported to any destination, (Source and Target could be Oracle, DB2 or SQL Server).
The purpose is to setup a repository to do this work on daily bases, User input source db, tables/queries and set the output file name. Application runs on the schedule and export data on their respective folders. The same will be for import process, users setup target db and mention data files to import on particular schema/tables. The source and destinations databases could be any (Oracle, Db2 or SQL Server). The application will log details about any error or success and show reports on dashboard.
I started to export SQL Server using BCP and its working fine, same is used to import data to SQL Server, now I have to import data files (.csv) to Oracle and DB2, then the export process from DB2 and Oracle, etc.
Anyone have better suggest, let me know.
Regards,
October 6, 2018 at 8:30 pm
shamshad.ali - Friday, October 5, 2018 8:47 PMAs mentioned earlier, I have to provide a solution in c# application which can accept any source db and can Export data in files format. Then those files can be Imported to any destination, (Source and Target could be Oracle, DB2 or SQL Server).We have more than 80 different applications databases mix up of above mentioned database environment type. With this application, it could be easy handle data and share to different departments for their needs including development/debugging, testing divisions could easily import using this application.The purpose is to setup a repository to do this work on daily bases, User input source db, tables/queries and set the output file name. Application runs on the schedule and export data on their respective folders. The same will be for import process, users setup target db and mention data files to import on particular schema/tables. The source and destinations databases could be any (Oracle, Db2 or SQL Server). The application will log details about any error or success and show reports on dashboard.
I started to export SQL Server using BCP and its working fine, same is used to import data to SQL Server, now I have to import data files (.csv) to Oracle and DB2, then the export process from DB2 and Oracle, etc.
Anyone have better suggest, let me know.Regards,
It sounds to me like the next big project for the company would be to settle on a common technology and the use of CSV files probably isn't it. 😉
Shifting gears to the subject at hand, I wouldn't use so called "CSV" because most people don't stick to the true "CSV" standard. If I had to push for something simple with the understand that while it may be better, it's still not a panacea, I'd push for "TSV" files instead of "CSV".
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2018 at 12:42 am
TSV = Tab separated, CSV is comma separated, is this what you mean? It is just a matter of giving extension to a data file.
I have given a different column and row separator when exporting data, comma is usually found in text (varchar) columns and when exporting data it mess up things and waste time, so we have used some other separator depending on user choice it would generate the file accordingly. However, the extension is .csv does not matter we don't use them for viewing in EXCEL or in notepad ++, usually files are more than 5GB in size to 22 GB per table which is no possible to open in these software. Still need suggestion if someone has any better solution or help me guide on my task/project.
October 7, 2018 at 9:23 am
shamshad.ali - Sunday, October 7, 2018 12:42 AMTSV = Tab separated, CSV is comma separated, is this what you mean? It is just a matter of giving extension to a data file.
I have given a different column and row separator when exporting data, comma is usually found in text (varchar) columns and when exporting data it mess up things and waste time, so we have used some other separator depending on user choice it would generate the file accordingly. However, the extension is .csv does not matter we don't use them for viewing in EXCEL or in notepad ++, usually files are more than 5GB in size to 22 GB per table which is no possible to open in these software. Still need suggestion if someone has any better solution or help me guide on my task/project.
The extension of .csv does matter because it will confuse people if you use some other separator. They will waste their time trying to review the data in Excel.
And, no... I wasn't talking about the extension of CSV for the very same reason you posted. The data will frequently have commas in it that are not field terminators and so some other terminator would be appropriate. I suggested TSV as tab separated but anything not in the actual text to be extracted will do provided those on the receiving end actually know what both the field and row terminators are.
As for your overall project, since you want a "repository" of files, then using BCP to extract the data from SQL Server and putting the various outputs into files in the repository would work just fine provided that the people using the data know how to import it on their end. If not, you'll need to either provide them with such knowledge or write the code for them to do the imports.
You'll also need to do the reverse for the exports from the other systems unless you install the correct drivers/providers on SQL Server and simply pull the data directly from those other sources.
Another option would be to buy some 3rd party software that already has been tested to do all of this for you.
I'll say it again, though... it seems that you have multiple technologies that need to talk to each other. You're attempting to setup a "poor man's replication" between them. I'd seriously consider migrating some, if not all, the technologies to just one. I know that's a large task for some but so is the continuous disparity between unlike technologies.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2018 at 12:05 pm
shamshad.ali - Friday, October 5, 2018 8:47 PMAs mentioned earlier, I have to provide a solution in c# application which can accept any source db and can Export data in files format. Then those files can be Imported to any destination, (Source and Target could be Oracle, DB2 or SQL Server).We have more than 80 different applications databases mix up of above mentioned database environment type. With this application, it could be easy handle data and share to different departments for their needs including development/debugging, testing divisions could easily import using this application.The purpose is to setup a repository to do this work on daily bases, User input source db, tables/queries and set the output file name. Application runs on the schedule and export data on their respective folders. The same will be for import process, users setup target db and mention data files to import on particular schema/tables. The source and destinations databases could be any (Oracle, Db2 or SQL Server). The application will log details about any error or success and show reports on dashboard.
I started to export SQL Server using BCP and its working fine, same is used to import data to SQL Server, now I have to import data files (.csv) to Oracle and DB2, then the export process from DB2 and Oracle, etc.
Anyone have better suggest, let me know.Regards,
I think you are going to have way more issues than what types of files to use for the data transfers. How are you going to handle data type issues - especially if exporting from SQL Server and trying to import that data into Oracle or DB2? What is the data type in Oracle for SQL Server's MONEY data type? What about date, time, datetime and datetime2 data types - what are the corresponding data types in Oracle and DB2? Floats/Reals/Numeric/Decimal - do they match up in the other systems and how do you code for every data type?
You also have to consider character data - and whether or not you are translating NVARCHAR to VARCHAR2 or NVARCHAR (depends on how the database was setup in Oracle - or what settings are defined in DB2). You also have to map (N)VARCHAR(MAX) to the appropriate data type in Oracle/DB2 - and let's not forget XML...can that even be translated?
Unless you have defined the destination table to be exactly the same as the source table (why would that be the case?) - then this really isn't going to work out very well.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply