I have a question regarding Raw Files. I am breaking a large package into more modular components for better processing and debugging.
The process will start with a preparatory dataflow that will create a Raw File(s). This Raw File will then be used as the source in possibly 6 data flows and/or packages.
My question is whether 1 Raw File can be read concurrently by the multiple jobs and how this would affect processing. I'm assuming that this would slow processing.
My other option is to Multicast the writing of the Raw File to 5 other versions of the file. All would be identical except for filename. Obviously this would use more disk space but this is not a concern as we have lots of disk space. Our concern is for speedy processing.
If you have experience with Raw Files, please let me know how you approached this issue. As always, blogs and specific examples are always great!
I would like to use one configuration file for multiple SSIS packages. How I want to do this is to save the connection string of my production server with the initial database field excluded and set that per package file. I am able to read in the configuration file into 2 different packages without issue but I cannot see how to extract the connection string into a ODBC Database Connection object in order to set the initial database. Is there some way I am not thinking of to do this, either through the designer or programatically?
Thanks for any help you all can provide! I am so n00b to SSIS.
Basically it seems that whilst you can indeed share a config file, it doesn't aggregate sets of say different connection managers, into a superset - you choose to reuse, but it actually overwrites. Any ideas, anyone??
I have several packages that all use the same DTSConfig file to map the OLEDB connections. This works fine in the development environment, however when i run a rebuild with Deployment set to true i get the error
Error 75 System.ApplicationException: Could not copy file "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransformPackagePath.dtsConfig" to the deployment utility output directory "C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeployment". ---> System.IO.IOException: The file 'C:Dev_TFSTowergateTowergate.BdwTowergate.Bdw.Ssis.TransforminDeploymentPackagePath.dtsConfig' already exists. at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) --- End of inner exception stack trace --- at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CopyFiles(ICollection fileNames, String outputPath) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.CreateDeploymentUtility(IOutputWindow outputWindow)
The DTSConfig file is included in the project under Misc in the development studio as all developers need to have access to the same config file.
The net result is that the MSI deployment package doesnt get built so we cannot do an automated delivery of the data.
Is there a work round or a setting that i can implement to remove the error?
My current environment has multiple packages stored in SQL server (MSDB). When working on a set of packages I want to bring them into my local development area Add existing package only allows you to pull one package at a time - anyone have the secret to selecting multiples
I've read that a single config file can be used across multiple packages, but am unsure if it applies to what I'm trying to achieve. As an example, I have 2 packages, PackageA and PackageB. Each package has a connection manager defined - ManagerA and ManagerB and I want a single package configuration file to contain the connection string for each manager.
I can define the package configuration of PackageA with no problems. I choose XML file, define the filename and add the connection string to the config.
I then open PackageB Package Configuration and point it to the existing config file, IS tells me the file already exists and allows me to choose the option to "Reuse Existing". I then supply a configuration name and the wizard finishes, without letting me expose any properties. At this point, the configuration file still contains config details from PackageA.
If I then choose to Edit the package configuration in PackageB, I get a warning saying the config values in the config file (relating to PackageA) cannot be found, and the wizard continues. The subsequent PackageB properties which I add overwrite the existing PackageA properties.
So it would appear that what I'm trying to do cannot be done, and I need a separate configuration file for each package?
I am currently migrating from Oracle to MS SQL Server 2005 using SSIS. Since the new schema being used on the SQL Server is very different, we have created separate packages for each target table.
We have several different sets of data stored as separate Oracle schemas, and I would now like to change to a different schema for the OLE DB Source objects in my packages to use (Table/column names to remain the same). While I can go through all packages and search through the drop-down list to select the table of the same name from other Schema, doing so is an extremely slow process.
Is it possible to use configuration files to set a schema to be used for a number of OLE DB Sources over a number of packages?
At the moment I am doing a find and replace over the .dtsx files, but can't believe I have to resort to this for something that I imagine many people would want to do with SSIS.
I am working on a project currently where we have many SSIS packages and we want to minimize the number of config files to be used. What I was thinking was using one master config file which will have Server/Database info which will be used by all the packages. Now the thing is there are multiple packages which are being executed from within a master package and I was wondering if this will be an issue. Is there anything else that I will have to keep in mind? Any help is appreciated.
This seems like a no brainer, but it's driving me nuts. I want one XML file for the entire solution. There are multiple packages in the solution which have different Connections in the conntion Managers. The packages share some connection names, and some are unique to the package.
Example:
LoadData.dtsx would have a source database connection named (SourceDB_OLEDB) and a oledb connection (DataWarehouse_OLEDB).
LoadDataMart.dtsx would use the same name for the (DataWarehouse_OLEDB) connection and have another oledb connection (DataMart_OLEDB)
I want one XML config file that has all the connection strings, but the problem is that the LoadDataMart.dtsx will throw an error:
Error 1 Error loading LoadDataMart.dtsx: The connection "SourceDB_OLEDB" is not found. This error is thrown by Connections collection when the specific connection element is not found. c:ssisLoadDataMart.dtsx 1 1
I have a problem while creating a dynamic source connection.
i have four files while which comes with different extension , apparently the name of the files are same for example.
9500.txt, the other one is 9500. rtf, 9500.dat, 9500.map, They are all text file but with different formats. I have problem selecting a dynamic source connection for each of this files.
when i create variable for input file name the file connection gets confused and throws me error with input file.
There is a small problem encountered while creating a package in sql server 2005. Actually i am using a flat file which has 820 rows and 2 columns which are seperated by line feed(for ROW) and tab(for COLUMN).after importing i found that ther are only 800 rows imported into the table. Ather verifying the input file i found out that there are some null values in the second column so there is no line feed for those values. Can anyone please help me how to give multiple delimiters for the same input flat file.
I am working in one company and currently I am assigned to new project for Data Migration from company X to our company Y using SSIS. I am totally new and i just completed 5 tutorial which was gien on MSDN website.
Basically client is going to send us first flat file with 1 million records with Header, Detail and Trailer records. I want to create a Package in such a way that it dumps all this first load into 7 to 8 different tables at a time. we also have to include functionlity for validation and error check. On successfull load error file should only return Header and Trailer but no detail records. If there are any errors then error file should contain Header, Detail records which were unable to load plus trailer which we have to sent back to client.
When 2nd file comes that time we have to check whether this is new records or change (update) one depending on Flag which tells it.
This is basically high level idea of my Package what i need to create. If u guys have any question then let me know.
I know you guys are very experienced one. Anyone of you please give me some detail idea on it I would really appricate it. I have very limited time line for it.
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Is there any table where we have source connection and destination connection information about the DTS packages? None of these tables in the msdb have this information.
doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.
1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries
When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.
The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.
How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?
I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
I have several independant DTS packages that I would like to schedule and run as 1 job stream. (sql2000) I can schedule them individually, but I would prefer to have each one be a step of 1 big scheduled job.
I have a schedule job that errors out on the first step when I attempt to run it. There are 3 packages that I created and I can run them all manually just fine.
I get this error when I try to run the job: -----------------------------------------------
An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80... The package execution fa... The step failed.,00:00:01,0,0,,,,0
I have a rather dumb question, it appears that we can have more than 1 package (.dtsx) in a solution. If I have multiple packages within the same solution, how do I invoke them from the main package in the solution? Thanks in Advance.
I'm having serious problems with the IDE for SSIS for projects that contain more than 5 packages. Especially if these packages call each other with a run package task thats configured with a file connection. Especially annoying are the 20+ "Document contains one or more extremely long lines of text.." messages that pop up during loading / validating. For my project with around 30 packages it takes me around 10 minutes to click through all these pop ups in addition to the long loading time.
Anyone got any tips on this specifically or how to improve performance in the IDE in general? As it is now, the product is a REAL pain to work with for large projects.
We have a folder in SSIS called ETL and there are about 25 SSIS packages in it. Now we need to update it. I tried to delete the folder but you get the message that the folder is not empty. So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work. Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes. But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
Hi Guys and Gals. Thanx in advance for any help or input. I have multiple DTS Packages that run each nite on a Server. The packages import data from SQL Tables on another Server. All of a sudden, the Jobs have started failing. There is nothing in the SQL Logs. The DTS Package Logs (that I save to the Server) specify an unspecified DTS error. Any suggestions? Even better - any suggestions what/where to look to find the cause of these errors? I re-run the Jobs in the morning when I get in and they complete without any problems. If you need more information just let me know. Thanx. billy
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package, I am getting the below error: Error: Error 0xC001000A while preparing to load the package. The specified package could not be loaded from the SQL Server database. .
I built 3 diffrent packages and i want to be executed in order. The first one is exporting some tables to another database and the other two packages are based on the database built in the first package.
I am trying to execute around 3 SSIS packages using Execute package task by having all the 3 in one SSIS package, I am getting the below error: Error: Error 0xC001000A while preparing to load the package. The specified package could not be loaded from the SQL Server database. . Then I did the following: 1. Right click on MSDB 2. Import package Inside Import package window: Package location: SQL Server Server: servername authentication: windows authen Package path: \serverC$folder1SSIS Packagesfolder2 Import package as: Package name: package.dtsx Below is the error I am getting: TITLE: Import Package ------------------------------ Cannot find folder "\serverC$folder1SSIS Packagesfolder2". Can I use Execute package task for this purpose?? Thanks!!
I have created several DTS Packages those are working fine, tested and verified. I need to run them by a single click, either by batchfile or some sort of command.
its my flow in one of my packates (ETL job) Excel file contains monthly revenue details, i wanna import the excel data to my database staging table, so i've created the package. its working fine...
Problem if we change the new data for the next month and running the package its not running; the same file, same format, only we delete the contents, of the file except first row of the excel sheet, and pasting the new data; new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us)
i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized Do you want to Fix this issue automatically with the available external column's meta data
Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to.
now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the package Execution wizard,
ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the asp.net webpage, that is executing the package in an On_click event)
The package is not running from the server, its due to the meta data change in the Excel file( i guess)
please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta data should not change when we have new updates in it;
otherwise suggest me some solutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kind of Data Profiling activity;
i know its some what crazy, but i need to maintain the system with permanent solution, instead of facing this meta data mismatch issue!!!
some what lenthy explanation--> its needed for my dear powerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries, i'll try my level best.
I have 200+ plus packages that need to be flexible in how they are run. For example, an end user may choose to run packages 1,2,3 and the next end user may choose to run packages 2,3,7, etc. Prior ro running a package, I set an "instance id" inside the group of packages so I can tie them all together in the logfile - I know that packages 1,2,3 were all run as group and that's distinct from packages 2,3,7 that were run in a differnt group.
Initially I embarked on a scenario where I had a queue table that loaded up the packages to be run and then had a little c# app that read the queue, generated the "instance id" and ran all the packages (either thru dtexec.exe or the Microsoft.SqlServer.DTS.Runtime). But now I wonder if using a master package that uses the Execute Package Task is the way to go. My 200+ packages are all independent and run based on a single config file and it seems as though going the parent package route will destroy some of that independence because I'll now be relying on parent package variables.
