Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2013-07-18 : 12:01:30
|
| Hi guys,One of the known challenges with SSIS is that it requires one to specify the file structure when you want to import or export a file. It is a challenge when one tries to generalize a file export.I have a requirement where I will be given a table in a database and need to just export the whole table into the CSV file. I don't know anything about the table structure prior to the request. Doing this in SSIS was not intuitive due to file structure definition requirements.I've looked into using BCP, and am now exploring powershell. I am totally new to powershell and while I was able to write a script to export out a table from the database into .csv file, I am not sure how to hook it up to SSIS.If anyone has been calling powershell from SSIS, could you please share what you had to do to make it happen?1. Any initial set up on the processing server?2. How did you store the code?3. How did you execute the code from SSIS?4. Any additional caveats to keep in mind?Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-18 : 14:37:24
|
| Whats the problem with BCP? bcp will help you to export to text file data based on your query------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2013-07-18 : 16:27:52
|
| There were a couple of problems with BCP:1. The headers would not get exported out, and so we had to create a stored procedure that makes 2 BCP calls (1st for headers and 2nd for data) and then a 3rd command line call to merge header + data outputs. And when we did combine the data, there would always be some weird character at the end that looked like a carriage return that we could not get rid of2. The code was quite involved as we had to loop through the info schema etc., and we always try to look for elegant solutions3. Some of our tables have NVARCHAR columns, and we could not get BCP to work well with those - CSV file outputs would have all the columns shown in 1st row (not sure if this is the issue with BCP or excel not understanding the unicode output)Due to these reasons we decided to investigate other solutions ... and also for the fun of trying to see how the same problem could be solved by other tools - power shell in this case. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-19 : 01:00:50
|
quote:
Originally posted by sql_er
There were a couple of problems with BCP:1. The headers would not get exported out, and so we had to create a stored procedure that makes 2 BCP calls (1st for headers and 2nd for data) and then a 3rd command line call to merge header + data outputs. And when we did combine the data, there would always be some weird character at the end that looked like a carriage return that we could not get rid of2. The code was quite involved as we had to loop through the info schema etc., and we always try to look for elegant solutions3. Some of our tables have NVARCHAR columns, and we could not get BCP to work well with those - CSV file outputs would have all the columns shown in 1st row (not sure if this is the issue with BCP or excel not understanding the unicode output)Due to these reasons we decided to investigate other solutions ... and also for the fun of trying to see how the same problem could be solved by other tools - power shell in this case.
1. the following code shows how to get column headers while doing bcphttp://dotnetkeeda.blogspot.in/2009/09/bcp-command-to-export-data-to-excel.html2. obviously you either need to have a consistent metadata beforehand or you need to rely upon catalog views if you want to get file structure which is varying3.Sorry didnt understand this. Illustrating with example will make it clear------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2013-07-19 : 17:18:39
|
| Hi visakh16,Thank you for providing the link to the code.I'll give it a test run and come back with questions! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-20 : 08:25:02
|
| you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dgh898
Starting Member
10 Posts |
Posted - 2013-08-08 : 23:10:25
|
| unspammed |
|
|
|
|
|
|
|
|
Calling a powershell script from SSIS package