Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Download and extract files from remote server Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 3:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:03 AM
Points: 28, Visits: 73
Hi,


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 possible 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,
Post #1505553
Posted Thursday, October 17, 2013 3:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 13,520, Visits: 11,313
You could use SSIS to do this.
How are you planning to download the file? FTP?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505560
Posted Thursday, October 17, 2013 3:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:03 AM
Points: 28, Visits: 73
I have a constraint that I cannot use SSIS. Can this be possible with just sql?
Post #1505563
Posted Thursday, October 17, 2013 4:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
This is a bit like being given a nail and told you're not allowed to use a hammer. Why can't you use SSIS?

John
Post #1505568
Posted Thursday, October 17, 2013 4:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:03 AM
Points: 28, Visits: 73
The requirement is such. What will be the solution using SSIS, so that it can be considered. please let me know.
Post #1505571
Posted Thursday, October 17, 2013 4:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 31, 2014 11:06 AM
Points: 5,430, Visits: 10,108
If you've never worked with SSIS before, I recommend that you search the web for a "getting started" tutorial. That will help you to create a basic package.

John
Post #1505573
Posted Thursday, October 17, 2013 4:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:03 AM
Points: 28, Visits: 73
I am familiar with SSIS, but not sure of how to get this task done.
Post #1505577
Posted Thursday, October 17, 2013 4:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 13,520, Visits: 11,313
dorothy.burton (10/17/2013)
I am familiar with SSIS, but not sure of how to get this task done.


Depends on how you want to download the file.
Could be with the FTP task, or with some .NET. (Downloading a file over HTTP the SSIS way)

Extracting could be done with an Execute Process Task (or with some .NET if you want to).
Importing the .csv files can be done with a data flow inside a for each loop.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505584
Posted Thursday, October 17, 2013 5:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, May 12, 2014 3:03 AM
Points: 28, Visits: 73
How to download using .NET code. I have "ftpservername", "ftpdirectory" and the "filename" details.
I need to copy this file to my local server.
Post #1505595
Posted Thursday, October 17, 2013 5:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 13,520, Visits: 11,313
dorothy.burton (10/17/2013)
How to download using .NET code. I have "ftpservername", "ftpdirectory" and the "filename" details.
I need to copy this file to my local server.


Why not use the FTP task?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse