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 |
|
radmoose
Starting Member
2 Posts |
 Posted - 2005-02-28 : 16:47:20
|
| I have a table with a list of files that need to be imported.If I setup a manual DTS with a hardcoded filename, the files import properly.How can I tell the DTS to use the list of filenames in the table to use for import?Any suggestions on what to read where? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-02-28 : 16:56:57
|
| I wouldn't even bother with DTS for this. Create a loop in T-SQL that gets the file names one at a time. For each file name, run bcp in using xp_cmdshell.DECLARE @cmd varchar(4000)SET @cmd = 'bcp DB1.dbo.' + @TableName + ' in C:\' + @TableName + '.txt -c -Sserver1 -T -t, -r\r\n'EXEC master.dbo.xp_cmdshell @cmdTara |
 |
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-02-28 : 17:02:48
|
| This is somewhat related, because it involves implementing looping in DTS:http://www.sqldts.com/default.aspx?246A non-DTS alternative may be to cursor over the rows and build dynamic SQL for a BULK INSERT command for each table/file. |
|
|
|
radmoose
Starting Member
2 Posts |
Posted - 2005-03-01 : 16:31:01
|
| Please accept apology for not explaining why DTS.The data is in a modified CSV format.BULK INSERT has problems with it as there are extra commas, and other text that needs to be removed.The items come in via SQL mail as attachments. I am successful at extracting the attachments and building a table that has the full path for each items.I can get DTS to import the files one at a time, but I need to automate this process. I want to pass the list of attachments to the DTS process via the temp table. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-01 : 16:33:17
|
| So then the link that PW posted will help you out with this.Tara |
|
|
|
|
|
|
DTS get parameter from table