Am I right to be concerned about extending SSIS packages with custom objects? I accept custom objects can extend the functionality of SSIS and can reduce repetition & complexity within SSIS. Are there any guidelines as to when to use custom objects? What are the risks e.g. performance, maintainability etc? Where do I draw the line? ...
Is there any way to force a package to fail from a Send Mail Task? We have a package whose last step is to send a failure message using the Send Mail Task if certain criteria are met. Usually, we create another Script Task directly after the Mail Task which just fails the package using code: Dts.TaskResult = Dts.Results.Failure Is the...
Is there a way to query the current status (executing, idle, etc) and the last result (successfull, failed, etc), and the last run time for a specific job name? The end result I am looking for is being able to display this information in an internal web application for various SSIS packages. ...
Hello, I am currently working on a VB.NET desktop application that uses .mdb (Access) database files on the backend. The .mdb files are opened and edited in the VB.NET app. Once editing is completed, the users will need to import the data into our SQL Server database. This is an easy task, until you try to introduce the dynamic filepat...
Background I have a dimension table that has a single record for each day. Each record has a primary key so example data would be: Dimension Table --------------- --------------------------------- | ID | DateTime | --------------------------------- | 1083 | 04/10/2008 10:02:00 PM | --------------------------------- ...
I'm designing the Fact and Dimension tables for a data warehouse currently using SQL Server, SSIS, and SSAS. Will I get any real benefit from programming the relationships between the dimensions and the fact tables into SQL? Or am I better off just defining the relationships manually when it comes time to create the cubes? It seems to b...
We have been developing SSIS packages for about a year now, and have 2 major issues associated with the maintenance of these packages : 1) We are using version control, but trying to identify the changes between versions of packages is a nightmare - the results of a text diff between 2 versions of a package is basically unusable. 2) Fo...
I want to be able to invoke an SSIS package at will from a web application. I've found that I can do this successfully with sp_start_job when running on my local machine. However, when I publish to our test site, I get: The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema dbo' So I tried this USE...
I'm working on a data warehouse project and would like to know how to (preferably in a Derived Column component in a Data flow) strip the date piece off of a SQL datetime record. Once I have the datetime converted to just a time I am going to do a lookup on the time to find the related time record in a time dimension table. Can someone...
How do I schedule SSIS to automatically execute a package from a file at a given time every day? ...
Hopefully this is easy to explain, but I have a lookup transformation in one of my SSIS packages. I am using it to lookup the id for an emplouyee record in a dimension table. However my problem is that some of the source data has employee names in all capitals (ex: CHERRERA) and the comparison data im using is all lower case (ex: cherrer...
Hello, I am having a bit of a struggle with Expressions inside SSIS Derived Columns. My source field is a BOOLEAN data type. It's destination field is a SQL CHAR datatype. ** Note that I did not design either of the schemas. If I had my way, the data types would match up. Unfortunately, this is not my case! I found a great example of...
Am I the only person on this particular planet that is inconvenienced by the lack of Identity Insert, missing in all relevant SSIS Data Flow Transformation objects? Only the Bulk Insert Task supports Identity Insert, what about elsewhere in SSIS? Is there a technical change in SQL Server 2008 that makes this very, very limited support ...
I'm doing a bulk insert of a CSV file into SQL Server 2005, using an SSIS package (not built by me) I'm running SQL Profiler and see the insert statement as: insert bulk [dbo].[stage_dht]( ..... ) but there's no FROM clause in that statement, so I'm curious how is it getting it's data, and is this so-called fast load the best way to ...
As part of a bulk load of data from an external source the stageing table is defined with varchar(max) columns. The idea being that each column will be able to hold whatever it finds in the source CSV file, and that we'll validate the data (for type, size, percision etc) later. But I'm concerned that the varchar(max) column has a lot o...
I want users to upload a file via our website (ASP.NET), which then gets imported into SQL Server 2005 using SSIS. Our web app is separated into a web server (IIS) and a database server (SQL Server). The problem is that after the file has uploaded to the web server, you cannot directly call the SSIS package from the ASP.NET code, becaus...
In SSIS I have a MONEY column in my OLE DB source that is formatted as 0.00. I want to output this value to a flat file in exactly the same format. So far, I've found two solutions: Convert this to a string in my source and output a string to the file. Since this is a flat file, it doesn't care HOW I got to 0.00. It just uses this ...
My team uses Visual Studios 2008 to develop SSIS packages and we use Perforce as our source control system. When a user adds a file to a project, the project is automatically checked out WITHOUT checking to see if it is the current version. Is there a way to force Visual Studios to get the latest version of a file before it is checked ...
I thought I asked a similar question in http://stackoverflow.com/questions/462071/sql-order-by-and-left-outer-join-doesnt-have-correct-order, but this is slightly different. I am getting data from another SQL-Server 2005 database, using SSIS, and a standard data flow. The records that I get are in a different order than in the source t...
I've inherited an SSIS package which loads 500K rows (about 30 columns) into a staging table. It's been cooking now for about 120 minutes and it's not done --- this suggests it's running at less than 70 rows per second. I know that everybody's environment is different but I think this is a couple orders of magnitude off from "typical"....