Controlling SSIS Package From A Website And Including A COM Object In The Processing
May 22, 2008
Hello everyone,
This is a new level of complexity for me..
The Boss wants..
1. To control an SSIS package , start, check status, and emergency stop + rollback a package from a web page. Does anyone know of an example or good articles to start with.
2. I have one of the iterations of the data invoke and use a COM object (third party) It will value the items and change a field.
I can always do #2 as a second step but I need all the help I can get on #1.
Is there a way to control SSIS 2014 package execution schedule. I have 8 different packages that I have to schedule for this project and these packages need to be executed only when the data is available on the source for the package to grab. So the idea we are banking on is to create a Control table with package execution flag and when the flag is set to yes for a package that will be executed via SQL Server agent. How would I accomplish this?
Does anyone know or could point me in the right direction to show how to run a SSIS package in a website environment? For example clicking the button on the webpage and it executes the package.
I am facing problem while processing my SSIS package
The error is: "Invalid delimited data. Text qualifier must be followed by a column delimiter (except for the last column)."
The text qualifier is the double-quotes character “. Text fields are supposed to get a double quote at the beginning of the string and one at the end. The column delimiter is the upright bar or pipe character |.
Some of the descriptions in table column have double-quotes embedded within the text string. When SSIS encounters one of these embedded quotes, it thinks that is the end of the text string and expects to find the column delimiter character next. It doesn’t, because the " is embedded between other alphabetic characters, so it raises an error.
For example if my column value looks like Test"String"One
What old DTS doing: DTS make the embedded " into "". The resulting text string then would look like this: "Test ""strings"" one".
When running the same data into a database using DTS, DTS recognized automatically the "" in the string and changed it to " so in the target the string looked like this: test "string" one. No problem.
SSIS, however, does not allow and does not support doing this "doubling" of embedded " when you are using " as the text qualifier. So it exports the sample string above like this (I added the column delimiters as well): |"test "string" one"|.
We have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
We are in the process of trying to automate our production releases (what a concept ;-)
The database is SQL server 2005 All objects are being stored in VSS Using Nant and Cruise Control for the actual migrations. I have two directories - Create (for a brandnew database) and Change (db object changes)
In my 'Change' script, I do the following -
1 - Take backup of database 2 - Migrate objects from 'change' directory to production 3 - Script out all objects of database and save in the 'Create' directory
For the #3, I was hoping I could create an SSIS package that would script out all database objects and save them on the VSS server.
I'm new to SSIS and want to verify it's something that can be done before I start down that path. If anyone has any examples or references, it would be much appreciated.
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200) declare @object varchar(200) DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR select table_name,table_type from INFORMATION_SCHEMA.TABLES union Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%' union select name,'FN' from sys.objects where type_desc like '%FUNCTION' OPEN object_cursor FETCH NEXT FROM object_cursor INTO @name,@object IF @@FETCH_STATUS <> 0 PRINT ' <<None>>' WHILE @@FETCH_STATUS = 0 BEGIN if @object = 'BASE TABLE' begin exec ('drop table ' + @name) end else if @object = 'VIEW' begin exec ('drop view ' + @name) end else if @object = 'SPROC' begin exec ('drop procedure ' + @name) end else if @object = 'FN' begin exec ('drop function ' + @name) end FETCH NEXT FROM object_cursor INTO @name,@object END CLOSE object_cursor DEALLOCATE object_cursor
Hi everybody. I need help with the next topic. From a stored procedure in sqlserver, I need to call a com+ dll, this dll connect ot another database diferent to sqlserver y return this dll must return to stored procedure a recordser for ther processing.
I tried with sp_OACreate, sp_OAMethod, sp_OAGetProperty but I did not know how to process a recordset
(dt_dbtimestamp)(colum_name,1, 23) when I include the period and three digits for milliseconds the package fails if I leave it out it is successfull. I need to include milliseconds for my datawarehouse project. I tried many different ways and always with failure 01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,23) fails 01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,19) succeeds
I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?
In visual studio 2005, I create a new Integration Services Project. It tries to create the first package by default "Package.dtsx". The "Package.dtsx[Design]" tab displays
Microsoft Visual Studio is unable to load this document
Object reference not set to an instance of an object
I try to create new SSIS package or edit an existing one (from tutorial), I get the same error in the SSIS graphical user interface tab.
After using FTP to transfer my site to Dotster, I get an error upon running the site. Here are the source error and stack trace respectively: Line 5: void Application_Start(object sender, EventArgs e) {Line 6: SiteMap.SiteMapResolve += new SiteMapResolveEventHandler(AppendQueryString);Line 7: if (!Roles.RoleExists("Administrators")) Roles.CreateRole("Administrators");Line 8: if (!Roles.RoleExists("Friends")) Roles.CreateRole("Friends"); [SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] The host provider says I must create the database [on their server] using their on-line utility, Is this true? Or do I need to configure the server to allow remote connections? If the latter is true, how is it accomplished? Thanks in advance.... any help is appreciated.
I've been working with SSIS for some weeks now and there are some Items that I really miss compared to for example BO Data Integrator. One of these is an Administration Console for SSIS packages. These would list all the jobs currently available in the repository: should be on file system and SQL server and give the user the ability to aunch them manually (in case of a job fails or something like that...)
Another feature this admin console should have is give the user a status of the running packages much like in SSIS designer when debugging packages.
Now I'd like to develop this by myself but I can't find any information about the Namespaces, Classes and objects I need to develop such an application...
Does anyone have some valuable information concerning this matter?
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.
ETL Packages are getting failed sometimes(Package Execution Error). Eventhough executing ETL Package again from start, getting the same Error. But after Restarting Sql Service in BI Server, it is working fine. Whether it is the issue from Developer Code side or from server side.
I am having trouble trying to construct the following process in SSIS/SQL 2005:
1. Grab a set of unprocessed rows (ProcessDT = null) in an 'Action' table 2. For each of these rows, execute multiple stored procedures base on the action type If actiontype = 1, exec spAct1a @param1, @parm2 exec spAct1b @param1, @parm2, @param3, @param4 If actiontype = 2, exec spAct2a @param1, @parm2, @param3 exec spAct2b @param1, @parm2, @param3 etc.... 3. Update ProcessDT so it's not processed again 4. Repeat until all rows are processed
Note - all sp params are contained in additional columns in the Action table. Basically the Action table is a store for post-event processing of sorts but is order dependent, hence the row by row processing. And some of my servers are 2000 so Service Broker is not an option (yet).
I first attempted to do this totally within the control flow - using an ado recordset/foreach loop control, but I could not figure out how to run conditional process paths based on the ActionTypeID. I then tried to do this within the dataflow using on OLEDB data source, a conditional split, and an oledb command control which almost got me there - the problem being for each row I need to execute multiple sp's and it appears as if the oledb command only gives me one sp.
I am facing some problem's while using the FOR loop container to execute 7-10 packages in parallel.
The main package has 7 FOR loop containers say F1-F7.
Each FOR loop container has 2 task's
T1==> exec child package C1
T2==> exec delay task Delay1.
The idea is to run child packages c1-c7 in parallel ...delay for some time and then run again since there are in the FOR loop container.
I am facing someproblems.
1. The execution of tasks T1-T7 is not guranteed. This means SSIS picks up any 6 tasks of T1-T7 randomly to start with. 6 is the max it processes whereas i have more than that. Can i change this setting???
2. Its not guranteed that if say Task t1 of FOR loop F1 is executed the subsequent task for Delay within tat For loop would be executed next. Typically wat happens is it starts with T1-T6 (T7 onhold) and then exec the delay for T1-T5 and passes control to T7 without going into the delay for T6.This is not the intended execution.
What i want is exec T1-t7 ..delay for the next exec and start again.
We have a problem importing the flat file data using SSIS into sql database.
For some erroneous files the package is just hanging with out any activity (Disk Reads/sec, Disk Writes/sec, Page faults/sec, Workingset Memory counters are const when the package hung). The package is giving the following messages before it is hung.
The column data for column "ServerName" overflowed the disk I/O buffer. An error occurred while processing file "M:Tani1APS-PRXY-02APS-PRXY-02-ISALOG_20060621_FWS_001-2006062106.Log" on data row 1. The PrimeOutput method on component "Firewall Data" (6109) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. Thread "SourceThread0" has exited with error code 0xC0047038. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. Thread "WorkThread0" has exited with error code 0xC0047039.
Even though it says has SourceThread0 has exited, it package is not terminating itself. We just want to ignore the errorneous file and move on to other files.
I tried altering DefaultBufferMaxRows in between 1000-10000, DefaultBufferSize to 1 MB €“ 20 MB which did not work out. Can one of you suggest us any resolution/work around for this issue? This is really a burning issue in our project would really appreciate any help!
I've got a falrly simple task I'd like to achieve in SSIS without scripting if possible.
I have a table with three columns, Cust ID, Address and Group ID I want to update the Group ID with a counter that increments every time the Address changes.
So currently the table looks like
Cust ID Address Group ID 62 ABC 57 DEF 79 DEF 42 GHI 11 JKL 20 JKL
and I want to end up with
Cust ID Address Group ID 62 ABC 1 57 DEF 2 79 DEF 2 42 GHI 3 11 JKL 4 20 JKL 4
I have created a view over this table that counts and Groups by address. This looks like
Count Address 1 ABC 2 DEF 1 GHI 2 JKL
What I'm thinking of doing is reading the View in Address order and updating the table (joining on Address) with a counter that increments for every row of the View read.
What I'm not sure of is how to increment this counter without creating a script. One thought I had was to create a new table from the view, and add an Identity column somehow (is this feasible in SSIS?)
Still new to DM and SSIS...anyand all help is greatly appreciated!
In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.
That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.
I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.
Hello, I have a DTS package that copies data from a fixed width file and inserts it into a SQL Server 2000 table. These files are generated daily, and usually contain about 200k records.
Sometimes, the format of the file between two days can differ slightly. I can't tell if the problem lies with the length of the record string, or the string terminator. The record string is supposed to begin with 'D1'. One file (as viewed in Notepad) will contain the records each in its own row, and each row will start with 'D1'. Another file will show that the first record starts with 'D1', but then the next record does not begin on a new line. Instead the next record continues at the end of the previous record. In this case, 'D1' is preceded with an unrecognized character as if it to indicate a carriage return.
Anyway, in SSIS, when I configure the row width of the fixed width file in the flat file connection manager to 386, and the beginning of all the records are all on their own row, the data processes without a problem. When the records do not all begin on their own row, the data will process fine if I change the row width to 385.
I don't have to compensate for this in DTS. Between SSIS and DTS, both flat file connections are configured with a row delimiter of LF, and no text qualifier. Why will SSIS and DTS process these files differently?
Does anyone have a helpful link for using the partition processing data flow task in SSIS? I am trying to process a monthly partition from within my package and am getting the following error:
Error: 0xC113000A Errors in the high-level relational engine. Pipeline processing can only reference a single table in the data source view.
If anyone has used this before and could point me in the right direction, I would appreciate it.
I have an olap database "A" and SSIS package "P" which process all the dimensions and cubes in "A" olap database.
I created "A1" olap database copy of "A" and made copy of "P" SSIS package as "P1" I opened "P1" SSIS package and updated olap connection properties "Initial Catalog = A1". A1 is my new olap database.
When I run package "P1" guess what? it processed "A" olap database's cubes and dimensions. Try it, not in production because I did it in production.
I'm currently trying to pull data from a ProvideX database and replicate it in a collection of SQL Server tables. However, I'm having a heck of a time trying to convert some strange decimals stored by the ProvideX database. As an example of the data I'm trying to retrieve, I'll see something like [. 1] or [. 1] ([]'s are to show the bounds of the field). After analyzing the data, it seems the decimal in the field represents a 1,000 placeholder. Thus [. 1] really means 1, and [. 1] really means 10. Something like .100 would be 100. 6.500 would be 6500.
As you can imagine, the spaces are causing errors when trying to pull the data, and I can't for the life of me figure out to just pull it as a string, run a script to convert it to a correct number, and then save the transformed data into SQL Server. When running the import wizard, it seems I'm being forced to pull these columns as decimals. Currently I'm trying to just pull the data out "as is" and throw it in a raw file, to be processed out of SSIS. Obviously doing it all within SSIS would be ideal, but if that can't be done, I'll do whatever it takes. I should also say I'm new to SSIS packages, but not necessarily new to SQL Server or SQL in general.
1) How can I pull these columns as strings? If I try to change the Export columns in the source query data flow step, it gives me an error saying that I can't do that.
2) If I have to pull as decimals, how can I capture the row on error, process it, and send it back to the export? So far, when I get an error, I lose all information in the row to the right of and including the error field.
I appreciate any responses, as I'm kind of going in circles at this point. If this sort of thing has been discussed here prior, I apologize...I didn't find it in any searches I did. Please just point me in the right direction if you've dealt with this sort of problem before. It seems to me that it should be an easy thing to do. I'm just not finding any tutorials on it.
What are the general guildlines for choosing these settings, like paralle vs. sequential, different error configurations? The default selection for processing multiple dimensions is paralle and use default error configuration. But the default for processing multiple partitions is sequential. I cannot find anything helpful other than the definitions from the online help. TIA.
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
And there is a task (Execute SSIS package) in First package that calls the execution of second package.
I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."
As we are running first package by job, job runs successfully logging above error
The protection level of second package is set to "EncryptSensitiveWithUserKey"
All I get back is an error message of "Analysis Services Processing Task Error: A Connection cannot be made. Ensure the Server is running" The server is running, I can process the cube by connecting to the AS instance and right-click processing it.
I can process the cube by running the SSIS task inside of SSDT Just when I deploy the SSIS package (in Project mode) and then execute it do I get the error message.
SQL Server, SSAS, and SSIS processes are all running under the same account. SSAS is on a separate server from SSIS and SQL if that matters.
I have one task in which i have to collect lots of .txt file having ## delimiter my requirement is to convert the delimiter from ## to comma and save the new file with .dat extension in different folder.
I have done all required process and run the application which should flow like collect source .txt file do Script component processing and create new .dat file with processed data in Data Flow task, but in my Task the Source and Destination start on same time and process start after words which cause empty file or some time a.txt file data stored in b.dat file where as a.dat file is completely empty.
The process should flow in sequence but behavior is totally against the process, i am using Foreach Loop Container for pick up each file.
