Foreach Loop read table data and write to file

Hi,

I want to do the following with a ssis package:

INPUT:

A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:

I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks

Danny

(Further you can use Import Column transform; in example from here this transform was called File Inserter (in beta release).) - I thought you need insert a file. To export a file you need Export Column transform

|||

The Sample you mention is not exactly what i need. That sample loops through a list of files and writes the names of the files back to a table. Then it has a standard Data Flow Task reading the table with the filenames inserted before and do something with it.

What i need is loops through a table, and for each row i need 2 values from the table to work with in the Data Flow Task. One of the values is the filename to be written and the other value is the content to be written in the file.

|||

You can do in following way :

1. Let's say you want to put the files in c:\YourFolder, add a data flow task and connection to your table

2. Add a derived column transformation; make a derived column name NewFilePath and in expressions :

"C:\\YourFolder\\+(DT_WSTR,50)ColumnA"

3. Add an Export Column transformation; in Export Column transformation editor set

Extract Column= ColumnB

File Path Column=NewFilePath

so SSIS will get the file from columnB and put in the folder using NewFilePath

forcing string value in table

Hi just wondering if there is a way to have a script that creates a table
write in a desired string for the first table entry?
I have an application that I am allowing the de-activation of a record. I
could not allow a delete since this could result in an orphan record. So
basically the table at startup (when first created) would look like
****************************
* pri key * varchar (20)*
****************************
* 1 * Not Active *
****************************
the application would add records to the table as it is used.
--
Paul G
Software engineer.Paul, I'm not clear on what you need.
if not exists (select 'x' from information_schema.tables where table_name ='mytable' and table_type = 'base table')
create table mytable (id int identity(1,1), status varchar(20))
if not exists (select 'x' from mytable)
insert into mytable (status) select 'Not Active'
Is this close?
Also, do you not have foreign key constraints defined in your data model?
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> Hi just wondering if there is a way to have a script that creates a table
> write in a desired string for the first table entry?
> I have an application that I am allowing the de-activation of a record. I
> could not allow a delete since this could result in an orphan record. So
> basically the table at startup (when first created) would look like
> ****************************
> * pri key * varchar (20)*
> ****************************
> * 1 * Not Active *
> ****************************
> the application would add records to the table as it is used.
>
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Its primary key is a foriegn key in another
table, this is why I can not delete a record. I also have a few constraints,
below is from Query analyzer script object. Just would like to put in for
the first record the string "Not Active" so perhaps what you listed should
work.
CREATE TABLE [DML$POC_T] (
[POC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[POC_Name_VC] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_DML$POC_T] PRIMARY KEY CLUSTERED
(
[POC_ID]
) ON [PRIMARY] ,
CONSTRAINT [IX_DML$POC_T] UNIQUE NONCLUSTERED
(
[POC_Name_VC]
) ON [PRIMARY]
) ON [PRIMARY]
GO
"Armando Prato" wrote:
> Paul, I'm not clear on what you need.
> if not exists (select 'x' from information_schema.tables where table_name => 'mytable' and table_type = 'base table')
> create table mytable (id int identity(1,1), status varchar(20))
> if not exists (select 'x' from mytable)
> insert into mytable (status) select 'Not Active'
> Is this close?
> Also, do you not have foreign key constraints defined in your data model?
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4F1222EF-14CF-49C4-B5E7-6F40E3CEE138@.microsoft.com...
> > Hi just wondering if there is a way to have a script that creates a table
> > write in a desired string for the first table entry?
> > I have an application that I am allowing the de-activation of a record. I
> > could not allow a delete since this could result in an orphan record. So
> > basically the table at startup (when first created) would look like
> > ****************************
> > * pri key * varchar (20)*
> > ****************************
> > * 1 * Not Active *
> > ****************************
> > the application would add records to the table as it is used.
> >
> >
> > --
> > Paul G
> > Software engineer.
>
>

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

Forcefully Disconnect All Users and Drop a Database

Hey all,

I am trying to write a function to drop a specific database no matter the connection status. I have tried

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

srv.KillDatabase("Name");

,and also tried

Database db= Database (srv, "Name");

Database db=srv.Databases["Name"];

db.Drop();

None of these worked. That surprises me because KillDatabase is supposed to disconnect all activity to the database, at least that's what it says it does.

Thanks.

Hi,

that was worth a Blog entry, there you are :-)

http://www.sqlserver2005.de/SQLServer2005/Default.aspx?tabid=56&EntryID=9

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks!|||Awesome! I was just logging in the forum to ask that very same question. Thanks a lot! :)|||You all most had it all you need is to add the following line and it will work.,

Database db= Database (srv, "Name");

con = new ServerConnection(sql);

con.Connect();

Server srv = new Server(con);

Database db=srv.Databases["Name"];

//Add this line
db.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted;

db.Drop();

|||Update to all, the SMO classes were changed to the following:

collection1.Add(string.Format(SmoApplication.DefaultCulture, "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", new object[] { SqlSmoObject.MakeSqlBraket(database) }));
base.ExecutionManager.ExecuteNonQuery(collection1);
this.Databases[database].Drop();

which is pretty close to my suggestion :-) :

s.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
s.Databases[databaseName].Drop();

-Jens.

sql

FOR XML streamed?

