SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Calling a powershell script from SSIS package
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 07/18/2013 :  12:01:30  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 07/18/2013 :  14:37:24  Show Profile  Reply with Quote
Whats the problem with BCP? bcp will help you to export to text file data based on your query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 07/18/2013 :  16:27:52  Show Profile  Reply with Quote
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 of

2. The code was quite involved as we had to loop through the info schema etc., and we always try to look for elegant solutions

3. 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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/19/2013 :  01:00:50  Show Profile  Reply with Quote
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 of

2. The code was quite involved as we had to loop through the info schema etc., and we always try to look for elegant solutions

3. 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 bcp
http://dotnetkeeda.blogspot.in/2009/09/bcp-command-to-export-data-to-excel.html

2. 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 varying

3.Sorry didnt understand this. Illustrating with example will make it clear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 07/19/2013 :  17:18:39  Show Profile  Reply with Quote
Hi visakh16,

Thank you for providing the link to the code.

I'll give it a test run and come back with questions!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/20/2013 :  08:25:02  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dgh898
Starting Member

10 Posts

Posted - 08/08/2013 :  23:10:25  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000