Post reply

Importing CSV file from remote location..

  • September 29, 2008 at 2:46 am

    #193753

    I had posted this topic 2 days back..Unfortunately din't get even a single reply..Is it not possible at all..?

    Please help.....

    Dear All,

    I have made a package using SSBIDS to import data fom CSV file to a table in database.

    Now I want to know 2 important things.

    How can I import a CSV file from remote Server..??

    How can I execute this package from Cold Fusion file..??

    Please help..It's very urgent.

    Thanks in advance.

    Santhosh Nair.

  • September 29, 2008 at 9:17 am

    #878454

    Santhosh,

    Your question is too large to write a full step by step answer. I will tell you at a high level how to do it.

    First, it is possible to import just about any type file to SQL Server. I'll assume that you are using SQL Server 2005.

    If you're using SQL Server 2005, open and launch dtswizard. If SQL Server is installed on the C:\ drive of your server, the path would be: C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

    Use this wizard to step through creating a SQL Server Integration Services (SSIS) package to import the file. You will need to specify the UNC path to it, and you (as well as your SQL Server) will need read access to it.

    Once you step through the wizard you have the option to either run and/or save your package. If this is a one-time thing you can just run it. If you need to repeat it, choose to save the package in your server. This is also handy to take a look at the package to learn more about the process.

    Once you've created, saved and tested your package you may need help understanding the parameters to execute it. The best way to generate the command to execute the package is to use the dtexecui.exe (2005, dtsrunui.exe in SQL 2000)

    Using this utility you can navigate too, and generate the command line to call the package.

    I have no experience with Cold Fusion so I cannot tell you specifically how to do it. If you can drop to a command prompt, you can call the package with the line you generated above. If you can load APIs, you can register SQL SMO, and execute it that way. There may be other ways built into the language to do it. Some time googling "Execuitng SSIS package from COld Fusion" should hopefully help you get a better grasp on this part. Lastly, the process will of course require sufficient privileges to call your new package.

    Once you get through this, if you have more specific answers, post back.

    Good luck.

    Chris.

    nairsanthu1977 (9/29/2008)

    I had posted this topic 2 days back..Unfortunately din't get even a single reply..Is it not possible at all..?

    Please help.....

    Dear All,

    I have made a package using SSBIDS to import data fom CSV file to a table in database.

    Now I want to know 2 important things.

    How can I import a CSV file from remote Server..??

    How can I execute this package from Cold Fusion file..??

    Please help..It's very urgent.

    Thanks in advance.

    Santhosh Nair.

    Chris.

  • September 29, 2008 at 11:25 pm

    #878681

    Hi Chris,

    Thanks a lot of such a detailed reply. I was waiting till evening (indian time) for reply from someone, but in vein. So I again opened my package...Added one FTP control. Copied the file from remote server to local server..and the did the rest as I had already made....

    Disadvantage is that we gotta know FTP username and pwd. In coldfusion there is a tag called <CFHTTP...using which u can directly access files in remote server.

    Am still looking for a better way out....Will let you know if I find anythng better...

    Thanks a lot....

    Santhosh.

  • October 2, 2008 at 5:46 am

    #879887

    how about using the Coldfusion function to copy the file from the remote HTTP Server onto the local server? Then import the file using the normal SSIS package. That way you don't need to worry about FTP.

    Andy

  • October 3, 2008 at 2:32 am

    #880346

    Hi,

    That's how we do the upload now..But it gives problem every now then. Gets timed out...sometimes it takes hell lot of time...

    Otherwise it's very easy...only a few lines codes..nthng else.

    I thought it would be better if we can do it at

    DBase level.

    Thanks..

    Santhu..

  • October 3, 2008 at 2:46 am

    #880350

    Then I suggest you identify the source of the time-out problem. It will affect your direct DB connection in just the same way as the file copy.

    I also think a time-out problem will cause your DB query far more problems than it will to a file-copy routine.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply