SSIS: directory import | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SSIS: directory import

Hi all,
I want to do the following with SSIS. I’m pretty sure it is possible, but I’m new to SSIS …
  • On Server A I have a directory (d:/images/) which contains .jepg pictures (image001.jepg, image002.jepg, image003.jepg, …)
  • On Server B I have a SQL2008 and a table called images
I need now a SSIS object, which fills all the filenames of the directory from Server A into the table of Server B. So that every file on A is a new row on B.
Doesn’t sound to complicated, right?
Thx for any help.
Reto E.

Me again,
I guess I could use the DOS-Command @dir g:images /A:-D /B /-C > g:DirList.txt to create a flatfile with the filenames. And then import the .txt file to the table.
But how can I from within the SSIS "connect" to the Server A with a different Windows-Login and Password to run the DOS-Command and access the .txt-File?
Thx for any hints
Reto E.

As you are using SQL 2008 why don’t make use of FILESTREAM feature in this case, http://technet.microsoft.com/en-us/library/bb933993.aspx to manage the cause.

Thx for the tip with the filestream. Sounds interesting to me.
But I already managed to create a SSIS package with a foreach loop container. I additionnaly had to make a proxyuser to run the SSIS package in the SQLServer to access the second server. Now it works like a charm.
Reto E.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |