ssis

How to backup SQL Server Agent jobs?

How can I backup and restore SQL Server 2005 Agent job schedules? ...

SQL Server: Find out what row caused the TSQL to fail (SSIS)

SQL Server 2005 Question: I'm working on a data conversion project where I'm taking 80k+ rows and moving them from one table to another. When I run the TSQL, it bombs with various errors having to do with converting types, or whatever. Is there a way to find out what row caused the error? ===================== UPDATE: I'm performing...

SSIS Conditional Split with 2 cases, case 2 should only run on case 1's successful completion.

I have a SSIS Data Flow Task with an OleDb Source, Conditional Split (w/ 2 cases), and 2 OleDb Destinations. Like so... ---------------------------- +--> | Case1: OleDb Destination | ---------------- --------------------- | ...

Programmatically add an SSIS ForEachLoop

How do you programmatically add an SSIS ForEachLoop? We are limited to SQL Server 2005. I'm having trouble adding the following properties: ForEachEnumerator CollectionEnumerator Directory FileNameRetrieval FileSpec Recurse My code format looks like this: Microsoft.SqlServer.Dts.Runtime.Executable executable = aSequence[0].Executable...

SSIS Update Table Values from Another Table

I want to update TableA with values from TableB on a nightly basis. Right now, I'm trying to do this with SSIS 2005 using a Script Task with the SQL in it. Each time I try to do the update in this manner, I get a time-out error. Is there a better way to do this in SSIS? Current information below: Public Sub Main() Const Compone...

What permissions needed to connect to SQL Server Integration Services

I need to allow a consultant to connect to SSIS on a SQL Server 2008 box without making him a local administrator. If I add him to the local administrators group, he can connect to SSIS just fine, but it seems that I can't grant him enough permissions through SQL Server to give him these rights without being a local admin. I've added hi...

SSIS Null Value Questions

I have a table with 5 string columns, all can be NULLs. After I read the data from this table, I want to convert any null values into empty strings. The reason is that I need to compare these columns with columns in another table of the same schema (using conditional split), and null values would cause the comparison to evaluate to NULL....

Dynamic query in SSIS Package

I have an OLE DB Command component in an SSIS Package that runs an update statement against a SQL table. Now, my statement initially is like: update myTable set columnA=?, columnB=? where columnC=? Where the three "?"'s are inputs to my component from another Conditional Split component. I can link each column name with each input par...

SSIS Package tasks execution detail

We have deployed SSIS packages in one of our Prod Server using source server as another Prod DB server, in this job I have many packages (almost 10 packages) with many tasks and sub tasks. The job is executing successfully and we can find the each package execution start time and end time using job steps. Now we would like to know ea...

How to determine where, or if, a variable is used in an SSIS package

I've inherited a collection of largely undocumented ssis packages. The entry point package (ie: the one that forks off in a variety of directions to call other packages) defines a number of variables. I would like to know how these variables are being used, but there doesn't seem to be an equivalent of "right click/Find All References"...

Integration services and identity columns

I am a bit of an SSIS newbie and while the whole system seems straightforward, I don't conceptually understand the process I need to go through in this scenario: Need to map Invoice and InvoiceLine tables from a source database to two equivalent tables in a destination database - with different identity values. For each invoice inserte...

SSIS SELECT VALUE from a table without a lookup

I'm fairly new to SSIS, I'm importing from an XLS spreadsheet into a database table. Along the way I want to select a record from a table, but it is NOT a lookup, ie: a straight SELECT with no join from input source. Then I want to merge this along with the other rows from the XLS. What is the best way to do this? Variables? OLE DB...

Oracle Transaction under MS SSIS

Hi, I'm trying to get transactions working under SSIS (SQL Server Integration Services 2005) and Oracle with no luck. I'm using Oracle 10g. I have created a Sequence Container, set its TransactionOption to "Required" and put inside it a DataFlow Task with TransactionOption = "Supported". The server running SSIS has the MSDTC service ru...

UPSERT in SSIS

I am writing an SSIS package to run on SQL Server 2008. How do you do an UPSERT in SSIS? IF KEY NOT EXISTS INSERT ELSE IF DATA CHANGED UPDATE ENDIF ENDIF ...

Import Xml nodes as Xml column with SSIS

I'm trying to use the Xml Source to shred an XML source file however I do not want the entire document shredded into tables. Rather I want to import the xml Nodes into rows of Xml. a simplified example would be to import the document below into a table called "people" with a column called "person" of type "xml". When looking at the Xm...

What are the differences between File System and SQL Server deployments for SSIS packages?

I have an SSIS package that needs to be deployed to SQL Server agent. It has 2 external dependencies (2 assemblies, both installed in the GAC) Now the package runs just fine under a File System Deployment but when we deploy to SQL Server agent it fails with 'Object reference not set to an instance of an object' within the task that re...

SSIS Package Troubleshooting

I'm working with an SSIS Package that pulls data from a DB2 source, runs through a conversion process (unicode stuff) and then stores the data in a SQL table. From the error information below, I have been able to determine that there is some kind of special characters in the DB2 file/table. What I do not know is how I can narrow down w...

Loading an XML file into a dataset using SSIS

Is it possible to parse & load an xml file with optional elements/attributes using SQL Server Integration Service 2005? Any examples/pointers would be appreciated. ...

Import WebTrends Data to SQL Server Using SSIS

Hi All, I need to import all the webtrends data to my local sql server database. on top of that i need to build reports. I am very new to Webtrends, so can some one suggest me how to import the WebTrends data to my local DB Server. Thanks ...

Read Permission problem on .NET Embedded Resource - Access DB file & SSIS

Hello. I am currently creating dynamic SSIS packages that import/export and access data between a SQL Server and several Access DB files. (Jet files if you want to get technical.) Anyways, everything is successful during testing, as long as my SSIS packages have hard-coded connection strings to the Access file. This is great and works f...