What To Do With Pkgs Once Created In BIS?
Mar 6, 2007
Hi,
I have been in the process of creating some new pkgs in BIS. My question is, once I am done writing the pkgs, what next?
That is, how do I get the pkgs into SQL Server, and how do I schedule them to run?
Also, what if I have the following scenario:
I have created a new SSIS pkg that has several EXECUTE DTS 2000 tasks in them. The DTS packages that are called from the EXECUTE DTS 2000 task are stored in the database under "Legacy". These should still run OK once I "load" the packages to SQL Server 2005, yes?
Where do the SSIS packages "live" in SQL Server 2005? That is, where can I view them.
Thank you!!
View 1 Replies
ADVERTISEMENT
Jan 23, 2008
Is there any limitation on how many tables I can export using DTS PKGS.? I have almost 110 tables, I need to export to another SQL server-- I want to use dts pkg-- is there limitation?
Thk
Josephine
View 1 Replies
View Related
Mar 14, 2007
Hi,
I have some questions.
Regarding SQL Server deployment, it says to use this option if sharing packages between servers. Does this mean sharing packages between development, test, and production environments? Or just sharing among production servers?
That is, I am trying to determine if I should deploy my packages on SQL Server, or just store them on the local file system. These packages will NOT be used on any other production server. So should I just keep them on the file system?
Another question. I see that I have a package stored in the "sysdtspackages90" table in the msdb database. Since I have not done any SQL Server deployment yet, I don't understand how this particular package ended up in this table. Is it OK to remove? I think this is a previous version of a package that I have since re-written. Are there any dependencies on this "sysdtspackages90" table that I should be aware of?
Thanks!
View 6 Replies
View Related
May 30, 2008
I have a server that is sql2000. We are planning to upgrade that to SQL SERVER 2005.
We also have a test 2005 server.
Before going live, we are migrating all 100 DTS pkgs that we have
in sql server 200 over to SSSS on the TEST 2005 sql Server. once all THE 100 DTSPKGS THAT we have in SQL
2000 PKGS Are scucessfuly migrated/tested on test 2005 server, only then we will
move forward with migrating the PRODuction 2000 Server to 2005.
My question is:
1. When we are ready to migrate PRODuction 2000 SERVER, TO 2005, We will need to transfer/copy all
the SSIS 2005 PKGS THAT WE HAD tested on the test 2005 server. Once we move that to prod 2005,
we will need to change datasource connection to point to the PRODuction 2005 SERVER.
However my question is WHAT IS THE EASIEST/FASTEST WAY TO tranfer/copy all these (100+ ssis pkgs)
2005 ssis pkgs that are on the TEST SERVER
OVER TO PROD 2005 SERVER? Do we transfer each and every SSIS PKg manually ,or is there any easy way to get
these 100 SSIS Pkgs, over from TEST 2005 TO PROD 2005?
View 10 Replies
View Related
Oct 24, 2006
Hi Every one,
i give a small fuzzle for you here , ie . i have a job which includes 6 packages in that one and while running that job any one package is fialed how can i execute that next package even that before package was failed. i am using for loop container for running my 6 packages ... is there any method to do this like on error resume next in .net
sreenivas
View 1 Replies
View Related
Jun 29, 2015
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
View 3 Replies
View Related
Nov 30, 2007
I currently have SQL SERVER 2000 Running. Once I upgrade that 2000 server to 2005, how do I migrate the dtspkgs that were on 2000 TO 2005?
View 5 Replies
View Related
Dec 28, 1999
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
View 1 Replies
View Related
Mar 22, 2001
I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer
Dim oJobServer As New SQLDMO.JobServer
Dim oJob As New SQLDMO.Job
Dim oJobSch As New SQLDMO.JobSchedule
Dim oJobStep As SQLDMO.JobStep
oSQLServer.Connect "mc", "sa", ""
oJob.Name = "JOB_Backup"
oSQLServer.JobServer.Jobs.Add oJob
Set oJobStep = New SQLDMO.JobStep
With oJobStep
.Name = "Step 1"
.StepID = 1
.DatabaseName = "master"
.SubSystem = "TSQL"
.Command = "BACKUP DATABASE [medco] TO DISK = 'E:Projectmedco.BAK' With INIT"
.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
End With
oJob.JobSteps.Add oJobStep
oJob.StartStepID = 1
'Set the schedule.
With oJobSch
.Name = "Schedule 1"
.Schedule.FrequencyType = SQLDMOFreq_Daily
.Schedule.FrequencyInterval = 1
.Schedule.ActiveStartDate = "20010315"
.Schedule.ActiveStartTimeOfDay = "150800"
.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
End With
oJob.JobSchedules.Add oJobSch
oSQLServer.DisConnect
Set oJobStep = Nothing
Set oJobSch = Nothing
Set oJob = Nothing
Set oSQLServer = Nothing
Something is missing but ?
Thanks for all your helps in advance,
Ben
View 3 Replies
View Related
Jan 6, 2004
I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT:
"insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
View 5 Replies
View Related
Jan 18, 2002
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf.
I tried sp_attach_db specifing only mdf file but geting errors
-------
microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223)
..... You cannot attach a database that has been created with multiple log files without also attaching all the log files.
----
how can I remove those big ldf files and create new ldf (defult(1mgb)
View 1 Replies
View Related
May 7, 2008
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100)
declare @SQL VarChar (2000)
Declare spot cursor scroll for
select GM from BIM_Historical_Performance.dbo.Performance_Master
open spot
fetch first from spot
into @@GM
While @@Fetch_Status =0
BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
EXEC (@Sql)
Fetch NExt from spot
End
View 1 Replies
View Related
Oct 31, 2005
I want to know who had created the new tables,How can I achieve it?
Can I know it by using the sysobjects table?
View 7 Replies
View Related
Dec 20, 2005
How Can I know who had created the table in my DB?
May I log it?
View 3 Replies
View Related
Jul 1, 2006
Hi,
How can i get the names of user created databases?
select Distinct (name) from sysobjects where xtype = 'U', i know this statement for tables but is there a statement for Databases?
thanks
View 5 Replies
View Related
Feb 20, 2008
Hi everyone.. I have a big problem.. :p
I have created a installer for my application and database.
When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
View 10 Replies
View Related
Aug 15, 2007
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet
ALTER procedure spCopyTemplateElementTypesToIssues
@TemplateRecno integer,
@ProjRecNo integer,
@IssueRecNo integer
as
declare @ElementTypeRecno integer
declare @ElementTypeDepRecno integer
declare @ProjTypeRecno integer
declare @PreElementRecNo integer
declare @PostElementRecno integer
declare @Count integer
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @ElementTypeRecno
--delete from tblElementCPO
WHILE @@FETCH_STATUS = 0
BEGIN
select @count = count (*)
from tblElementCPO
where ProjRecno = @ProjRecNo
and IssueRecno = @IssueRecno
and TemplateRecno = @TemplateRecno
and ElementTypeRecno = @ElementTypeRecNo
if @Count = 0
begin
insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,
ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,
TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)
values (0, @ElementTypeRecno, @IssueRecno,
@ProjRecno, 5,0,6,99,
99,@TemplateRecno, getdate(), current_user)
end
FETCH NEXT FROM element_Cursor into @ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
select @Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
if @Count > 0 then
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
WHILE @@FETCH_STATUS = 0
BEGIN
select @Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @ElementTypeDepRecno
and PreElementRecNo = @PreElementRecNo
and PostElementRecno = @PostElementRecno
if @Count = 0
begin
insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,
PostElementRecno, ChangeDate, ChangePerson)
values (@ElementTypeDepRecno, @PreElementRecNo,
@PostElementRecno, getdate(), current_user)
end
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
called by
Code Snippet
Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "spCopyTemplateElementTypesToIssues"
cmd.CommandType = CommandType.StoredProcedure
Dim spmTemplateRecNo As SqlParameter = _
cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int)
spmTemplateRecNo.Value = _
Me.cbTemplate.SelectedValue
Dim spmProjRecNo As SqlParameter = _
cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _
Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _
cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _
Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414
cnQI02414.Open()
reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.
cnQI02414.Close()
What should I be looking for?
View 1 Replies
View Related
Nov 5, 2007
Hi all,
I have been trying to extract table data into file but its not getting extracted. I am using following command to run.
exec spGetIPAddress @ip = @servername output
select @SQL = 'bcp '
+ char(34)
+ ' select Field from ['
+ @DatabaseName
+ '].[dbo].Temp_ExtractSR39'
+ ' order by Field'
+ char(34)
+ ' queryout '
+ @RenamePathAndFile
+ ' -T -c -CACP -S'
+ @servername
exec master..xp_cmdshell @SQL, no_output
Is there any setting need to be done at database level? or any setting for Sql server?
Thanks in adavance,
Anshu
View 3 Replies
View Related
Apr 10, 2006
Hi there.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
DROP DATABASE [db_name_something]
CREATE DATABASE [db_name_something] ON (NAME = N'db_name_something_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'db_name_something_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
..
..
any ideas where I am going wrong?
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
Many thanks
View 3 Replies
View Related
Dec 5, 2007
--The Dependency row is not created!
-- I have a procedure "Example 1".
-- When I look at the sys.sql_dependencies I didn't find a row with Table1 dependency.
--If I write the procedure like "Example 2" the dependency Table1 exists in sys.sql_dependencies.
--
--DEFENENTLY A BUG. CAN YOU FIX IT?
--I REALLY NEED IT!!!!!!
Create table Table1 (id int);
GO
--Example 1
CREATE procedure a
as
create table #Test(id int)
Insert into #test
Select id from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('a');
GO
--Example 2
CREATE procedure c
as
Select id into #test from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('c');
DROP PROCEDURE c
DROP PROCEDURE a
DROP TABLE Table1
View 1 Replies
View Related
Jul 19, 2007
I loaded the import/export wizard into my Tools menu. I ran it and exported a view to an excel worksheet with mixed results (I got the headings, but no data). I want to take a look at the package that should have been created (I selected that option, but the export didn't complete and I couldn't decipher the log to determine whether the package was created).
If it was successfully created, where would I find it in SQL 2005 Express?
Thanks,
Bill
View 12 Replies
View Related
Jul 1, 2007
in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
View 2 Replies
View Related
Aug 14, 2000
Hi Guys,
Is there a way to notify DBA when a new stored procedure,table,view is created by any user...
Help is greatly appericated..
Jessi..
View 1 Replies
View Related
Nov 24, 2000
Can Query Analyzer deal with this or not???
Select FirstName into FirstNameTable From NameTable
Select Count(*) from FirstNameTable
Fails with FirstNameTable as invalid object.
TIA
View 5 Replies
View Related
Apr 9, 2003
Does anyone knows what this account does? I heard that it is used by Visual Studio and Query Analyser. However, I couldn't find much documentation about it. This account has a random password which is generated by the Server. The password cannot be changed and will never expire.
Thanks...byyu
View 4 Replies
View Related
Jul 8, 1998
I am installing SQL Server on a large number of servers. MSDB was not created on 7 of the
36 servers. MSDB was created just fine if I uninstalled SQL Server and reinstalled it and also
when I created MSDB using the instlmsdb.sql script out of the MSSQLInstall directory.
There is no mention of this problem on TechNet but there is an article on it on
www.support.microsoft. However, the article says that the bug happens if SQL Server is
started in single user mode. I am not starting any of these servers in single user mode.
Someone else has tracked this problem to BIOS. What do you know?
I am installing 6.5 on Win NT 4.0 SP3.
Thanks for any help.
Virginia
View 1 Replies
View Related
Jun 11, 2006
I am getting a return code of 12 (""File not created"") from sp_trace_create. I have checked to ensure that the path is valid. I am using Windows Authentication and the Windows Administrator account. SQL Server is using the Windows userid that I created for SQL Server. I have verified (at least tried to verify) that the user for SQL Server has access to the folder containing it. The following is the relevant portion of what I am attempting. DECLARE @RC int, @TraceId intExec @RC = sp_trace_create @TraceId OUTPUT, 0, N'C:TraceFile' I have tried other paths that also do not work.
View 1 Replies
View Related
Nov 7, 2006
Is there a command I can use to find out WHO created a specific table?
~Le
View 4 Replies
View Related
Mar 18, 2008
Hi Folks,
I am trying to make it that a user can just click on my form and generate a custom sql query.
for example:
If I had three list boxes,I would like my user to be able to click on any of the items in the three boxes and come up with a datagrid displaying the query results.(in effect creating an individual query
i.e
ListBox1
robert redford
dustin hoffman
steve mc queen
ListBox2
butch Cassidy and the Sundance Kid
the Sting
straw dogs
pappilon
List 3
1965
1966(through to)
2008)
So then I want my user to come along click on Dustin Hoffman then click on a film(the sting perhaps) then the year,to find out if Dustin was actually in that film in that particular year...and if he was produce the details.
This will create a "custom" sql statement like
select * from lst 1 ...ect.
What I would like to know is can this actually be done.
This is only a small example of the datbase.I have thousands of films and name ect so it can get quite big.
any help fully appreciated
Gracie
View 4 Replies
View Related
Sep 20, 2012
I get the following error message from Server-Agent-Jobs:
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server "xxx". [SQLSTATE 42000] (Error 7302).
The linked server already exists a long time and has always worked. I can still see all the tables and can query the data via select. The stored procedure that is called in the job, can I call manually.
View 2 Replies
View Related
Apr 3, 2008
Hi Guys,
Im new using SSIS and i just created a data flow that reads from multiple flat files (with a mutipleflatfiles conn manager) and inserts the files data to an sql server (with an ole db destination)
The thing is i need to insert the created date property of each file im importing and i dont know how to do this.
I've read i could do this with a script but im not very familiar to it.
Could someone help me there?
View 2 Replies
View Related
Jun 11, 2008
Hi I am facing an issue running an SSIS package on a SQL Server 2005 SP2 machine
Here's more information:
1) The SSIS package that dumps data returned from a stored procedure call to an excel (CSV) file. The package owner is a SQL Login called AppAdmin. The login has user memberships/roles in two databases:
1.1) Database AppDB - Has execute and select permissions.
1.2) Database msdb - SQLAgentUser role
The package has one step that is run as a SSIS Proxy account PrxApp. The Proxy is based on a credential CrdApp which is based on a windows account <domain>AppAccount. This account is part of the Local Administrators group and also has SA rights in on the server. The same account is also used to run all the SQL Server Services on this machine. The machine is part of a 2-node cluster (A/P).
When I try to execute this package (as a schedule or one-off), I am getting the error mentioned in the Subject. I have checked a few posts using google and have acted upon as many suggestions that I possibly could (e.g. change this, change that...did not do any registry modification, etc). Many of these posts have been talking about changing local security policy and adding the user to several groups. Did that, but still does not work in a SQL Agent Job.
The account currently has the following rights (local security policy)
Act as part of OS
Perf. volume maint tasks
Lock pages in memory
Replace process level token
One thing that I did notice and don't know if that affects the issue is that the following groups (and SQL Logins) are missing from my Windows groups listing (Computer ManagementUsers and GroupsGroups). Note that this is a Windows 2003 machine.
(a) SQLServer2005MSSQLUser$<machine_name>
(b) SQLServer2005SQLAgentUser$<machine_name>
Now these groups (and logins in SQL Server) do exist on some other SQL2005 machines that I have checked to see what could be wrong.
Any help at all is greatly appreciated. If you need any more information please let me know.
Thanks
Shishir Khandekar
View 6 Replies
View Related
