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.
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?
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.
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
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.
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!!!
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
Hi all,
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
I searched on this in the forums but based on the question it's difficult to kow if it has been answered or not.
Details:
I am writing some SSIS packages that need to be executable on another server. Many of the Data Flow Tasks copy data (such as from a Fuzzy Grouping transformation, and lots of other stuff) into a new table. But the other server will not have these tables set up for the first run.
My current solution is to check information_schema.tables and drop IF EXISTS. But, then the Data Flow Task will not work (becase table does not exist). So, I script to new window a create table statement based on the existing table that I use in my dev environment. This is a hack and I want to find a better method.
It is quite possible (although unlikely) that the source columns could be changed in the future, or some query used to pull the data might be modified. If this happens, then I would need to change the CREATE TABLE Execute SQL task. I want my package to accommodate without having to modify it.
When I use the Import/Export Wizard, I can select a table name from the drop down list OR type in a new name. When I type in the new name, it assumes I want to create the table. NOW, is there a way to mimic this in BI Developer Studio? Yep, I saved the Wizard version of the SSIS package and all it does is run a CREATE TABLE statement first.
I am looking for a way to leave a Data Flow Task destination table name as-is, and have SSIS auto-create the table if it doesn't exist already.
Any ideas?
Brian Pulliam
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 RelatedOk, I created SSIS packages on my local box. All of my packages are using config files for the db connections and other configurations that'll be changed per environment. My question is how do I deploy the .dstx file and the associated config file to the servers?
Right now I'm running the packages in BIDS as I create them. I now want to run them on an actual server
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.
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
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?
What are the minimum permissions required to deploy an SSIS package to SQL Server? Here is what I have tried:
1. No additional permissions (i.e user created with no special permissions):
Deployment Error: No execute permissions on sp_put_package???
2. Dbowner role on msdb dataabase
Deployment Error: The SaveToSQLServer method has encountered OLE DB error code 0x80040E14 (Access to Integration Services package 'xxx' is denied.). The SQL statement that was issued has failed.
3. Sqladmin role on login: No errors
Most DBAs are reluctant to give sqladmin role to developers. Is there any way around this restriction?
I have a SSIS package, which get data from oracle 9i, and dump into sql server 2005 64bit itanium, its running fine. When I am deploying the package on SQL Server 2005 its showing me the error with connection. on 64bit Oracle Net manager showing its able to connect oracle successfully. I think actually the problem is with encryption, as the package is developed on 32bit machine, therefore all connection strings are encrypted for that machine. Is there some has faced same issue? please help!
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.
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
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
Hi there,
Something a little wride mysterious is happening with my package when I deploy it to run at the SSIS server instance. Everytime that I try to deploy it (from my local development environment) to SSIS server, my package is not keeping its database user and password.
As the database user and password are the same one we dont need to use the XML setting to keep these data there.
So, does anyone know what could be happening with my package and/or my deployment?
Thank you,
Luis Antonio - Brazil
We would like to deploy SSIS packages as an ETL Tool to an appserver that does not have SQL Server 2005 installed. Is this possible, or does it HAVE to be executed on the server with SQL 2005 installed?
Kevin
Is it possible to deploy a SSIS package without building an Integration Services Catalog on the server?
View 4 Replies View RelatedI'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.
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 RelatedHi,
I am trying to deploy a DTSX package to 32-bit SQL Server 2005 in a 64-bit machine from my installer.
I am getting the following error in the SaveToSQLServer method:
The SaveToSQLServer method has encountered OLE DB Error code 0x80004005 (Communication Link Failure). The SQL statement that was issued has failed.
However I am not facing this problem when I try to deploy the DTSX package to a 32-bit SQL Server 2005 in a 32-bit machine.
Can anyone tell me why this happens and how this could be solved?
Thanks,
Sandhya
I built a SSIS(writing out to a flat file ) in 32 bit machine and it woks fine . But however when I deploy to the produciton server(64 bit) the SSIS writes out garbage data . After some research I found out that the problem with the 32 bit OS and 64 bit OS problem.What is my next step. Am I out of luck that now I will have to redesing the SSIS in 64 bit?
View 5 Replies View RelatedHi everyone,
Primary platform is 64 bit cluster.
How to move information allocated in SSIS variables from Data Flow to Control Flow layers??
We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again????
Thanks in advance and regards,
Hi there
I am fairly new to SSIS, I got my package running fine in my development environment with the connection managers (connecting to 3 databases on the same server, with username and password, not Integrated security) and all.
The problem comes in when i want to deploy my package or execute it outside of the development environment.
When I execute the .dtsx file in my project it brings up the Execute Package Utility and I just click Execute, thinking it should work, but not, any ideas??
So I read some of the forums and got looking into the configuration file, suppose that it does not carry the connection manager settings thru, but dam that still doesn't work for me?
The whole idea is that I will be executing the package from my C# code, so does my package have to be just on the server in a folder or does it have to be installed in the db? which is best??
Hope anyone can help me!!
Thanks
Jamie
Hi All,
I have created an SSIS package which will import data from EXCEL file to SQL server. I had assigned the excel file path using an expression which wil be a combination of the folder path where the excel file resides + file name + date+extension. I also assigned the connection string for the OLEDB using the Expression. I have also exported the Folder path and the eonnection string variables to the package configurations and i have created the deployment project, which contains the package, packagemanifest and the package config file.
Now i need to know the steps to install the package in the production server and the steps to schedule the job in the sql server.
any of you plz give me the link to find the steps or plz tel me the things that i have to do.
Thankz to all
I have developed a SSIS dts package, its running fine from my computer, but the problem is when I am deploying it on actual server then giving me error for connection
scenario,
development env: dev006
three servers: srv01 (64bit), srv02 (32bit), srv03 (32bit)
in development environment, same package is getting data from srv01 and pushing it to srv02 and srv03, but when i am deploying it on srv01 then it fails to establish connection with srv01. If I have a Business Intelligence Studio on 64bit then I will check it but that Studio is available for 64bit environments.
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
I have created a package using SSDT2012 studio and it is failed to deploy under sql server 2012 sp1.Data inserting into sharepoint from sql server 2008 r2 database.The error says:The package failed to load due to error 0xC0010014.
View 9 Replies View RelatedIs it possible to deploy or import an SSIS package developed in visual studio 2010 onto a 2008 R2 integration server?
package: developed in vs2010, SQL Server: SQL Server 2008 R2.Â
Trying to deploy the package onto the server.
Hi There,
In SSIS package development environment, I was able to connect to an oracle database and pull data into my sql server database. I installed the client tools for oracle and I put an entry into the tnsnames.ora and I was able to connect.
But in production environment, if I deploy the package on sql server, I was wondering if I had to do the same job of downloading the oracle client tools onto my production machine --which creates a tnsnames.ora file to it default location and then edit it with tthe tns entry-- or is there a better way to do this--avoiding the download?
Can somebody plzz help me ?
Thanks.