This may sound a little anal-retentive, but I have a number of SSIS packages that, when I open them, the first thing I have to do is scroll to the left or up to get to where the tasks are displayed. Even if I move the tasks right or down, they still end up in that initial position. This happens even if I use auto arrange.
Is there a way for me to set the package so it has a consistent point or display at which it opens?
Hi
I'm not a sql server savvy, so I need assistance on the following two scenarios:
A customer runs a script like this (slightly larger, but I ripped away the meat)
---------------------------------------
create database test_database
go
USE [test_database]
exec sp_changedbowner 'sa'
use master;
go
sp_grantlogin 'server01CUSTOM_ADMIN';
go
use test_database;
go
-- lots of table creations, where one of the tables are TEST_TABLE
sp_addrole 'ADMIN_ROLE';
Go
sp_grantdbaccess @loginame = 'server01CUSTOM_ADMIN', @name_in_db = 'USER_ADMIN';
go
sp_addrolemember @rolename ='ADMIN_ROLE' , @membername = 'USER_ADMIN';
Go
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[TEST_TABLE] TO [ADMIN_ROLE]
GO
---------------------------
Now, if a person is added to the server01CUSTOM_ADMIN group, he/she should be able to do the following: (let's say it's a he)
- Create a test.udl-file (win xp). Set a provider to sql server.
- On the connection-tab enter hostname of database server in the Data Source-field and use windows NT Integrated security.
- When he now test the connection, it should work, since he has access to the database.
- Also, using the dropdown "3. Enter the initial catalog to use:", he should see SOME datatables. Not ALL, not none. The ones that he has access to.
So, if TEST_DATABASE is the only access that server01CUSTOM_ADMIN has, that database and only that one should show, right?
In my customers scenario, some databases show (irrelevant ones), but not TEST_DATABASE. In my test, I still get ALL databases. Even after I rip the guy out of the Administrators-group and Users-group. He's only a member of the CUSTOM_ADMIN-group on server01. "Test connection" succeeds, and all the databases on the server shows.
What I hope for is following questions like "He's probably sysadmin, check it" etc, so that I can systematically (using your brains) filter out the reasons for these scenarios to happen.
Thanks in advance.
I have a problem with my Microsoft Visual Studio 2005, since I try to click and drag SQLDataSource to design page, it won't display SQLDataSource tasks as usual so that I have a problem to configure SqlDataSource. Any help pls? Is there any way to solve this, since I'm not interesting to reinstall again.My specification computer : Pentium IV 2.00 GHz and Memory 512 MB.Best Regards,Aries
View 4 Replies View RelatedThanks in advance in reading this thread.
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
Thanks in Advance.
Hello,
I have done a search and have read some of the posts, but am left more confused than before. I am fairly new to SSIS. Here is my situation and what i am trying to accomplish.
I have a package that has a sequence container, in which there are multiple SQL tasks (about 20) running in parallel. I have checkpoints enabled, and FailPackageOnFailure enabled as well. If the package fails, when i re-run the package it will run the last task as well as all the other tasks. What I am looking to accomplish is when the package is re-run, have the SQL tasks that failed ran and not the previous successful tasks.
I think the best way would be via disabling tasks on successful completion of a task, where it writes the name of the SQL task to a temp table, but I am skeptical.
Can anyone point me in a direction to help me accomplish what I am looking for please.
Thanks in Advance.
Hi,
I have a Flat File Source and I want to retrieve few properties of it in an Script Component. How do I?
Also, How could I make the file path of Flat File Source or Connection manager dynamic or configurable through some file ?
any input is appreciated.
Fahad
Hi everyone,
Once I've accesed to package by means of LoadFromSqlServer method how to read its Sql Tasks, for example?
I'm trying with the Executables property but unsuccessfully results:
pkg.Executables.Item(0)
Thanks in advance,
Hi,
We want to develop an error handling process that will log the errors into multiple destinations (eventlog, text files or sql database) depending upon a variable set in the package. Also we want that this errror handling process should be initiated by all the tasks in the package on error.
Is this possible? Can the same event handler be called from multiple tasks in the package? Also in the event handler can we call another package which actually does the error handling. This way we have only one place to change our error handling process in case required.
Thanks in advance for your help.
$wapnil
I have several sequence containers in one package that fire off execute package tasks. I would like each of the sequence containers to start at the same time when the job starts running. However when I set them up to do that, i get an error that the variable cannot be read because it is locked. I have the variables setup as readonly so not sure why they are being locked. When I run the package and have each sequence container fire off after the previous one ends it runs fine.
Any ideas on how to get around this?
Thanks!
I'm trying to do some custom SSIS logging using event handlers, similar to the ideas provided by Jamie Thomson in the past. My problem is that when I use System:ourceID as one of the items to be logged, I can't match up the SourceID to any of the GUIDs that are displayed in the property window for the various tasks in my package.
Where is this sourceID coming from and how can I track it down?
Thanks for any insight on this.
John Woods
Thanks in advance in reading this thread.
I have developed a big SSIS package to extract data from flat-files ( + 200 Dataflows ).
The situation is the following, inside de SSIS package, there are a lot of validations before extracting & loading the flat-files, i'm running this validations in paralell, so that when a file arrives, it enters the "validation process" and start extracting the file.
When i run the SSIS package from BIDS it works the way i have concepted it... but when i run the ssis in the server, the tables that are loaded through the process are only "available" when the SSIS PACKAGE ends, it is imperative that trough the process, when a table receives new data, it becomes ready, and don't just be available when the SSIS package finishes...
I have attached the an lousing .jpeg.
It is importart for the tables to be available, so the stored procedures(OUTSIDE SSIS PACKAGE) that are dependent of some tables, start working before the SSIS package Ends.
Thanks in Advance.
Hi there,
I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?
Thanks in advance for any help you can give me.
Sincerely,
Ricardo
I have a Package and a DataFlow Task.
The Package has TransactionOption=Required.
The DataFlow Task has an OLE DB Source and an OLE DB Destination.
The DataFlow Task has TransactionOption=Supported.
The package executes on a Workstation and DataSources for the OLE DB Source and the OLE DB Destination are on a Server.
After the package had been launched an error message showed:
[OLE DB Destination [43]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "DWH_Destination" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[DTS.Pipeline] Error: component "OLE DB Destination" (43) failed the pre-execute phase and returned error code 0xC020801C.
[Connection manager "DWH_Destination"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
[Connection manager "DWH_Destination"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D024.
If I set TransactionOption=NotSupported in the DataFlow Task then the package executes successful.
What is the problem?
Thanks in advance.
What are the advantages and disadvantages of having multiple data flow tasks in one SSIS package?
Is this a good idea at all considering the workflow may be similar now but may change in the future? Should it be left as one data flow per package?
I have some "Execute T-SQL Statement Tasks" in a package. I would like to run this same package on another SQL Server without having to change it on the other server. Since the server name can be given when setting up the connection, I think if I leave the server name out then the package could run on any server? Is my assumption correct?
View 10 Replies View RelatedHello
As a beginner I have created some Packages to import Data to SQL Server.
The Packages are running well.
I have created a Task to run the Package on a SQL SERVER 2005 Express. The Task starts.
Then a Execute Package Wizard Picture appears on the Screen.
I have mannually to select the Connection Manager and the Connection String.
After the selection the Task runs without any Problem.
Now I like to get the Task to run without any manual intervention.
I was looking in Internet but could not find a solution.
Can someone give me a hint how to get the Task working without intervention?
Thanks for an early answer.
Regards
Chaepp
Hi,
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
Thanks,
Vitaly
Hi,
I have a problem with the task "event watcher".
I've made a query like the one in msdn (SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE Targetinstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name="e:\\temp""). I have 20 similar tasks for watching in different folders, but when there are too much tasks in parallel, it doesn't work anymore. I change the numbers of executables to 128 (in the general properties of the package (to test)) but it doesn't seems to work.
I don't understand why it works when there are only 1 or 2 (6 seems to be the maximum) tasks and not if there are more than 6.
Could you help me with this issue?
Configuration : Windows Server 2003, SQL Server 2005, SSIS, Sql Server Agent
Thanks a lot.
Julien.
Hi folks,
I have come across a situation where there 10 tasks. The second task on the flow is a script task which disables all further tasks based on a condition. I thought that the logic would be better if we force terminate the package successfully at this stage itself. How can this be done.
Thanks
Subhash Subramanyam
HI All,
In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.
Thank you.
Steve.
As part of my SSIS package, a list of sites is created that need to be created on a remote machine. let's say 1000 sites. I need to pass this list to a web service so web service sitting on that machine creates these sites for me. MY SSIS package does not run frequently so I can sacrifice time a little bit to get better functionality.
I need to move the sites that are not created (for any reasons) by web service to another table and successfully created sites to another table, so I need to get confirmation for each site from the web service.
Which option is better?
1) Calling web service for every single record (site) and get the confirmation and then based on the confirmation I move the records accordingly. I know this might be very time consuming, but as I said my SSIS package might only run every six months
OR
2) Sending records to a web service in a batch and get the result. I don€™t know how to do this though.
Any help or better solution is appreciated.
When I try to open the designer (with exiting, e.g. tutorial or new project/package) I get the following error message:
Microsoft Visual Studio is unable to load this document:
Index (zero based) must be greater than or equal to zero and less than the size of the argument list
I reinstalled (repair) - still not usable - I saw another post with this issue and the only recommendation was reformat and reinstall - seems drastic, looking for a less intrusive solution
thanks
Richard
I ran into a variety of problems trying to set a script task breakpoint in a package containing multiple script tasks. The debugger apparently treats the breakpoint as if it were set in ALL tasks in the package, not just the one in which it is actually set.
At best this results in hitting breakpoints in scripts where they are not set and at worst the debugger brings up the "Send error report" dialog and quits (while the package continues to run). The latter seems to happen most often when the script with the breakpoint has more lines than an earlier script and the breakpoint is set at a line number that exceeds the number of lines in the earlier script--it bombs when the earlier, shorter script starts.
To get the debugger to work under these circumstances I had to add some nonsense code like
While False
Dim i as Integer = 0
End While
to every script, at the same line number near the beginning of the script (line 40, for example). I then set a breakpoint on the middle statement in one of the scripts (doesn't matter which) to cause the debugger to open at runtime. It doesn't hit the breakpoint because the line is never executed. If the breakpoint is set on a line that can be executed in any script in the package, bad things tend to happen.
I then add a "stop" statement to the script that I want to debug. This only works if the debugger is already open, hence the dummy breakpoint above.
This workaround is usable, but I am debugging a package that has quite a few scripts and having to insert dummy code in all of them at a fixed line number is rather inconvenient. I would really like to see breakpoints work the way one would expect--only in the scripts where they are set.
Is there some other, easier way around this problem? Is there at least an easier way to get the debugger to open so that "stop" will work?
hi all,
i have a package; thats executing another 4 packages, i can able to execute these packages in ASP.net
I want to display the status notification of the executing packages in the webpage( while packages are being executed, i want to display which package is executing, and what is the status of the package! whether it is executing or not? or it is raising any errors?) every thing i want to give in the front end--> its not my requirement, my client ask me to do that!
so please help me! how to display these status information of the running package in asp.net (c#)
(i'll give you one good chocolate if you give me good solution!)
post your mails to vivekanandaraj.natarajan@honeywell.com Thanks you a lot!
Please help. I'm completely new to SSIS, and I was hoping someone could point me in the right direction(s).
I am developing a winforms client app, and I need to be able to provide the user with the ability to import data in CSV format into our application's database.
I'd like to use SSIS if possible, as long as what I am trying to do isn't near impossible.
I'm thinking of a UI where the user can specify a flat file (CSV) to upload and be processed by an SSIS package on a remote database server.
This package will be responsible for validating the CSV file and inserting data into the database as appropriate.
Is there a way I can:
launch an SSIS package remotely, from a winforms app on the client machine that is not running SQL server (preferably asynchronously) -- and pass it some parameters, including the import file itself
(optionally) provide progress feedback to the client pc to let them know it is being processed
display a nice SSRS report on the client upon completion that tells them exactly what the success/failure of the import was (how many rows processed, which ones failed and why, etc)
Any helpful examples, links, etc would be most appreciated.
Thanks in advance!
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â
Hi,
I have been using a licensed copy of Visual Studio 2005 and MS SQLServer 2005 for some time but am only now trying out the Reporting Services functionality.
I have attempted to follow the instructions from url:
http://www.microsoft.com/technet/prodtechnol/sql/2005/usingssrswithsqlexpress.mspx
However despite confirming that the report services are running and also checking the configuration by following the information in the above web page I still get the following problems.
1. When attempting to create a project via the wizard I get the following error: Exception has been thrown by the target of an invocation. If an attempt is made to add a new data source I am unable to choose the data source type (eg: Microsoft SQL Server). I am not given a choice for the type, in fact the relevant drop down is blank.
2. A project can be created without the wizard but if I right click the Shared Data Source folder I do not see the next Pop up to set the data source parameters.
I assume I am missing something quite fundamental - however so far I cannot see what.
Thanks in advance for any advice...
Regards Andrew
Hi, trying to use SQL server for the first time, and it's asking for a password that I don't know. Any guidance on that? Thanks.
View 5 Replies View RelatedHi all. The company I work for is looking for a new SQL server. Where can I find information and or a tool for sizing information? By sizing information I mean how big a pile of hardware am I going to need to run MS SQL for x number of connected users with x size database, etc. I've been tooling around the internet and MS' site but can't find any info on this.
Can anyone shed some light for me?
i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \serverfolder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '---' is not yet available". can you guide me what are causes of this problem and how may i solve it?
Thanks for help.
Regards,
I want get get results in sql that are all written in UPPERCASE but I want to receive them in Initial Case format
I know UPPERCASE is UPPER
lowercase is lower
but what is Initial Case(first letter Capital in a word)
We had a runaway query which built the size of tempdb to 24000mb. Then someone changed the unrestricted file growth property to restricted growth while the size was 24000mb. Now I can not reduce the initial size. I have set the property back to unrestricted file growth. I have shrunk the tempdb and available space is almost 24000mb. I have stopped sqlserver. I even deleted the existing tempdb.mdf & tempdb.ldf files. But when SQL server is restarted, the initial size is set to 24000mb. It will not let me reduce the size. Is there anything short of manipulating the system tables to reduce the size back to 500mb?
View 5 Replies View RelatedHi All,
I need to create a brand new db in prod and I was wondering if anybody has any recomendations for initial .mdf, .ndf, and .ldf file sizes.
Thanks.