Changing SSIS Package - New User Question
Feb 23, 2006
I saved a simple export/import package from the import/export wizard to a .dtsx file/package. Now I want to change the package to delete/truncate all of the tables before adding data. (since this has 80 tables it would have been a pain to edit each of them individually)
How can I easily go about doing this with the SSIS package. I can't find out where this would actually be executed.
Also, how can I actually view the code behind the SSIS package.
Thanks.
View 3 Replies
ADVERTISEMENT
Feb 7, 2008
I'm new to SSIS so I have not found the answer yet. I have a SSIS package that needs to be run everyday,that loads a basic text file. The only problem is that the text file may not be in the same location everyday so my users will need to locate the path prior to running the package. Is there a way to browse to the text file then have my users execute the package sort of like the old DTS in SQL 2000.
Shanon
View 4 Replies
View Related
Feb 22, 2008
I wrote a simple SSIS package that runs a batch and a stored proc. They both run fine. I can see the stored procedure run and in process under task manager I see the batch run. However I don't want the batch to run under the System account, how can I change to another user. Thank you.
View 4 Replies
View Related
Feb 5, 2007
Hi!
I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.
Finally I need to load data to a database on a different machine using this package.
I used several scenaries:
1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.
2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).
3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.
In all cases I recieved the same error execution message :
"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."
As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.
As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.
Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.
View 9 Replies
View Related
Feb 23, 2007
Hi,
I have a small problem. I've gone through the SSIS wizard and created a dtsx file which imports data from an access file into a SQL Server 2005 database. It has been set to delete existing rows and enable identity insert.
I then edited the .dtsx package in SQL Server Management Studio and added an environment variable configuration to allow me to change the destination database. In the script which runs the dtsx, here is what I have (it's an x64 system, so hi have to use DTExec):
"C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe" /file e: estimport.dtsx /set Package.Connections[DestinationConnectionOLEDB].Properties[InitialCatalog];newdatabasename
and here is the error I get:
Description: The configuration environment variable was not found. The envir
onment variable was: "InitialCatalog". This occurs when a package specifies an e
nvironment variable for a configuration setting but it cannot be found. Check th
e configurations collection in the package and verify that the specified environ
ment variable is available and valid.
I got the package.connections.etc etc path from originally creating the environment variable as an xml config file, then I could open the config file and see what the path was...
Any help would be appreciated :)
View 11 Replies
View Related
May 8, 2008
I have created two User variables at Package level (OldRunID{Int32} and NewRunID{Int32}). I am using two simple ExecuteSQL Tasks to get a Single Row Resultset return (each) and loading them (on Result Set Page). Using the Constraint and Expression Flow controls, I have found that they load to True (-1) instead of the 25 and 50, respectively, that I would expect. I then wrote a Script task to evaluate their values and find that I get zero for Dts.Variables.Count. - Why am I not loading the variables correctly and why can I not access them in the Script task?
View 18 Replies
View Related
Mar 31, 2006
I have a small SSIS package which:
- drops existing table in Excel,
- creates a new one in Excel,
- copies data from SQL to newly created sheet.
So there are 2 connections: one to a excel file, and second to SQL using sa account. I am working on my account belonging to Administrators group.
SQL Server and Excel file are on my development machine. Everything works ok when I run package from file system using DTExecUI.exe (in context of my user belonging to Administrator group). But when I have created job to execute this package problem with security occured. Even assigning local account on which SQL Server and SQL Server Agent works to Administrator group do not help. I have simulated what can happen when running DTExecUI.exe utility in contex of different user. I have used Administrator (I start it using "Run As..." from context menu in Explorer"). And what occured, SSIS package can not login to SQL Server using sa user!
1. Why running DTExecUI.exe in context of different user impact login to SQL on sa accout? If it were Windows Integrated I could understand it.
2. Especially if the different user is also Administrator!!!
I have found solution for running a task in SQL Agent - SQL Agent has to work on the same account on which SSIS package was created. But this is crazy when I would like to deploy it to a production server.
Please help.
Przemo
View 1 Replies
View Related
May 23, 2008
All:
I am working with a package that executes a stored procedure. This stored procedure makes use of a Linked Server. When I execute it in debug mode in VS 2005, it works flawlessly. However when I deploy it to the production server and execute it by going to Management Studio, connecting to Integration Services, navigating to MSDB, right clicking and choosing "run package" I get the infamous:
Login failed for user '(null)'. Not associated with a trusted SQL Server Connection. OLE DB Provider SQLNCLI for linked server [name] returned message "Communication Link Failer"
error. If I execute the stored procedure by itself on the production server, it works flawlessly. It is only when this stored procedure gets executed via the above mentioned way inside of an SSIS pack that I get this error. To me this suggest it is being executed as a different user, but I don't know which one.
Any help would be wonderful.
Thank you in advance...
AccDev
View 2 Replies
View Related
May 8, 2008
hi in my package, some sql operations need the special user name and admin privilage. so how do i create my ssis package so that when it executes it takes the given username and password from the table in some database.
View 8 Replies
View Related
Aug 30, 2006
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
View 7 Replies
View Related
Nov 1, 2006
Hello can anybody out there help.
I am just getting ready to move a system to production using SQL 2005 and SQL Mirroring. The problem that i have is when the databases fails over my connection string user account losses its rights on the DB untill i run the SP_CHANGE_USERS_LOGIN script. I have been trying to come up with a method of getting around this so far the only fix that i have is to make my user account sysadmin but this is far from ideal for production, does anyone have any ideas i was thinking that changing the SID of the User so that they match might get over the problem.
Any help will be good
Matt.
View 3 Replies
View Related
Apr 23, 2008
Hi,
How do you change the name of a package ? if i change it in the properties (identification > Name), this is not reflected on the designer or on the filename...
View 1 Replies
View Related
Jul 7, 2000
Can the user change their password in Enterprise Manager? When they follow the documentation, this does not seem to work
unless they are sa: (I know about sp_password....we are trying to find a GUI method for typical user.)
Expand a server group; then expand a server.
Expand Security; then click Logins.
In the details pane, right-click the login to modify, and then click Properties.
In Password, on the General tab, enter a new password.
Confirm the password.
View 2 Replies
View Related
Feb 12, 2001
My SQL servers are using integrated windows nt security. Our user account is changing to a new domain. Is there an easy way to change the server logins to point to the new domain instead of removing the user and adding a new login from the new domain.
View 1 Replies
View Related
Aug 8, 2007
I'm trying to automate the creation of a databse and a User/Login but i'm running into problem with a conflict between a new user to which i'm trying to assign a WindowsLogin which is already linked to the dbo. (SQL Server 2005)
Is there a way to reassign the dbo's LoginName to some othe user
Here the code
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ABC')
BEGIN
declare @cmd nvarchar(max)
set @cmd = 'CREATE USER [ABC] FOR LOGIN ' + @WindowsLogin + ' WITH DEFAULT_SCHEMA=[Shared]'
exec(@cmd)
END
Which give me the error
Msg 15063, Level 16, State 1, Server SR-DEV-GOI1, Line 1
The login already has an account under a different user name.
The script is running under the @WindowsLogin in question !
Thanks All
Gilbert
View 2 Replies
View Related
Nov 5, 2007
Hi all,
I am creating a dts package to export files from one database to another database.
I tried to search for ways to execute the files and found out that i need to add
reference to Microsoft.Sql.managedDts. However, I cannot find this reference from
my reference. Do i have other alternatives to run this file?
View 8 Replies
View Related
Nov 28, 2001
Hello,
Does anyone know how to change the owner of a DTS package? Currently it is set to someone's domain login and I want to change it to the system administrator account.
The reason for the change is because I think this is why the package will not run when scheduled on the server, but will run locally for the user.
Thanks,
Brent.
View 1 Replies
View Related
Sep 26, 2000
Is it possible to change the database name defined in the destinatation table definition of the transformation object programmitacally?
We have different database names between dev and prod. The package was created against dev. I'm trying to copy the package to the prod server and programmatically change the database name where necessary.
View 4 Replies
View Related
Jul 9, 2004
Hi everyone,
I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!
Thanks,
Pankaj
View 1 Replies
View Related
Apr 17, 2011
Is there a way to prevent a particular user with SQL login from changing their password?
We have an application where we only want to prevent a certain user from changing their password but do not want to use windows authentication?
View 14 Replies
View Related
Apr 8, 2004
Is there an automatic way of changing the Domain user password getting used for running the SQL Server as a Domain user account? I'm taking about EM---Security----Domain User name and the password getting used for running the SQL Server?
View 1 Replies
View Related
Oct 15, 2007
I tried to cahnge this account option to built in account option in SQL ( MSSQLSERVER) properties box. Then SQL Server was log on as Local system. But then I can not view my reports which i created. Now I changed it again to this account option. but I dont exaclty remember the password. Is there anyway I can get to know the password I created.
Thanks
View 1 Replies
View Related
Jun 3, 2015
Is there a kind of log to trace the change on DB user, such as when the password/permission is changed, etc. If yes, how can i get this log; If no, is there a way to achieve this thing?
View 2 Replies
View Related
Jun 18, 2007
I am wondering how I could do the following -
Right now the default folder is Home, what if I have two users User1 and User2, my folder structure is the following -
Home/User1
Home/User2
I want each user to only have access to their folder. That is easy to do.
Once both users have proper access to their folders they still don't have access to Home folder. In Report Builder when I do 'open file' it defaults to Home folder so I get an error. How can I specify the home folder to be User1 for that user and User2 for the second user? I know I can give them both browsing permissions to Home but it's not very elegant.
There must be a way to programmatically change the folder as soon as proper user logs in. Anyone knows how to do that?
Thanks.
View 9 Replies
View Related
May 9, 2007
Hi,
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
View 5 Replies
View Related
Jan 10, 2007
Hi,
In our project we have two SSIS package.
And there is a task (Execute SSIS package) in First package that calls the execution of second package.
I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."
As we are running first package by job, job runs successfully logging above error
The protection level of second package is set to "EncryptSensitiveWithUserKey"
Can anybody please suggest how to handle it?
View 4 Replies
View Related
Jan 15, 2008
I have a package using Slowly changing dimension in the data flow task. It works fine if the number of records are less but for a large file the package fails with the "Violation of Primar Key" error even though there are no duplicate records in the table.
for eg i have a table with employee database with a composite primary key comprsing of Name and Employee Id. I need to do an UPSERT depending on the Name and Employee Id combination. I have a file with 100,000 records and when i try to execute the package it gives an error 'cannot insert duplicate data' even though the combination does not exist in the database.
Please help.
Aashna Behl
View 3 Replies
View Related
Jan 29, 2002
Usually all the user datatypes in our databases have the owner dbo.
One has a few that are owned by a user with dbo rights. I am trying to change them to dbo owner.
Sp_changeobjectowner gives 'object does not exist'
Any ideas
Jim
View 1 Replies
View Related
Aug 24, 2000
Is there a way to change the physical mapping of a user defined data type to the physical SQL Server datatype. It seems that once you create them, it is just about impossible to change it.
Thanks,
Andrew Abrams
View 3 Replies
View Related
Dec 19, 2005
Hello. I am using SQL Server Management Studio (SQL 2005) and created a daily backup job. Inside the job, i have an "Operating system" step to copy backuped up files onto another directory. However, the job kept on failing with an error "Executed as user ... Access is denied." With this error, how can i change the user used to execute the job? Thanks.
View 5 Replies
View Related
Sep 10, 2007
Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.
View 9 Replies
View Related
Jun 15, 2006
Is there any way to change variable scope while using package templates?
I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.
Appreciate any help...thanks.
View 4 Replies
View Related
Mar 1, 2007
I’m working with a team of programmers who are in the process of upgrading an older ASP application to ASP.NET 2.0 (VB). The existing application connects to the SQL Server Database using different Logins, depending on the User logged into the ASP Application.
We are hoping to do the same using ASP.NET 2.0, although we haven’t been able to find a simple solution. The most feasible solution we have found includes programmatically trapping all the Selecting etc events and changing the SqlConnection at run time. This allow for ease during Designing of the Web Pages, but implementation requires adding code to each web page (>100 pages).
We are hoping there may be a simple solution which can be implemented once for the application where the event can be trapped at the application level, and the SqlConnection set at this point. Or possibly another type of solution such as creating a new class etc that can be used?
Does anyone have a simple solution where the username / password for the Database Connection can be set at run time?
View 2 Replies
View Related
