|
Hey All,
I find myself here asking a question around SSIS which is somewhat out my comfort zone of C# but I have been tasked with some work, and I have came across a problem which I can't seem to over come.
I have an SSIS Package which I am calling from the command line so its in the file system. Its encrypted with password etc. When I am logged in under my own domain user name the script executes perfectly. However; there is a network service user our support department has set up to run this job. When I run this package under this new user, the pacakge fails.
My package has several tasks, however; the task which appears not to run in the Script task. Again, it runs under my own logon, just no with the service user logon.
As a test, I added the service user to the administrator group on the computer the package is running, and the script task worked perfectly. (I think this might be important).
I'm guessing I have a permissions issue with my new user. My thoughts are that it cannot execute vb scripts which I believe are internal to SSIS?
If anyone has any thoughts, could you please help.
Thanks in advance
TF
|
|
|
|
|
You need a Configuration File to store the credentials to the Server. To make sure if what you are saying 9is what am assuming. can you please send us the error ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
|
Even though the structure might be similar, the order of columns is not the same. When you use *, SQL Server takes the column names in the order they were created, therefore causing a data type mismatch. When you specify the columns, SQL matches the column names exactly. This can even happen with a normal INSERT INTO ... SELECT statement, it has got nothing to do with Linked servers.
|
|
|
|
|
Please don't delete the post once someone has replied to it. It might help others.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Just throwing the question out there incase anyone has tried this. When you're using SQL Server Management Studio, you can right click on most objects and manually generate the create/drop/alter scripts.
Is it possible to generate the SQL scripts (or send the script text to a parameter) from within a stored procedure?
Thanks for any pointers!
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
I'm not aware of anything "built in" that would generate the create/alter/drop script for you, but the syntax of these commands is pretty straight forward.
For example:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FSACTIONSET]') AND type in (N'U'))
DROP TABLE [dbo].[FSACTIONSET]
So you could build your own SPROC to generate the script syntax.
What are you trying to do ? Maybe there is a different way to attack your problem.
david
|
|
|
|
|
Right, that would be from a drop script.
I need to dynamically rebuild a large set of tables. There could be changes to the table on a daily basis, so I was hoping there would be a way to programmatically get at the create scripts and exec it as dynamic sql.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: There could be changes to the table on a daily basis
This tells me your design sucks. If you are changing table structure daily then there must be something disastrously wrong with your data design. I shudder to consider working in such an environment, it sounds like a nightmare.
|
|
|
|
|
I think I found a way to accomplish what I need. Thanks anyways.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
Jon_Boy wrote: I think I found a way to accomplish what I need
Care to share? It might help someone else.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Sure Ashfield (thanks for reminding me to do so).
I found this thread on another site. It got me pretty close to what I'm trying to do, just modifying to my needs.
"There's no such thing as a stupid question, only stupid people." - Mr. Garrison
|
|
|
|
|
i have the Following Query
select sa1 as[sa1],sa2 as [sa2],count(sa1) as [count] into #ttemp from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2
and my final query is this
select c.sa1,c.sa2,c.dur1,c.dur2 from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2
as you can see there are Duplicates in the sa1 and sa2. So i want to delete any of record but one of each. so that means at last there should be 5 Records.
that bring me these Duiplicate
sa1 sa2 Dur1 der2
============================
6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Just 1 small doubt..
For every record, the
der2 field is different.
So how can u say that entire row is a duplicate(though the first 3 are same!)?
Please clarify this.. and what is the output u are expecting please put it clearly
Niladri Biswas
|
|
|
|
|
i catch the Duplicate from two Records. i was able to do it like this
select sa1,sa2,count(sa1) from EXP_REL_CLSH_CONT
group by sa1,sa2
having count(sa1)> 1
order by sa1,sa2
SELECT ACTV as ACTV_ID--, count([VENU]) as , sum([stud])
into #temp
FROM [dbo].SOL_ACTV_VENU
group by ACTV
having count(venu) > 1
order by sum(stud)
and i was deleting my Duplicates like this
SET ROWCOUNT 1
DELETE EXP_REL_CLSH_CONT
from EXP_REL_CLSH_CONT C
INNER JOIN #ttemp t
on c.sa1 = t.sa1
and c.sa2 = t.sa2
SET ROWCOUNT 0
but now in SQL Rowcount = 1 will stop everytime a duplicates is deleted so that it cannot delete the second record. Its Good. But how can i make it to loop and skip one record and delete one in this approach ?
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Try this
declare @t table(sal1 int,sal2 int,dur1 int,dur2 int)
insert into @t
select 6,7,3,2 union all select 6,7,3,3 union all
select 354,867,1,2 union all select 354,867,1,3 union all
select 354,872,1,2 union all select 354,872,1,3 union all
select 356,867,1,2 union all select 356,867,1,3 union all
select 356,872,1,2 union all select 356,872,1,3
select * from @t
I am taking this RecordSet
sal1 sal2 dur1 dur2
6 7 3 2
6 7 3 3
354 867 1 2
354 867 1 3
354 872 1 2
354 872 1 3
356 867 1 2
356 867 1 3
356 872 1 2
356 872 1 3
Since you want any one of the record set so I can use either
sal1 sal2 dur1 dur2
6 7 3 2
or
sal1 sal2 dur1 dur2
6 7 3 3
If this assumption of mine is correct, the here is the answer
select sal1,sal2,dur1,dur2 from(
select row_number() over(partition by sal1,sal2 order by sal1,sal2) rn, sal1,sal2,dur1,dur2 from @t) X
where rn = 1
Output:
sal1 sal2 dur1 dur2
6 7 3 2
354 867 1 2
354 872 1 2
356 867 1 2
356 872 1 2
Here I am considering only the first one for every duplicate entries
What next you can do is put this record set in some temp table , delete the original one and then insert this record back into the table.
Please let me know in case of any concern.
Note - This code will work for Sql server 2005+
Niladri Biswas
modified on Tuesday, November 24, 2009 9:31 AM
|
|
|
|
|
User row_number partitioned over your key fields. This is a sample of a partition I use
ROW_NUMBER() OVER( PARTITION BY ProductID, SubProductID, IssueLabel, Maturity, CurrencyID, Exposure Order by Exposure) as RowNo
You need to include the ID field in the rest of the select and then delete any record where the RowNo > 1.
|
|
|
|
|
Hey everyone,
I'm using Oracle 10.2g with PL/SQL Developer 7.1 from Allround Automation and I want to list my tables one by one showing each detailing its fields.. Any simple way to do this??
Many thanks!
|
|
|
|
|
The only way I've done it in the past was write some PL/SQL code that would loop through tabels like: SELECT *
FROM ALL_TABLES
WHERE OWNER='myOwner
ORDER BY TABLE_NAME;
|
|
|
|
|
CREATE OR REPLACE VIEW schema_tables AS
SELECT o.object_type AS object_type
, c.table_name AS table_name
, c.column_id AS column_id
, c.column_name AS column_name
, DECODE(c.nullable,'N','NOT NULL','') AS nullable
, DECODE(c.data_type
, 'BFILE' ,'BINARY FILE LOB'
, 'BINARY_FLOAT' ,c.data_type
, 'BINARY_DOUBLE',c.data_type
, 'BLOB' ,c.data_type
, 'CLOB' ,c.data_type
, 'CHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'DATE' ,c.data_type
, 'FLOAT' ,c.data_type
, 'LONG RAW' ,c.data_type
, 'NCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'NVARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'NUMBER' ,DECODE(NVL(c.data_precision||c.data_scale,0)
, 0,c.data_type
, DECODE(NVL(c.data_scale,0),0
, c.data_type||'('||c.data_precision||')'
, c.data_type||'('||c.data_precision||','|| c.data_scale||')'))
, 'RAW' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'VARCHAR' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'VARCHAR2' ,DECODE(NVL(c.data_length,0),0,c.data_type
, c.data_type||'('||c.data_length||')')
, 'TIMESTAMP' , c.data_type,c.data_type) AS data_type
, CASE WHEN c.data_default IS NULL THEN 'N' ELSE 'Y' END AS data_default
FROM user_tab_columns c,user_objects o
WHERE o.object_name = c.table_name
ORDER BY c.table_name, c.column_id
/
set feed off markup html on spool on
/
spool 'c:\filename.htm'
/
select * from schema_tables
/
spool off
/
set markup html off spool off
/
|
|
|
|
|
Use:
dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
Example:
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;
Gives you all the tables and indexes for the logged in user.
|
|
|
|
|
Dear All,
I need your help and deal.
I'm using SQL Server 2005.
I want to select a number that nearby a number that i want.
Example:
30 is number i need. when select from table have some number equal it.
So i want a number is so near 30.
(12, 43, 31, 35) = 31
I am sorry if descipt not clear...
Thanks you for your help.
Best Regards,
Sovann
VB.Net
|
|
|
|
|
I dont get enough your question but I think you have to use BETWEEN in your select.
Example:
select * from yourtable where number between 29 and 31
This sttement will give you all records between value 29 and 31
It will be better if you explain more your question.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks you Blue_boy
but i need only one number.
If that number do not have i need number that nearby it.
so how can i do ?
VB.Net
|
|
|
|
|
Well,I'm gonna ask you a question.
Case is: 1,29,30,32 and you want search the nearest number of 30? So, the nearest number is 29 and not 32, right?
What if you have case like this : 1,29,30,31? Which nearest number you need to get?
I think you have to write a function.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
