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
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 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,
Can anyone help me out on the following tasks with examples
XML Task.
Message queue Task.
Send Mail Task
Thanks in Advance.
Regards
su..........
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.
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.
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.
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 ?
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'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'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
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
I am finding difficult to set Expression Property "SelectedDatabases" in Check Databases Integrity Task in SSIS.
I keep getting error:
TITLE: Expression Builder
------------------------------
Expression cannot be evaluated.
ADDITIONAL INFORMATION:
The data type of variable "User::varDataset" is not supported in an expression.
Reading the variable "User::varDataset" failed with error code 0xC00470D0.
(Microsoft.DataTransformationServices.Controls)
I need the flexibilty to be able to control the Task behaviour during runtime through Variables.
Any suggestions .... or even a different approach will be helpful..
cheers
aigbor
Hello -- I primarily use SSIS for data loading tasks from falt files into SQL Server DB.
I usually have the Loading Fallouts routed to a FALLOUT Table with SSIS Err Codes.
However, the package execution stops if there are a bunch of fallouts.. like more than 20/30..
Is there any place in the package where i can specify a bigger Number, rather than Err'ng out for more than 20/30??
Thank You!
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.
Hi,
I have a SQL 2000 script which I use to automatically schedule various backup tasks. This script adds and schedules a full backup once a week, differential backups nightly, and log backups hourly, in addition to a couple other maintenance tasks such as rebuilding indexes.
The idea is that a less technically savvy person can set a few variables at the top of the script (such as DB name and backup file folders) and click 'execute' to run the script and schedule the backups etc all in one go, for different clients.
Since some of the stored procs I use in this script are deprecated in SQL 2005, I am trying to replicate this functionality in SSIS but am having trouble figuring out how I can get all of this functionality encapsulated in the same 'click and go' manner where the user can simply execute the package and all the jobs will be scheduled without any user interaction.
Is this even possible? Where should I be looking for examples of how to do this?
Thanks!
Hi,
I'm facing a problem with my SSIS package regarding the execution order of tasks. I am using my package for the purpose of loading data from XML to staging tables in the database, and have a loop to process all XML files.
As a precondition to the loading action itself, I am running a stored procedure against the database (using the ExecuteSQL task) to check whether all staging tables are empty. The output parameter of that stored procedure is mapped to a variable I have defined in the SSIS package, so I can use it as a basis for the decision whether to run the loading action or not.
In order to test my package I added a script task, right after the execution of the stored procedure with a precedence constraint, that pops up a messagebox with the value of the stored procedure return value stored in the package variable.
The problem is that for the first iteration of the loop, the variable value presented by the messagebox is incorrect (equals to the initial value I assigned to the variable), and it looks like the script task starts before the stored procedure execution task finishes and its output value is stored in the package variable.
Please assist.
Hey guys,
I created a script task in my package that checks for filenames of files that are to be processed by the package.
for example, File.txt contains a header row of "File.txt|03252006"
what my script should do is to check whether or not the actual filename matches the header record inside..
If File.txt has a header record of "readme.txt|03252006" then the script should raise an alert or notification but not fail the package..
I am lost here.. I have no idea how to access the SSIS package's events/alerts from within my script task..
my code looks something like this:
'Get Filename from File Connection String
Dim strFile_Name As String = strFile_Conn.Substring(strFile_Conn.LastIndexOf("") + 1, strFile_Conn.Length - (strFile_Conn.LastIndexOf("") + 1))
'Get Filename from header
Dim ReadHeader As StreamReader = New StreamReader(strHeader)
Dim HeaderFileName As String = ReadHeader.ReadLine()
ReadHeader.Close()
HeaderFileName = HeaderFileName.Substring(0, HeaderFileName.IndexOf("|"))
If strFile_Name <> HeaderFileName Then
<I don't know what to put here I should raise an alert or notification that the file identification failed.>
End If
Thanks in advance guys, I hope someone would be able to help me
Kervy
I want to delete all files in a given folder that are older than 3 days from today's date.
I tried using a "Foreach Loop container" with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?
TIA,
Barkingdog
P.S. History of the problem:
I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep! I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.
Hi
I have a script component task that uses a column (which contains filenames) as input,
my problem is i cant access this in the script ? the column name is "Document"
and i've tried Me.ProcessInput("Document")
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
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
I want to get the start time of data load and end time after data load and store it in a table which has mapping_id , mapping_name,start_time,end_time.
i use ActiveXScript task to get the start time before data load and store the mapping_id in a global variable,then data flow transformation occurs.
i want to use a global variable to store the mapping id ,so that i can update the end time after data load with that variable.how to do this?
is there any other way, i can get the start and end time of data load (other than the logging information)?
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.
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.
Anybody got any idea on how to go about this?
are there restrictions on what a DBA can do on a developer's edition?
i'm asking coz i get really bad connection problems when changing service accounts, client aliases, port numbers, connecting to different components like ssis, reporting services, database engine, etc...
in sql2000, changing these things are a breeze, is there some guidelines when making these changes in 2005?
TIA
--------------------
keeping it simple...
Hi guys
Just want to know whether you guys do the following as a dba:
1. Setup a logging for tracking database as well as table size that containing size, indexing size. You can measure the growth.
2. Record for indexing for each database. I think this is over kill task.
3. Record database setup such as create statistic, update statistic etc.
These tasks can be automate to record every month for example and record this into Administration database for instance.
What else you guys record?
Thanks
If I'm in the Data Flow tab in VS 2005, how can I select only certain components to run to test? I tried highlighting the ones that I want to run but it's running all of them in the tab...some of the components I want to take out for testing then maybe put back in later. If I delete the tasks I don't want to run, then I end up having to recreate them
View 11 Replies View RelatedWhere to look for the web assitant created jobs on the server?I created a web page using the web assistant which is suppossed to get updated each time a value changes in a particular table,but I can't find the task which I created ,where do i look for that?I didn't use this wizard previously.thanks for any help!
Sheila.