We are trying to write an XSLT report generator that is pipelined throughout all stages. The first step in the process is to get XML out of our database. We are using 2005 April CTP. The white paper by Michael Rys in the MSDN library seems to indicate that the FOR XML streaming behavior of SQL 2000 is still present in 2005. However, it seems to hint that if you use the TYPE directive to return your XML in an actual native XML field, then the XML is no longer streamed. The entire document may first be created and then sent back all at once as a scaler XML field.

Here are the 2 FOR XML clauses we are expirimenting with:

"For XML Path('PurchaseOrder'), Type, Root('PurchaseOrderReport')" - sends back an XML field instance.

"For XML Path('PurchaseOrder'), Root('PurchaseOrderReport')" - sends back an nvarchar(max) instance.

Can anyone answer if using the TYPE directive eliminates the streaming behavior and therefore defeats our attempt to pipeline?

Thanks!

The TYPE directive will create an XML datatype instance and thus will "Blob" on the server before sending the result back to give you the guarantee of a well-formed XML datatype instance.

So: yes, it eliminates the streaming. You can still pipeline after that on the client side though for the rest of your pipeline.

Best regards
Michael|||Thanks for the reply Michael!

Aside from the guarentee of well formed XML, are there other advantages/disadvantages to using the TYPE directive that we should concider when trying to decide between the typed or non-typed route?|||The TYPE directive constructs an XML data type instance, so you can use it for

(a) nested FOR XML
(b) assignment to XML columns, variables and parameters
(c) querying the XML data type result using XQuery

at the server. If your intent is to get the XML to the client in a streamable manner, and (a)-(c) don't apply, then you are better off without the TYPE directive. This also saves you the cost of data conversion to XML data type.

Hope this helps.

Thank you,

Shankar
Program Manager, Microsoft SQL Server

FOR XML in web page- OK what's next

(1) I need to select records from a SQL database and create a XML document which I then need to write to a users directory. I am using SQL express with VWD 2005. I located the FOR XML and can execute in VWD's SQL graphical tool. I haven't tried in a Web Form yet but I assume it will work ok. But then how do I write the xml results from the SQL query to local user's directory?

(2) I can execute the FOR XML in the SQL graphical tool but how do i Connect to the DB in the Web Form? Use data.sqlClient.Connection and use .SQLcommand to perform the SQL query? If so, then what?

Following is an example I found that shows use of a NameSpace

WITH XMLNAMESPACES (DEFAULT 'urn:example.com/doc'

, 'urn:example.com/customer' as "c"

, 'urn:example.com/order' as"o"

)

SELECT CustomerID as "@.ID",

(SELECT OrderID as "@.OrderID"

from Orders

where Customers.CustomerID=Orders.CustomerID

FOR XML PATH('o:Order'), TYPE

) as "c:Orders",

CompanyName as "c:CompanyName",

ContactTitle as "c:ContactName/@.ContactTitle",

ContactName as "c:ContactName/text()",

PostalCode as "c:Address/@.ZIP",

Address as "c:Address/c:Street",

City as "c:Address/c:City"

FROM Customers

FOR XML PATH('c:Customer'), ROOT('doc')

My research turns up nothing on the above. Is FOR SQL the best way to go? I see that the SQLXML is not available in SQL server express 2005.

Thanks for any help.

Pauley

Once you have the data in a dataset you can write the XML to a file(froma datatable as well if you want) Link is here;

http://msdn2.microsoft.com/en-us/library/zx8h06sz.aspx

To do this you do not need to use for xml or even the XML-DT on the server.

For Xml Explicit

I would like how i have to write my stored procedure ... so that i can make a treeview with different nodes ...
I use the for xml explicit ... but i dont know how a can go ahad with this thing
can you help me please ?
thx a lot
cyrexWhat is the structure of your data in sql server ? Will it be displayed as a treeview - if so what language are you using ?

For loops in Tsql

Can somebody please tell me how can i write a tsql statement in sql server 2000.

Same time how can i get the last digit of a inteager variable through tsql .What i want is to write 'right(intVariable,4) which is in vb .I want that in sql server 2000

Thank you

The best way to write TSql is to use Query Analyzer.

To get the last digit of an integer variable try: Right( Convert(varchar(10), intVariable ), 1 ) That will turn the int into a string, then get the last digit.

Since your subject has For Loop, but you haven't asked about For Loops, I'll just throw it in. TSql has While() instead of For. Declare a variable for your counter, use the While, and be sure to increment the counter.

Declare @.counter int

Set @.counter = 1

While @.counter < 10

Begin

Do something significant

Set @.counter = @.counter + 1

End

|||

Hi rohanfernando, I moved your question over to the Transact-SQL forum. The folks over here should be able to help you with your question.

Cheers,

JJustice [MSFT]

|||The easiest way to get the last digit of an integer is to get the 'mod 10' of it. Like this:

select 234 % 10 --gives 4
select 12098523 % 10 --gives 3

Rob

For Each

Is it possible to do a foreach loop in T-SQL? I need to populate a database w/ dummy data for testing and need to be able to write a SP that will cycle through each record in one table and populate other table(s) based on what is in that record. Any ideas?there is no for each loop but you can certainly get the data into atemporary table or table variable and loop through each record. You canuse the primary key column to go to the next row.
|||You are thinking procedurally. This is not how SQL works. You need to think in sets.
What you are trying to do can likely be solved with a single UPDATE statement with a few CASEs.