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?
I am trying to execute three dts packages from a Job that I created. When creating the steps I am setting the Type To: Operating System Command(CmdExec).
The Command I am using is: dtsrun /S servername /E /dtsPackageName
For some reason whenever I try to run thes jobs it fails. Within this Job I have three steps. For each step I am using the same job but with a different package name. I have a trusted connection so my command should work just fine. What am I doing wrong. Can some please explain this process.
I have about 100 DTS packages and they were created by a user who has left the company. Now I( the new user)am trying to open the packages from another client machine and trying to save them back. I am connecting to SQL server where the packages are stored using NT authentication(domain account). When I try to save the package, I get the following error
Ole DB, Only the owner of the DTS package "packagename", or a member of the sysadmin role can create new versions of it".
What I have tried is that I can save the same package with a new name without being a member of the sysadmin role but I cannot save the package with the same name. ANy help will be greatly appreciated.
I was working on a flat file import spec yesterday, using the import wizard, and I saved my work as a SSIS package. Today, if I connect to Integration Services through SQL Management Studio, I can see the package, but I can't edit it. I've tried to open the package using BIDS, but I can't find it on the server. Obviously it exists, but where?
How the heck can I edit this package? And, where is it? I don't understand why things are so much more difficult in 2005 vs. 2000.
I created a SSIS Package programatically based on the few threads here in this forum. This package just has a data flow task, during data transfer for every 1000 rows or so I want to update the status in a table in the database.
How do I achieve this?
As of now I just have a source and a destination, no transformations in between the flow. I'm not sure if rowcount will help, when I tried it using a onprogress even handler it always showed up as zero.
I've begun to get the above error from my package. The error message refers to two output columns.
Anyone know how this could happen from within the Visual Studio 2005 UI? I've seen the other posts on this subject, and they all seemed to be creating the packages in code.
Is there any way to see all of the columns in the data flow? Or is there any other way to find out which columns it's referring to? Thanks!
We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
Hi All, One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server.. At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
I try to make SSSIS packages made on my pc accesible to an other user of SSIS (both running on Windows 2000). The packages are on a shared drive in our LAN.. Some configurationfiles are use to configure the Oracle DBconnection.The same files are on the same location on both PC's
The other user can open and run the packages but from the moment the lookup buffers are loaded, we get the following errors:
Error: 0xC0202009 at AB_ADDRESS_DF, LOOKUP_POST_ID [22801]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "".
Error: 0xC020824E at AB_ADDRESS_DF, LOOKUP_POST_ID [22801]: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.
Error: 0xC004701A at AB_ADDRESS_DF, DTS.Pipeline: component "LOOKUP_POST_ID" (22801) failed the pre-execute phase and returned error code 0xC020824E.
I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!
I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.
I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.
I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!
I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.
Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!
Could anyone please provide insight as to what my issue may be?
Thanks in advance!
Code Snippet
using System; using System.IO; using System.Data.SqlClient; using System.Collections.Generic; using System.Text; using Microsoft.SqlServer.Dts.Runtime; using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper; using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
// Set some common properties of the connection manager object. //flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false); flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited"); flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, """); flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, " "); flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
// Create the source columns into the connection manager. CreateSourceColumns(); }
private void CreateSourceColumns() { // Get the actual connection manager instance RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
public class Column { private String name; private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType; private int length; private int precision; private int scale; private int codePage = 0;
public String Name { get { return name; } set { name = value; } }
public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType { get { return dataType; } set { dataType = value; } }
public int Length { get { return length; } set { length = value; } }
public int Precision { get { return precision; } set { precision = value; } }
public int Scale { get { return scale; } set { scale = value; } }
public int CodePage { get { return codePage; } set { codePage = value; } } }
String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString(); String b = sourceComponent.OutputCollection[0].Name; String c = sourceComponent.OutputCollection[0].Description; String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();
// Create a path and attach the output of the source to the input of the destination. PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);
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?
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.....................
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
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
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)
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
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"
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()
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.
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')
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.
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!
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.
