Hello experts, I have an exe configured under windows scheduler to perform timely operations on a set of data. The exe calls stored procs to retrieve data and perform some calcualtions and updates the data back to a different database. I would like to know, what are the pros and cons of using SSIS package over scheduled exe. Thanks ...
Hi, i'm running a sql server replication to sync a table to a separate (reporting) database. In the source database, all records have a DateTime value (not so unusual) but it isn't stored as a DateTime, but as a Guid... which points to a table which contains all timestamps (one for every minute). Now what i would like to do is during ...
Our shop relies heavily on SSIS to run our back end processes and database tasks. Overall, we have hundreds of jobs, and for the most part, everything runs efficiently and smoothly. Most of the time, we have a job failure due to an external dependency failing (data not available, files not delivered, etc). Right now, our process is set ...
SSIS package loops through input files. For each file, flatfile parse adds records to a DB table, then file is renames/moved for archiving. After all files, package calls a sproc to delete all year-old records. Package runs from visual studio OK. Put in SSIS package store, run from there, no problem. Create an SQL Agent job to run p...
I have SQL Server 2005 and I am trying to export a table into an Excel file. I right click on my database and click export. I go through the wizard and choose to export to excel then I choose my one table that I want to export and hit finished. I get this Messages Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E...
I'm working for a company running a software product based on a MS SQL database server, and through the years I have developed 20-30 quite advanced reports in PHP, taking data directly from the database. This has been very successful, and people are happy with it. But it has some drawbacks: For new changes, it can be quite development...
I'm working on an ssis package to fix some data from a table. The table looks something like this: CustID FieldID INT_VAL DEC_VAL VARCHAR_VAL DATE_VAL 1 1 23 1 2 500.0 1 3 David 1 4 4/1/05 1 5 52369871 2 1 25 2 2 ...
hi, i created a SSIS package on my development machine which simply: 1) Deletes records from weak table in database. 2) Loads file from filesystem into weak table in database. 3) The package is called from an asp.net page When i run the package manually on my machine it works, when the protection level is EncryptSensitiveWithUserKey. W...
I need to migrate a database from Postgres 7 to SQL Server 2008. I am familiar with the SSIS Import and Export wizard but I am stumped about how to define the data source or define the data provider. What is the best way to migrate Postgres to SQL Server, and how do I define data sources/drivers for postgres? ...
I need to parse a date like "22/06/2010 3:21:31 p.m." in SSIS in a data flow. Cast(DT_DBTIMESTAMP ) does not work at this is does not handle this format. What is the easiest way that I can do this? ...
I know its possible to generate the database tables from the domain model. But is there any way of doing things the other way. I have a totally awful database (worst I have ever seen). Its sharded (16 Shards!!), split across multiple postgres databases (all on the same server) with foreign key relations like urn:dbtable:guid. Its pro...
In my SSIS Package, I am using an XML file that was downloaded by another process. Before I proceed to other portions of work in the package, I want to ascertain that the file downloaded completely. I have the XSD file handy as well. How do I verify in SSIS that the XML file downloaded properly? ...
I'm trying to export some information from SQL Server. The SQL runs fine in the SQL Management Studio, but when I try to export it gives me the following errors. I've had issues similar to this before and adding the SET ANSI_WARNINGS OFF line fixed it. That line is not working this time. EDIT: I've tried with the SQL SELECT * FROM Ta...
What are the requirements for running an SSIS package from an asp.net web site? ...
I have this URL where there is XML data. I have to extract that data from URL and dump it into DW table. I am using SSIS Script Task for that. This is how the data looks like: -<currency> <csymbol>AED</csymbol> <cname>United Arab Emirates Dirhams</cname> <crate>3.6732001305</crate> <cinverse>0.2722421770</cinverse> </currency> −...
I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell: declare @cmd varchar(5000) set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;c:\foo.xlsx' print @cmd exec xp_cmdshell @cmd This works fine, ho...
I went for a job interview this morning, and one of the scenarios that the IT manager (a former programmer) told me about was the following .... If anyone could give me any pointers that I can email him pointers to tomorrow (Wednesday 22nd) it may just help tip his decision in my direction. So there's beer in it if you answer a relevent ...
Hi there I am using SQL Server 2005 SSIS and we are using the Data Flow Task to move data from one table to another. This works well. Now we have another requirement to do data update from the same table using this approach. Is this possible to use the same approach for as follow: *) We have a dataset from Table A based on complex que...
Hi Everyone, I have created SSIS packages to move data from AS400 to SQL Server which are scheduled daily.some of the packages in sql agent are taking longer duration more than 9 hours to complete.IF I run same package in Business intelligence studio manually, it is completing in less than 4 hours.Due to this problem my schedule packages...
I'm trying to move data from an Oracle (10g) database to SQL-Server (2008). I also want the indexes to be re-created on the SQL-Server side. However, in Oracle, there is a primary key defined on the first two fields, and it has data like this: VALUE3 FOO4 VALUE4 FOO8 Value4 Foo8 When I get that data to SQL Server, it won't mak...