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 |
|
dorsun
Starting Member
2 Posts |
 Posted - 2013-10-17 : 05:42:53
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-17 : 08:32:46
|
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. |
 |
|
|
dorsun
Starting Member
2 Posts |
Posted - 2013-10-18 : 02:31:50
|
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. |
|
|
|
|
|
|
|
|
Download and extract files from remote server