SSIS Package Hangs In Data Flow, Magically Works After Opening And Closing Components

Nov 2, 2006

We're experiencing a problem where intermittently our SSIS packages will hang. There are no log errors or events in the event viewer. It will happen whether the package is executed from the SQL Job Agent or run from BIDs. When running from BIDs it appears to hang inside one of the data flows (several parallel pipes with sorts, merge joins etc...). It appears to hang in multiple pipes within the data flow component. The problem is reproducable, we just kill it and re-run, and it appears to hang in the same places.

Now here's the odd thing: as we simply open and close some of the components in the pipe line after the place it hangs, a subsequent run will go further in the pipeline before hanging. If we open and close all the components after the point it initially hung, the data flow will run fine, from there on out. When I say "open and close" I mean no changes are made, we simply double-click the component, like a merge join, then click 'close.'

To me this does not seem like a memory problem but likely something is wrong with the metadata, where opening a component and closing it somehow alters the metadata to "right it".

This seems to occur intermittently after we make modifications to the package. It's like if you make any mod, even unrelated to the data flow, you then have to go through and open and close every component in your package to ensure it will work. Again, no errors or warnings are fired.

Has anyone seen this type of problem?

View 10 Replies


ADVERTISEMENT

What Does Strategy Exist To Deploy SSIS Package And My Own Data Flow Components Into A Enterparise Server?

Mar 29, 2007



I created a SSIS package and several data flow componenets for this package.



What does strategy exist to deploy SSIS package and data flow components into a enterparise server?



Thanks in advance.

View 2 Replies View Related

Developing Custom Components By Extending The SSIS Stock Data Flow Components

Sep 7, 2006

Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.

But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.

View 1 Replies View Related

How To Disconnect Data Flow's Components Using SSIS API?

Sep 29, 2007

Hi,

I have a SSIS Package which I would like to modify using SSIS API. I need to put new component between some two existing data flow's components. During this process I need to disconnect two data flow's components using SSIS API. How can I do that?

Thanks,
Rafal

View 1 Replies View Related

Programmatically Iterating Tasks/components In The Data Flow Portion Of A Package.

Mar 6, 2007

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.

View 1 Replies View Related

Data Flow Contains No Components After Package Save Operation And Reopening Solution

Jul 25, 2007

I have Data Flow task that contains 50 components.

My computer configuration: 1 GB RAM Microsoft Windows Server 2003

Periodicaly when i try to save package after making some changes Out of memory ... exceptions message box appears , and soon after this Not fatal error occurs ... message box shows . If i close solution and open it again all my 50 components disappears --instead I see clear list, and all my work losen.



Such "Not fatal errors" making hell out of job -- every time I need to change package i must add package to archive!!!

View 4 Replies View Related

Reuse Existing Data Flow Components In A Custom Data Flow Component

Aug 29, 2007

Hello,

Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?

Thanks,

Yoann

View 15 Replies View Related

SQL 2012 :: How To Capture Data Flow Component Name Dynamically While Package SSIS Package Is Executing

Jun 3, 2014

I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?

View 5 Replies View Related

Data Flow Source For MS Access In SSIS Package

Jul 26, 2006

Hi all...

I'm creating a SSIS in the designer view of SQL Server BI Dev. Studio (SQL Server 2005)

