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 2005 Forums
 Other SQL Server Topics (2005)
 Download and extract files from remote server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dorsun
Starting Member

2 Posts

Posted - 10/17/2013 :  05:42:53  Show Profile  Reply with Quote
I have a requirement to connect to remote server and download CAB files to local server. Need to extract the CAB files which contains a CSV file and write the contents to the database table. Sql Server 2005.

What are the best possibl solutions for this?

1. Do we need to write code in .NET and call the dll in sql?

2. Write a CLR function for this task?

3. Is there a way to have pure tsql commands to get this done?

Thanks,

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 10/17/2013 :  08:32:46  Show Profile  Reply with Quote
quote:
Originally posted by dorsun

I have a requirement to connect to remote server and download CAB files to local server. Need to extract the CAB files which contains a CSV file and write the contents to the database table. Sql Server 2005.

What are the best possibl solutions for this?

1. Do we need to write code in .NET and call the dll in sql?

2. Write a CLR function for this task?

3. Is there a way to have pure tsql commands to get this done?

Thanks,


Just based on the information you posted, SSIS seems like perhaps the best option. It is quite flexible and is applicable to a wide range of sources and destinations.

You could write .Net code to do all of that, but I would not try to call that from a T-SQL procedure. I would create an executable and then run that from outside SQL Server.

You could do this with CLR procedures, but it is probably best not to do that because you will have to give unsafe access to the CLR procedures and accept the associated security threats etc.

I don't know of a way to do this using pure T-SQL, unless the CAB files were stored in a SQL database on the remote server. Even so, to unzip them, you would need to access the OS. IN other other words, not a recommended approach.

So bottom line: I would investigate SSIS first.
Go to Top of Page

dorsun
Starting Member

2 Posts

Posted - 10/18/2013 :  02:31:50  Show Profile  Reply with Quote
quote:
Originally posted by James K

quote:
Originally posted by dorsun

I have a requirement to connect to remote server and download CAB files to local server. Need to extract the CAB files which contains a CSV file and write the contents to the database table. Sql Server 2005.

What are the best possibl solutions for this?

1. Do we need to write code in .NET and call the dll in sql?

2. Write a CLR function for this task?

3. Is there a way to have pure tsql commands to get this done?

Thanks,


Just based on the information you posted, SSIS seems like perhaps the best option. It is quite flexible and is applicable to a wide range of sources and destinations.

You could write .Net code to do all of that, but I would not try to call that from a T-SQL procedure. I would create an executable and then run that from outside SQL Server.

You could do this with CLR procedures, but it is probably best not to do that because you will have to give unsafe access to the CLR procedures and accept the associated security threats etc.

I don't know of a way to do this using pure T-SQL, unless the CAB files were stored in a SQL database on the remote server. Even so, to unzip them, you would need to access the OS. IN other other words, not a recommended approach.

So bottom line: I would investigate SSIS first.




Thank you James for your inputs.
Can you please give details on how you can create an executable and run that from outside SQL Server?
Also, can you please give some samples on how to do this using CLR procedures.
Will there be any special permissions needed for running the SSIS? Also can you provide some sample package that can do this task.
I will have to provide alternate solutions and not just one.

Thank you.
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.27 seconds. Powered By: Snitz Forums 2000