February 10, 2012 at 12:58 pm
I am fairly new to SSIS and am just stumped by this, here is what I have. I have a table named X. It has 4 fields in it: RecId, ID, name, body.
I have to iterate through this table and for each 'name' (which is a filename), take what is in the field 'body' and put it into a html file in a specific location. example of what is in the table:
RecId, ID, Name, Body
1, ID1, 1234.htm, 'this is text'
2, ID2, 2345.htm, 'this is also text'
3, ID3, abcd.htm, 'some more text'
4, ID4, hello.htm, 'a little more text'
what I need is based off of a where clause that is selecting 200 different RecId's. (ie select recid, id, name, body from X where recid in ('1','2','3','4'). So I will have 200 different html files each with the 'name' as the file name and the corresponding 'body' inside each file.
I have been trying using execute sql task and a foreach loop but I am just not getting this to work and I am really at a loss of what to do. I keep googling what I think I'm looking for, coming up with some ideas and trying them only to not have them work. I am fairly new to SSIS and can typically move data between databases and create one file off of tables, but this has me stumped. Any help I can get is greatly appreciated.
Thank you for your help
Michelle
February 10, 2012 at 1:41 pm
Not too sure what your asking but as i read it, you want a script to look through your results and create a file called what ever your name colummn is and the content of the file is the file body? is that correct?
***The first step is always the hardest *******
February 10, 2012 at 1:43 pm
that is correct. I am trying to do it through SSIS.
February 10, 2012 at 1:54 pm
I used the following method in the past to create a batch file, with the content of a SQL query, you may be able to use this method to complete your task.
my steps where to populate a table with some text, the body of the file, then export that information into a batch file
exec master.dbo.xp_cmdshell 'SQLCMD -E -S servername -h-1 -Q " set nocount on select * from tablename" -o"C:\DB\batch_test.bat"'
for your task you might want to split this command up, use dynamic SQL to build up your command and then execute let me know if you need some help with that
***The first step is always the hardest *******
February 13, 2012 at 1:24 am
Not sure if this will work or if its even viable.
Add a variable object. Populate it with a distinct on your 'body' field. Use a loop through to create each file using a value from the variable list and the relevant data from your query, using the current variable value to name it.
Will that work?
Ian Cockcroft
MCITP BI Specialist
February 13, 2012 at 2:34 am
Not Ideal, but this will work.
DECLARE @Filename VARCHAR(100)
DECLARE @Body VARCHAR (1000)
DECLARE @Cmd VARCHAR(1000)
DECLARE @Folder VARCHAR(100)
DECLARE @FileInfo TABLE
(
RecID INT IDENTITY(1,1),
ID CHAR(4),
Name VARCHAR(150),
Body VARCHAR(150)
)
SET @Folder ='C:\'
INSERT INTO @FileINfo
SELECT 'ID1', '1234.htm', 'this is text'
UNION
SELECT 'ID2', '2345.htm', 'this is also text'
UNION
SELECT 'ID3', 'abcd.htm', 'some more text'
DECLARE fileCSR CURSOR FOR
SELECT
name,
body
FROM @FileInfo
OPEN fileCSR
FETCH NEXT FROM fileCSR INTO @FileName,@Body
WHILE @@FETCH_STATUS =0
BEGIN
SET @Cmd ='ECHO ' +@Body + ' > ' +@Folder +@Filename
EXEC xp_CmdShell @Cmd
FETCH NEXT FROM fileCSR INTO @FileName,@Body
END
CLOSE fileCSR
DEALLOCATE FileCSR
February 13, 2012 at 12:20 pm
thanks for all of your comments and help. I actually was able to get the solution from StevefromOz in this thread:
http://qa.sqlservercentral.com/Forums/Topic530435-147-1.aspx
It took me several tries, but I did finally get it to work the way I was expecting after a few modifications based off his post. Yay!!
Thanks!!
Michelle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply