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
I am looking for solution for "Communication link failure" Â since many months in google but no luck, am running an SSIS package to load data. job failing many times with error 'Communication link failure', searched every where but found nothing.
Below is the complete error description when job failed.
OS - Windows server 2008 R2 Enterprise Edition
RAM - 198GB
 SQL server 2008 R2 Enterprise Edition and error description is below,
Started: Â 6:22:40 AM Â Error: 2015-08-19 18:50:32.70 Â Â Code: 0xC0202009 Â Â Â
Source: Data Flow Task Lookup [193] Â Â Â
Description: SSIS Error Code DTS_E_OLEDBERROR. Â An OLE DB error has occurred.Â
Error code: 0x80004005. Â An OLE DB record is available. Â
[Code] ....
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.
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.
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 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 all,
I am wondering if you guys have any experience with failing Stored Procedures running inside a SSIS package with the following error:
=====================================================
Message
Executed as user: GAALPSVR034FSYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:51:22 AM Error: 2008-05-19 09:06:55.15 Code: 0x00000000 Source: usp_SLIM_Site_PreProcess Description: TCP Provider: The specified network name is no longer available. End Error Error: 2008-05-19 09:06:55.18 Code: 0xC002F210 Source: usp_SLIM_Site_PreProcess Execute SQL Task Description: Executing the query "Exec usp_SLIM_Site_PreProcess" failed with the following error: "Communication link failure". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:51:22 AM Finished: 9:06:55 AM Elapsed: 932.203 seconds. The package execution failed. The step failed.
======================================================
Sometime it fails when gets called through a job and sometimes even when a package is open in a design mode. The actual Stored Procedure NEVER fails if called in SQL Server Management Studio.
Any ideas or suggestions will be very helpful and appreciated
Thanks for your help!
Jacob
Run the SSIS import/export wizard.
(xls -> sql table)
Select the tasks created.
Copy.
Here is the error that occurs.
p.s. Does anyone have any needles I can borrow? I think sticking them in my eyes would be nicer than working with SSIS.
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'Preparation SQL Task' to the clipboard.
(Microsoft.DataTransformationServices.Design)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
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 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
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?
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!
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.
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
Hello everybody,
I need some help :
I want to transfer a few access mdb kept in a folder to sql server. I am using foreach file enumerator. I want to perform the following actions :
1. At time of loading the file, check if the file name starts with "ED". If not, move the file to error folder.
2. Count no. of records that were there in the access db and that were transferred to the sql table.
3. I also want to run the package from command line. Even though i figured out that, i want to display only limited messages on the console. For eg: File processing begins, File currently being processed, TOtal rows transferred, File processing completed etc.
Please guide me with some examples.
thanks mint.
Hi,
I need some suggesstion regarding which SSIS tasks to use regarding my job in hand. Basically what I need is to extract data from joining multiple tables and then load to a particular output database.
How I can go about it?
Thanks
Hi,
Can anyone help me out on the following tasks with examples
XML Task.
Message queue Task.
Send Mail Task
Thanks in Advance.
Regards
su..........
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 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
What are peoples opinions on using SSIS as a "central repository" and replacement for all scheduled tasks. Example, we have a bunch of servers which we have installed services which we have written. Currently we have scheduled tasks on each machine to stop and start the services. One of my collegues is using SSIS to run a system which runs tasks on multiple machines by remotely running programs on other machines via scheduled tasks and then collects the data and puts it into a database.
He's now pitching the idea that we remove the scheduled tasks on each machine and start and stop our services via SSIS so that it's centralized. In addition, we can also check for holidays in our database before starting services. Since it doesn't seem like SSIS was meant for this type of use, I'm weary of using the tool to do something it wasn't intended for.
Any opinions? I'm also worried that the learnnig curve for everyone is going to be too high.
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.
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure:
CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the
),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
Hi
I have a strange problem with SSIS packages.
(brief description - packages select some data from DBs , write them to CSV file and then CSV file is
copied and renamed to a folder made up of the date)
I have 5 packages scheduled to run, these jobs run perfectly when test scheduled during the day,
(so its not a user permissions problem).
However it seems the 1st package to run at night will fail.
The reason I say 1st is the following,
I had Package A scheduled at 11:20PM and package B at 11:30PM , Package B always succeeded
but package A always failed.
I would test A during the day and it would run fine (the jobs would run successfully aswell as just executing the package manually) .
The I changed the time with B to 11:50PM and it succeeds and A fails ! without changing the packages themselves.
This counts out a possibilty of a DB backup causing the problem (pack always succeeded at 1130, now fails at the same
time)
I was thinking maybe as the folder wasn't created when the 1st pack ran this was causing the fail, but when i test run the job
this morning it succeeds .. and todays folder doesn't exist either !
any ideas ?
I'm not exactly sure how to ask this question ... but here goes!!
I want to get an idea of how SSIS actually executes transformation tasks.
Do transformation tasks (eg a lookup) complie down to managed code or are the executed as SQL commands in a SQL server database?
Thanks.
Hi
I have migrated a DTS that had some activeX transformation tasks within data pump flow tasks.
Those parts were migrated as "DTS 2000 tasks" .. so activeX transformation tasks aren't possible in SSIS ?
I know ActiveX script tasks are but for transformations ?
1. IF i leave these Encapsulated DTS 2000 tasks in the migrated SSIS package, will it run independently of the original
DTS or does it need the old DTS running to "call" that part from ? (I hope im making sense here)
is it possible to load this functionality internally into the new SSIS ?
2.
How could I (if i can't do ActiveX transformation tasks) achieve this is SSIS ?
can I achive this using the script tasks in SSIS ?
HI All,
I have Uzip Files to be downloaded From ftp.companyname.com, the zip files get updated everyday thus i have to download the newly added files, the Zip file has got 13 text Files within it, the issue is:
1. how do i download it for the new zip file only, the zip files are shown below, i am trying using FTP Task, but need more info or other alternative.
2. (Optional) How do i UnZip it and and take the text files and then load them to sql server 2005, each text file has to be loaded to sql server tables.
3. How do i automate it, i mean every time i run the package (on Job based) it has to look the new file only, see the zip file below to understand what i am saying about.
02/25/2008 09:02PM 2,780,729 CompanyName_2008-02-22.zip
02/27/2008 09:04PM 1,274,557 CompanyName_2008-02-23.zip
02/25/2008 08:57PM 1,383,112 CompanyName_2008-02-24.zip
02/26/2008 04:21PM 3,327,882 CompanyName_2008-02-25.zip
02/27/2008 08:44PM 3,623,334 CompanyName_2008-02-26.zip
02/28/2008 05:27PM 3,570,243 CompanyName_2008-02-27.zip
02/29/2008 04:20PM 3,444,375 CompanyName_2008-02-28.zip
03/01/2008 04:17PM 2,655,782 CompanyName_2008-02-29.zip
03/03/2008 04:09AM 1,179,338 CompanyName_2008-03-01.zip
03/03/2008 05:21PM 1,267,777 CompanyName_2008-03-02.zip
03/04/2008 04:49PM 3,238,121 CompanyName_2008-03-03.zip
As you can see the Zip file names are the blues color, they are added at different time, when you browse the ftp.companyname.com you will get the blue color files, thus what i need is to download only the current Zip file (i mean i have to downLoad only the newly added Zip file (recent one)). Thus the SSIS Task has to go to this FTP server and look the newly added zip file. (Optional) if possible, After that i have Unzip it b/se i have 13 text files there, and then Load them to Sql Server 2005.
Please get help on this, the deadline is near by time, if possible try as soon as possible, I would like to say Thank you for every help you do and try.
Thanks,
I can't find information on how you are suppose to handle the TransactionOption setting inside of a custom task.
I have a custom task that I have developed, and it basically calls a COM+ object that writes data to a database. When I have the task inside of a container that has the transactionoption set to required, and my custom task is set to supported, if one of the X items fail to execute in my custom task I am telling my task to fail the parent, which I thought would rollback everything. But it does not.
Is there someplace that I need to write rollback code in custom SSIS tasks? If there is I can't find any mention of it anywhere. Any examples out there on how to build custom SSIS tasks that support the TransactionOption parameter?
Thanks!
Matt