Hello,
I have designed a quite small SSIS package and everything worked just fine .
Untill I moved the SSIS project folder to another solution folder and added whole solution to Visual source safe.
Since then opening the package for design has become unbelievably slow, debugging it even worse.
I think the cause is in moving the SSIS project folder. But I have no idea how to fix this.
Any suggestions ?
Janez
Hello all,
We have noticed in our environment slowness when starting SSIS packages from SQL Server jobs. I did a quite detailed study on when the slowness actually occurs and what are the consequences. Here are the results.
The SSIS package execution is slow if all the following is true:
The package is started from a job. If started directly as a SSIS package, the execution is fast.
The job is running on a 64 bit Windows Server (SQL Server 2005 SP2). The SSIS package and the job are either on the same server or on different servers (the second server is SQL Server 2005 SP1). If the job is run on a 32-bit workstation (Windows XP SP2) the execution is fast (the SSIS package still being on the server).
The package contains tasks.
§ If there are no tasks, just an empty sequence container, the execution is fast.
§ If a package that has no tasks has logging into the database configured, the execution is fast.
§ Slowness has been verified with A) a package having a single Execute SQL statement and B) a package having a Send Mail task.
It doesn't seem to matter which user account is used on when running the job.
The slowness happens in several locations, e.g. (there are also others, at least the following have been verified)
There is exactly 30 seconds lag between starting the job (as seen from job history) and when PreValidate (as seen in the sysdtslog90 table) of the package appears in the log.
The validation of the package takes 15 seconds (the time difference in the log betwen PreValidate and PackageStart)
The problem is really affecting our production environment. Currently the only solution we have come up is to put all the jobs on a workstation and use the workstation as a production server for the jobs.
I haven't heard anyone else having the same problem.
I am using SSIS packages for data transfer, When i run the package on virtual server it takes more time as when run on a PC. After analysing i found that Package when run on Virtual server takes time in startup around (50 sec) or so.Could anyone help me with a little bit of detail description as to why it runs slow.
View 10 Replies View RelatedHi,
I have created a package that has
2 SQL Execute Task, One Loop container, 2 Data Flow tasks, 1 Foreach loop container, 1 ftp task. The data flow tasks has 1 oledb source, 1 flat file source, 1 row count transformation, 1 recordset destination and 1 oledb destination.
When I load the package into BIDS it takes 125 MB of memory and then everything is slow, the properties panel slides in slowly and exists slowly. The object is the packages are not painted properly. to make changes and run takes lot of time.
Am I doing anything wrong here? Why is it consuming so much of memory?
Thanks in advance for your help.
Regards,
$wapnil
Hi
I am busy designing a ETL solution and have a question about how to design the packages.
We have over 30 source systems for different customers. We are building a WH that will combine all this data for analysis. The main issue is that these systems are always at different versions of the software. When a patch is released, it usually goes to one or two for a Beta process before it moves to the rest. These patches can affect the DB design, and we would want to be able to extract any new data as it comes available from the systems that have it.
Solution 1 - Package Versions
The idea was to create the SSIS packages with a version number in their name. For each change, you would create a new version. The Batch control application that is being developed will store which system needs to use which version of the package.
Solution 2 - Multiple paths within a package
This was to create a single package for each table, with a conditional split as the first task. The batch system will still provide which path the package needs to take with different Data Flow tasks containing the different column mappings.
Both have pros and cons, but was wondering if anyone has experience with this type of setup and which way worked best, or if there are any other options.
Many Thanks
Michael
Greetings SSIS friends,
I am in the process of designing a package for populating a Dimension table for my new data warehouse. I would like to discuss with you my proposed solution in the hope that some of you will be able to advise me on any mistakes that I have made so far.
I have 3 source tables which are used to populate my Dimension table (I will restrict each source table to a few columns to keep this simple).
The source tables are :
Meetings (MeetID, NameID, Meeting_Start_Date, Meeting_End_Date, Meeting_Time_Stamp)
Events (EventID, MeetID, Event_Start_Date, Event_End_Date, Event_Time_Stamp)
Names (NameID, Long_Name, Short_Name, Names_Time_Stamp)
My Dimension table structure is very basic as follows :
Product_DIM (EventID, MeetID, Meeting_Long_Name, Event_Long_Name..... Meeting_Time_Stamp, Event_Time_Stamp)
Please note that I haven't included the start and end dates to keep this simple, but in my real solution, the Dimension table has many more columns.
The Meetings and Events tables contains hundreds of thousands of records so when my package is run, I should only retrieve information that has changed since the last time my Dimension table was populated. This is why I store the time stamps in my Dimension table.
So when my package runs, I get max(Meeting_Time_Stamp) and max(Event_Time_Stamp) from my Dimension table then retrieve all records from the source table where their timestamps are GREATER than the max values.
The problem is this :
If in the source system, an event is changed, the time stamp associated with that record will change value but the meeting record associated with that meeting will not have its time stamp changed.
How do I ensure that when my package is run, I retrieve all meeting and events information even if one hasn't changed?
Should I build a table made of IDs?! And if I do need to build a table made up of IDs, should I store this as a permanent table in my database or is there an SSIS data structure I can use to hold this information?
I hope this makes sense!
We are downloading 4 large (500mb) zip files in the package. Those come from 4 different FTP sites. Sometimes the FTP download on one of those fails.
Zip files contain images which need to be uploaded for existing listings. Each zip file is processed the exact same way. The question is how can I make the SSIS package so that each downloaded zip file can be processed immediately after the download - and not duplicating the processing logic?
Hello,
I am new to SSIS. I need to transfer data from SQL Server 2005 Operational Database to SQL Server 2005 Report Database. The upload needs to work every night. There are few master tables and remaining are transactions tables.
I am planning to create 2 packages one for master tables and other for transactions tables.
Is it the good approach?
Also few of transaction tables are heavy in terms of number of records. Will it better if i further break them in many packages?
I am using book "Microsoft SQL Server 2005 Integration Services Step by Step".
Can you suggest any other good book?
Regards,
Shailendra Shelar
Win 7 SP1 x64 PC. I installed SQL Server 2014 Dev Edition + Visual Studio 2015.
I'd like to create some basic ETL SSIS packages, and I worked very comfortably in 2008R2.
For 2014, I started with this tutorial:[URL]However, it says to go to Start->All Programs->Microsoft SQL Server->SQL Server Data Tools.Â
I did explicitly install SSDT when I installed VS2015. I also installed it separately. I see SSDT listed in Programs, and SSIS is running according to SQL Server Config Manager, and Services. Half of Microsoft's docs seem to be 2012 era, which is a shame because 2014 is out and it's nearly 2016...
how do I get to the GUI where I can design ETL packages?Â
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
Does anyone else have problems with the speed of the design environment for SSRS reports? We are using visual studio to create and manage hundreds of production reports (Oil and Gas).
It literally takes 5 seconds for the design environment to react to each change in the report layout. For instance, a common change is to reposition a text box, change the length, text, font size, font weight etc...:
Grab the text box - 5 seconds until designer responds - then reposition
Grab the edge to change the length - ditto
change text - 5 seconds for designer to "save" the change and allow next action....
blah - blah - blah....
So, performing the simple change above takes at least 30 seconds in addition to whatever time it took to edit the text box.
I've talked to the other developers using SSRS here and they all report that's "just the way it is".
Is this normal or is there an environment issue/setting we are overlooking.
Thanks, Mike
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
SSIS packages running slow on startup in 64 bit prod env. looks like it is taking lot of time (around 2mins) to validate the package. Delayvalidation is false (default); changing it to true didn't help. Any fix for it?
Executing packages using dtexec commad.
We have job packages which loads 10s of child packages in sequence using package execution task... taking hours to complete. Same are working fine 64 bit dev env.
Prod env:
Windows 2003 SP1
SQL Server 2005 SP2
Thanks
We have been working with SSIS for a while and we have not found a solution or a reason for this. We have a master package that calls 10 packages in sequential order. (as shown below). If we execute each one of the package separately the run in less than 2 minutes, but when we call them through the master package the execution time start increasing as follows: Child 1 (2 min), Child 2 (3 min),, Child 3 (4 min), Child 4 (6 min), Child 1 (7 min), and so on. The execute package task has the ExecutionOutOfProcess = false (when we set it equal to True even takes longer to execute, it was creating a dtsHost.exe process for each child and always remain in memory after the package finished executing). Can someone please provide a solution or a workaround for this? Any help would be appreciated. Any help will be appreciated.
Master
|------Child 1
|
|------Child 2
|
||------Child 3
|€¦€¦€¦€¦
|€¦€¦€¦€¦
||------Child 11
Thanks,
I have a package that reads the contents of 11 Excel files into various tables. Opening this package in the designer, or with DTExecUI is extremely slow. In both cases when I open the package is takes over 10 minutes to do anything. Visual Studio gives the "Visual Studio is Busy" message for 10 minutes and DTExecUI just hangs. DTExecUI actually hangs twice, once when opening the package and a second time when clicking "Execute" (totalling over 20 minutes). It seems like no matter how I try to run the package it will always hang for 10 minutes before running with no status message or anything. Once it runs it completes quickly with no errors.
The various tasks in the package are fairly simple, most being Source > Data Conversion > Destination.
Any suggestions?
I have a parent package which executes 14 child packages in parallel, which on average take ~10 seconds each to complete when I execute the parent packege using BIDS or DTEXEC.
However, if I run the parent package using SQL Management Studio (Integration Services > Stored Packages > MSDB > Right Click > Run Package) each package takes in excess of 10 minutes to run, getting progressively slower as each package starts.
Surely the package is executing in exactly the same way as BIDS/DTEXEC, just a differenct UI?
Can anyone explain why this happens?
Thanks in advance,
Leigh
I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?
View 4 Replies View RelatedHi,
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?
Hello everyone!!
I am completely new to SSIS and have been given a large project (of course with a tight deadline) that has the absolute requirement of using SSIS. I am/was very, very good with DTS and could easily accomplish what I need to do with an ActiveX script task in DTS in no time, but as this is new development, we are not to use ActiveX script tasks within SSIS since it will not be supported in the next SQL Server release. I'm thinking script task, but please give some comments on how you would accomplish the following in SSIS (please remember I'm new to SSIS, so don't assume I know anything. )
I must accomplish this: in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and we have X amount of regions. (Pseudo code followed by standard explanation)
Select a max value from region lookup table in SQL (this is the # of regions)
for N=1 to MyMaxValue
select states from region lookup table where region code = N (the current region we are on)
'this returns a list of states in a region, need these in array or recordset object or something
Open an output file which will be a tab delimited text file we will write results below in loop to (in DTS I would programatically kick off a transformation task in the package)
'loop thru states returned, so if in a rs object...
do while not rs.eof
execute customer stored procedure, passing as a variable the current state we are on
'this will return all customers within a state, this whole result set (approx 1 million) needs to go to the tab delimited file
'I have to execute this stored procedure for each state & then write results to the SAME file, until we are onto a different region
rs.movenext
close file
loop
next
OK, so basically, as you can see, Its sort of simple in a way what I need to do, i just have no idea how to go about doing this in SSIS. I can not hard code any state or region values. I MUST read them in from the lookup tables as region codes are constanatly changing and we are constantly adding in new states and new regions, so with above coding idea, it would always dynamically pick up any new states, new regions or changes.
So in a nutshell, I need to create separate tab delimited text files of customer informaion. One for each region. Each region consists of X amount of states and there are X amount of regions. Pretty strait forward, huh? The requirements are strait forward, but SSIS is throwing me for a loop... it does not seem flexible enough to be as dynamic as I need it to be to do this. I'm sure it is, just my understanding of it is very basic so far.
Please provide your suggestions! I think a lot of newbies would benefit from some SSIS design info... how to do common things in SSIS, but beyond just retrieving a recordset and writing it to a file... what do you do when you need to add just a few layers of decision processing, and retriving recordsets and writing files based on that decision processing?????
THANK YOU!!!!!!
Kathy
Hi everyone,
Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again
.
That same behaviour happened with Sql2k-dts.
Thanks in advance and regards,
Can anybody give me design ideas on the following?
I have numerous tasks, any one of which can fail. I want to point them all (via 'Failure' constraint) to a SendMail task for a "failure notification" email. This I have setup, and is working fine. Now, I want to have a changing message for the email's body (MessageSource) to say "Process A has failed", or "Process B has failed", etc.
My initial thought was to add a variable, then add a ScriptTask between each task and the single SendMailTask, have the script update the variable, and have the MessageSource (body of email) mapped to the variable. Is this the proper way to go about this? Seems like if I have 20 processes that could fail, then I'll have to add 20 script files; this becomes a bit unwieldy the more processes that I want to monitor.
Is there a better way to accmoplish this?
Thx,
Mojo
Hi,
Does any one have a workaround this issue. If I designed a package from SQL2000 and save it on SQL 7, I can't open/execute it from SQL 7.0. My workstation is SQL 2000 and the server is SQL 7.0, even if I have the same SQL 7 but different SP.
Thanks
Hi all,I'm fairly new to this...I't trying to design a DTS package on SQL Server 2000`, which willconnect to the server, export some table date into a .txt file (basedon a select statement) and then delete the data from the table (basedon a delete statement) upon successful completion of the export.So far so good..Now, upon the successful deleteion, I want to export some more data andthen (upon the successful export) to delete the data. I want to repeatthis process approx. 10 times.Though, when I try to link the deletion to the new export on success, Iget the message: "Defining the precedences between the selected itemsis not valid."Any idea on how I can accomplish what I want to do?thanks a lot
View 3 Replies View RelatedAll:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Thanks!
I have a bunch of packages that take views and create tables from them. Some of the views are rather complex, but the packages themselves are very simple... drop and re-create a table using the data from a view on the same server. We create a new DB for each year, and this year we've upgraded to a new server with SQL 2005, so our DTS packages on the 2000 SQL server had to be recreated in SSIS on the new server. No problem, as I said the packages are really simple. But when I create the packages in SSIS they now take an extremely long time to execute, and I cannot figure out why.
For instance, one DTS package would take approximately 5 minutes to run when the view contained hundreds of thousands of rows and the underlying tables contained millions. But now, even with MUCH smaller tables (since it's the beginning of the year, new DB) the SSIS package I created on the new server takes over an hour, literally. The view that the SSIS package is using to create the table only takes about 15 seconds to execute in management studio (only about 16,000 rows). How can this possibly take so long??
the new server is virtually the same hardware-wise... 4 x 2400mhz, 4gb ram, win2k3 server
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
Hi,
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
I designed an SSIS package about 200 packages in one project.
the package extract from live to reporting server. some of my packages are very slow about 10 of them. strage enough the ones with more data number of rows run very fast. I'm using Source->Lookup->Conditional split->OLEDB Destination or OLDB Comand.
Can someone help me to found out what could be the problem. I'm very new in SSIS.
On a dual opteron server, viewing the properties of a task inside a data flow was immediate up until this morning.
We just installed SP2, rebooted, and now you right-click on any task and it takes between 30 secs and 5 minutes before the context menu pops-up.
The server is 99% idle, most of the 16 Gigs of memory are idle as well.
I don't know what is wrong but the moment i open SSIS it run's too slow...
AND if i have 100 warnings it takes it's own time to even open the designer...
I am just getting frustated with this
If i open my browser it's slow n email slow...
Any help is appreciated..
hi~
our system to run the the job with SSIS package is slower than DTS package.
The SSIS package action is the same as DTS package.
Why?
What do I take care??
Thanks!!!
Hi
i had a problem with the SSIS response time - its very slow.
When I try to open the solution and than click to open the Pakage it's take at least 6-10 minutes until it's open.
Please advice what can be the reason
10x