I need to import a whole table from MS Access into my local SQL Server.(this task will be performed weekly, so once working I'll schedule a job for it)

I've created a 'FILE' connection to MS Access in the 'Connection Managers'.

When I'm on the 'Data Flow' tab I can't find a Data Flow Item to use as a MS Access connection.
(available on the 'Data Flow Sources' are only: DataReader, Excel, Flat File, OLE DB, Raw File and XML Sources)

What am I doing wrong/missing?

Thanks for your help.

View 4 Replies View Related

Logging Data Flow Steps In SSIS Package

Jul 14, 2006

Hello.

I am using the "SSIS Log Provider for SQL Server" to log events to a table for "OnError" and "OnPostExecute" events of a package. This works as expected and provides a nice clean output on the execution steps of the package.

I am curious as to why I do not see any detail for any/all tasks that fall under the "Data Flow" section of the package though. For instance, on my "Control Flow" tab, I added a "Data Flow" task that simply loads a few tables from a target to destination server. However, there is nothing shown in the logging output. Just that a Data Flow task was initiated. And when I'm configuring this logging under "SSIS-->Logging" in the checkbox area on the left, you cannot "drill into" data flow steps.

Is there a reason why there is no detailed logging for Data Flow tasks? Would getting to that require me to create a custom log provider?

Thanks for the help.

Greg

View 1 Replies View Related

Multiple Data Flow Tasks In One SSIS Package

Jul 25, 2007

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?

View 1 Replies View Related

Data Flow Components

Nov 1, 2006

Does anyone know a good place to download (or even purchase) custom data flow components?

Some ideas I've had (and I'm sure I'm not the first):

Transformations:

Filter
Sometimes I just want to filter out certain rows.

Unicode Converter
Convert all unicode columns in place.

Sequence Column
Adds a column with a sequence of numbers. Or perhaps even random numbers.

GUID Column
Adds a column with a GUID.

Destinations:

Garbage
Would act as a visual indicator in the designer that these rows are being discarded. You could add a data viewer right before it too.

Log File
Good for redirected error rows, writes them to various log files.

Email
Along the same lines as Log File, but sends off all the rows in a single email. Great for day to day things. Nothing too critical.

View 1 Replies View Related

SSIS Package (Data Flow) Validation Time Going Exponential

Jul 25, 2007

I am using SSIS to populate a star schema.

The issue is in the data flow for loading and setting the Fact table dimension keys (the dimensions are all loaded fine). After 16 rather pedestrian Lookup Transformations, I have an escalating problem adding additional Lookup transforms to the Data Flow. The problem is not in execution; the problem is adding more transforms in design mode.

Lookup # Fields in Data Flow Time to validate that lookup
<17 47 Sub-second
17 48 2 sec
18 49 4 sec
19 50 8 sec
20 51 16 sec
21 52 32 sec
22 53 64 sec

While I€™m intrigued by the mathematical progression that is forming here, the issue is that I have at least 6 more Lookups to perform. I hope you can see my dilemma.

I have gone to where it takes a little over 4 minutes each to validate the lookup transform and its associated Derived Column transform and Union transform (Total 12 Minutes). Not only does this add up to many idle minutes to each design step, BUT it breaks the debugger as it pre-validates the ENTIRE data flow before it ever switches into debugging mode.

Some notes:
1. It doesn€™t matter what order the Lookup transforms occur in, the timings are exactly the same.
2. I tried many Data Flow execution optimizations, but they don€™t improve the validation times (or even get a chance to improve the execution times!)


I realize this may be somewhat of a unique problem.



Thanks for any help you are able to lend.



-Dave

View 3 Replies View Related

Grouping Data Flow Components

Nov 15, 2007

Hello everyone,

I am developing an SSIS solution where the Data Flow task extracts data from a source csv file, then performs several transformations on the source data and then starts inserting the cleansed data into several destination tables.

The Data Flow task is getting too large!

Question:
Is there a way (best practice) for grouping components in the Data Flow - similar to the Container concept in the Control Flow?

I know this question sounds too luxurious, but I really loose the overall picture, when the Data Flow canvas gets too crowded.

Thank you in advance.
Samar

View 7 Replies View Related

Opening And Closing The DB Connection

Mar 16, 2008

Hi most of my code follows the following format in a lot of my pages, my question is; Am i approaching it the right way in terms of performance, sql injection attacks and anything someone more knowledgeable than myself can think off, please feel free to criticise the code. Thank youprotected void Page_Load(object sender, EventArgs e)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_EventsByID", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@eventID", SqlDbType.Int).Value = Request.QueryString["id"];
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
eventList.DataSource = reader;
eventList.DataBind();
conn.Close();
}
}

View 7 Replies View Related

Opening And Closing DB In Different Sub Routiens

Apr 20, 2006

If Someone could please show me some example VB code where i can open the my Sqlconnection in the Page_Load subroutien... and then close that SqlConnection in the Page_Unload routine. I want to be able to execute Sql command without having to re-open and re-close the connection.

Thanks,
Greg

View 4 Replies View Related

BIDS Crashes After Opening A Data Flow

Mar 11, 2008



I have a package with numerous data flows in it. (I know - this is not recommended as a best practice.) Anyway, one of the Data Flows just started giving me some trouble. When I opened that particular Data Flow, then BIDS would crash. I tried loading the project again, and opening that Data Flow task, and it just kept crashing. I rebooted - same deal.

The Data Flow was itself very simple. There was an OLE DB Source, and OLE DB Destination, a conditional split, a derived column, a data conversion and a Lookup. Pretty routine stuff. Out of the blue really, and long after I created it - it just starting crashing BIDS when I opened it.

My solution was to copy the Data Flow control flow component and paste it back into the same package. This worked perfectly fine, and didn't see any issues after. So, apparently something in the meta data became corrupt, and caused it to crash.

Anyway, just curious if others have seen this behavior, but also wanted to post this as a solution in case someone else has the same problem.

View 5 Replies View Related

Simple Data Flow Hangs

Jul 26, 2007

Hi

I have a simple data control task that has an OLE source and OLE target.

the source is a SQL query that returns 200m records this is then written straight out to a table. I have used a data flow task so that I can chunk up the inserts rather than using a INSERT INTO.....SELECT FROM.

I have ran it multiple times and it hangs once it reaches 18,961,020 records.

