Easy Stuff

hi.. guys this is simple for you guys how can i make ms access to display the current date in a col... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??I bet that you'll get a lot better response if you post this question in the MS-Access (http://www.dbforums.com/f84) forum.

-PatP|||thnx Pat...|||That didn't hurt much, now did it ?!?!

Anywho, while I'm sure that somebody here could have answered your question, why bother to ask here when there are oodles of folks that are readily available that can answer you? Better still, they can offer lots of insight because they actually USE MS-Access as their tool of choice.

-PatP

Dynamics in SSIS?

I have a Table say

Table1

Col 1 Col2 Col 3

A X1 1

A X2 2

B Y1 3

C Z1 4

C Z2 5

( Col1 represents Entity names, Col2 represents there respective field name and Col3 represents the values of those field name)

How can i use the above Table1 and update table A, B, C

Ex:

Table A

X1 X2

1 2

Similarly Table B

Y1

3

and Table C

Z1 Z2

4 5

2 Question:

I was trying to use for each loop container. I am getting this error:

Error: Variable "User::ADOVar" does not contain a valid data object

I followed all the steps in the links

http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx

and check out other sources but unable to track where the problem is?

Can some body help me out please

Question 1:

This should work...

update TableA
set
x1=(select col3 from Table1 where col1='A' and col2='x1')
x2=(select col3 from Table1 where col1='A' and col2='x2')

Although I'm not sure why you would want to do something like this. Do you only plan on having one record each in TableA,B,C?

Question 2:

What exactly are you trying to do? Are you trying to add Table1 to a result set and then loop through each record and execute a dynamic update statement?

|||

Anthony

Question 1:

can I do this in SSIS using task?

Question 2:

Yes I was trying to loop thru the results set from Execute Task and process each row.

My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.

As in my previos example:

I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3

|||

AWM_dB wrote:

Question 1:

can I do this in SSIS using task?

Take a look at the Pivot / Unpivot components in the data flow.

AWM_dB wrote:

Question 2:

Yes I was trying to loop thru the results set from Execute Task and process each row.

My Table1 -> Col1 represents Entity name of the table; Col2 represents the Colum of that particular Entity.

As in my previos example:

I need to process the table1 row by row. It shud Check the Col1 for Entity Name ( Table i need to update) and Col2 for the Colum ( which gives me what Colum in the ENtity name i have to update ) and change the value with Col3

The error makes it sound like the resultset isn't being set into the variable. Can you confirm that the variable is populated from the first Execute SQL Task by adding a Script Task between the Execute SQL and the For Each, and checking that the variable is not null? Also, verify that the variable is defined at package scope and not on each task.

DYNAMIC TSQL

Here is the sample query:

DECLARE @.TABLENAME NVARCHAR(50);

DECLARE @.COL NVARCHAR(50);

DECLARE @.VALUE NVARCHAR(50);

/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/

DECLARE @.SQL1 NVARCHAR(1000);

SET SQL1 = 'SELECT * FROM' + @.TABLENAME + ' WHERE' + @. COL + '=" + @.VALUE

EXECUTE sp_executesql @.SQL1

Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @.VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @. VALUE

is there any way I can do this; give quotes to @.value like @. COL + '=" + ' @.VALUE'

Use the following query,

Code Snippet

DECLARE@.TABLENAME NVARCHAR(50);

DECLARE@.COL NVARCHAR(50);

DECLARE@.VALUE NVARCHAR(50);

DECLARE@.SQL1 NVARCHAR(1000);

DECLARE@.PARAM NVARCHAR(1000);

SET @.SQL1= N'SELECT * FROM ' + @.TABLENAME + N' WHERE ' + @.COL + N'=@.VALUE';

SET @.PARAM = N'@.Value as Nvarchar(50)';

EXECUTEsp_executesql @.SQL1, @.PARAM, @.VALUE