there is no locking on the database and I have even restarted the SQL instance to ensure that there was nothing else contending for resource.

I have changed gthe buffer size and rows per buffer to 100m and 100,000. Now it hangs before the 18.9m mark presumably becuase of the increased buffer size.

I notice that the SELECT statement continues to clock up io and cpu cycles but the BULK insert process has gone.

any ideas on where to start looking?

Thanks for your help

Marcus

View 16 Replies View Related

Opening A Data Flow Tasks Forces A VSS Check Out

Mar 9, 2007

Hey all, is there any explanation why just opening a data flow task causes a VSS check out?

The issue with this is that I have multiple developers, and when one wants to look at a data flow task in a package already checked out, it generates multiple VS errors.

Not big on the priority list, (not as big as the modal dialog issue) but still a pain.



Thanks!

BobP

View 4 Replies View Related

Data Flow Execution Hangs With No Error

Dec 7, 2005

I have a SSIS (CTP June 2005) package with several data flow tasks.  One data flow has 2 OLEDB data sources which are then unioned together, followed by a conditional split, then a derived column transformation, which feeds into an OLEDB destination.  When I run the package, this particular data flow seems to run fine and then just stops.  There are no errors and the records counts don't move.  I've used data viewers to look at the data and it seems fine.  I've switched the OLEDB destination with a flat file and execution runs fine, then switched back to OLEDB and it hangs again (over and over).  There's nothing unusual about the OLEDB destination, and all the other OLEDB destinations work fine.  It also seems to hang on the same destination row number count, but again that row has been verified as valid.  In fact, I dropped the DB table and recreated it with no constraints and all fields nullable, but the problem persists.  Help?

View 6 Replies View Related

Opening And Closing A Connection In A Loop, Should I Or Shouldn't I?

Oct 31, 2006

i have a loop that can run say 30k times.  in there i am using the try / catch / finally blocks to handle ADO.NET Transactions (thanks CADDRE!)Should i open and close the SQL Connection for each record? or open it and close it outside of the loop only once ?thanks in advance, mcm  

View 3 Replies View Related

Custom Data Flow Components, DllImport, And Third Party Dlls

Mar 29, 2007

I've been having an issue with the integration of a third-party DLL into a custom data flow component.



The company sent me a C# project that generates a simple console application. The project includes a class that calls their DLL with DllImport. The console application runs fine.



I created a stand-alone class using the C# class they sent to expose the methods of their DLL. In my custom component, I'm referencing this class to pass data to and from their DLL.



The first method from that stand-alone class that my component encounters simply gets their installation path from the registry and does not use DllImport. That path retrieval works fine. The next method calls a function that is declared with DllImport. Each time the call fails with "System.DllNotFoundException = {"Unable to load DLL AMZip.dll': Exception from HRESULT: 0xE06D7363"}".



I've copied this DLL to countless locations (e.g., the PipelineComponents directory, the project/solution bin directory) and included these paths in all manner of path variables.



What am I missing here? Their DLL is not strong named (does this matter since I'm using DllImport?), my stand-alone class is, and of course, the custom component itself is. I appreciate the help.

View 9 Replies View Related

Power Pivot :: Determine Opening And Closing Balance

Jun 3, 2015

I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance.  To simplify, this is what I did.

1. Dragged months to the columns labels

2. Created a slicer for selecting the Financial Year.  In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.

Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.

To determine the monthly opening cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))

To determine the monthly closing cash balance, I added the following measure

=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])

Much to my surprise, I got the result as seen in the image below.  Cells B5, C6 and D5 are blank.  On going through my Bank Book, I realised that

1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31.  The last transaction was on March 28

2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31.  The last transaction was on May 30

3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.

As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.

Please also note that there can be multiple transactions on the last day of any month.  For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow). 

So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day.  I have to take the final balance on that day.

How to solve this problem i.e. in determining the opening and closing cash balances.

View 10 Replies View Related

Assigning User Name And Password Form The Databse To Ssis Package For Data Flow Operations And Execute Sql Statement

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

DOS Batch File Running Osql - Opening/closing Connections

Jul 20, 2007

I've written a small dos batch script that runs a simple query throughosql and then calls an exe which sends an email if the returned value


Quote:

View 2 Replies View Related

Opening SSIS Package Without Validation

Jun 11, 2007

Is there a way you can open an SSIS package without validating it?

The reason is - when I take a package from PROD and open it in DEV - initially all settings-variables are wrong - and the validation takes heck of long time in that case. And then I need to change the variables and reload the package. And bloody hell - if I forgot to change a variable - I sometimes have to validate package 3 times. And sometimes - I only need to get visual look of package - so why do I always need to wait for validation...

View 6 Replies View Related

How To Make Changes In The SSIS Package W/o Opening It.

Aug 23, 2007

Hello,

We need duplicate a large number of SSIS packages , changing only server connection and database name inside the OLE DB Source sql command. Is there any easy way to do it , I mean without opening every package individually?

Thanks,

BeginnerInBiztalk

View 8 Replies View Related

SSIS Package BLOCKS At Opening.

Mar 21, 2006

Hallo,

I'am relatively new to SSIS-programming and experiencing a serious problem with a package (6618 KB large) containing 5 dataflows. These dataflows all start from a datareader-origin and flow trough multiple transformations. While editing the fifth dataflow the developmentenvironment suddenly became unworkably slow. After any modification made to the dataflow it takes several minutes before I can continue editing. Meanwhile in the task manager the process devenv takes a very large portion (50 to 100%) of CPU.

Building the package,shutting down SSIS and even rebooting the PC don't make any difference. When you close the package ands reopen it, this takes at least 10 minutes.

We work with Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00.

Does anyone know if there is a limit to the number of transformations in a flow or the size of a package? Could that be the problem?

Before this problem occured I was multiplying transformations by copying ,renaming and altering them within a dataflow. Could SSIS have some problem with that?

Any suggestion for solving this problem, other than chucking it all away an restarting, will be gratefully accepted.



With kind regards,





Paul Baudouin



View 3 Replies View Related

Error On Opening A SSIS Package

May 24, 2007

I am getting the following error when I try to open my SSIS package

Error 1 Error loading CCTAdvocateList.dtsx: The connection "DestinationConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. j:cct db migrationcodecct db migrationCCTAdvocateList.dtsx 1 1
Error 4 Error loading CCTAdvocateList.dtsx: The connection "SourceConnectionOLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. j:cct db migrationcodecct db migrationCCTAdvocateList.dtsx 1 1

The connections which BIDS is complaining about are not even there in the package. I had created these two connections some time ago but then I had removed them from the package and it was working ever since.

Suddenly I have started to get these error messages.

regards,
Abhishek.

View 2 Replies View Related

SSIS Package Hangs

Mar 29, 2007

I am encountering a weird issue with SSIS.

I have a very simple SSIS package that executes a .bat file. Here's
the
actual file it executes:


@Echo Off
c:
F:
cd ReportingServicesScripts
rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest
EXIT


The .bat file executes a Reporting Services .rss script using the
rs.exe
utility. When I run the command from the command prompt, it runs just
fine.
When I execute the .bat file manually, it runs and completes. When I
execute
the actual package, it also completes. But when I schedule the package
as a
job, it just hangs...it never finishes.


The owner of the job is an administrator in SQL Server. I have the
SQL
Server Agent configured to interact with the desktop - although
my .bat file
requires no input from the user.


I've created other jobs that just execute plain old SQL using the
same
owneer and these jobs complete just fine.


What am I missing?


Any help would be appreciated!


Thanks!!

View 3 Replies View Related

SSIS Package Hangs Sometimes

Oct 9, 2007

Hello everyone,
I am having an odd behavior with an SSIS package that transfers data from one database to another. It seems to me that with some of the larger databases the services just stops working (0% utilization) when it is almost done. I don't get errors and the job stays in the "executing" state.

This doesn't happen always, but it will happen once the package is ran many times over the course of the week, I was wondering if you guys could point me in a direction on how I can find out the source of these hangs. Right now I am leaning towards it being a memory issue (or other misc. hardware problems) but I would like to rule out that it is software first.

Much help is appretiated.

Thank you
Jaime

View 13 Replies View Related

SQL 2012 :: Opening A Deployed SSIS Package?

Feb 6, 2015

I have been handed a datawarehouse environment to look at however I cannot find the ssis packages (.dtsx).

I see that the packages have been deployed to SSISDB but I can't find the files to load into Datatools.

Once the files have been deployed is it possible to remove the package files? Is there a way of retro'ing the deployed packages out of SQL so I can view them in Datatools?

View 2 Replies View Related

SSIS: Opening Package Takes 8 Minutes

Apr 20, 2006

I have an IS package containing approx. 10 tasks in the control flow - one of these tasks is a rather large data flow containing around 50 transformations plus 3 sources and two destinations. Around 10 of these are script components and another 10 are Union All transformations. The rest are primarily lookups, conditional splits and derived column transformation.

The XML file containing the package is approx. 4.5 MB. As I am developing the package, it is becoming increasingly slow to work with as more transformations are added to the data flow. Now, it takes 8 minutes every time I have to open the package for development (DelayValidation is even set to true) and DTExec (not the debugger) uses the same amount of time before it starts executing the package. It also takes a very long time to edit the data flow, as I typically have to wait 1-2 minutes every time the designer has to "commit" a change.

Does anyone have any idea what can be done to speed up the package - both with regard to development and execution?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved