integration-services

How to insert a row into a dataset via SSIS

I'm trying to create an SSIS package that takes data from an XML data source and for each row inserts another row with some preset values. Any ideas? I'm thinking I could use a DataReader source to generate the preset values by doing the following: SELECT 'foo' as 'attribute1', 'bar' as 'attribute2' The question is, how would I inse...

Integration Services: Multiple-step OLE DB operation generated errors

I'm attempting to make a DTS package to transfer data between two databases on the same server and I'm getting the following errors. Iv read that the Multiple-step OLE DB operation generated error can occur when you are transferring between different database types and there is loss of precision, but this is not that case here. How do I ...

How to turn on FastParse in SSIS (SQL Server Integration Services)

The book that I purchased to help with my SSIS understanding seems to have glossed over this, and I wanted to know exactly what is the method to turn on FastParse in SSIS? ...

How to remotely run a DTSX package from bat file?

I am trying to remotely run a DTSX package from a bat file with this command: DTEXEC /DTS "\File System\MY_PACKAGE_NAME" /SERVER MY_SERVER_NAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V This is working fine locally but failing remotely (I do have admin rights on the machine I am pointing to and I have SQL permissions as w...

Remove last record from the text file!

What's the easiest way to remove last row from the text file using SQL Server Integration Services? Thanks. ...

Can you use Sybase.Data.ASEClient.dll .Net Provider with SSIS?

I am trying to create a data connection in SSIS. I have the Sybase.Data.ASEClient.dll version 1.1.680 installed but this does not show as a .Net Provider. Is it possible to use this ADO.NET provider with SSIS and if so how can I make it accessible? ...

How can I manually fail a package in Integration Services?

I am running an Execute SQL Task statement in my SSIS package. The Execute SQL Task is running sql and checking that the tables have more than 1000 rows. If they have less than 1000 rows, I want to fail the package. How do I force a fail inside of a SQL statement? ...

How can I parse Serv-U FTP logs with SSIS?

A while back I needed to parse a bunch of Serve-U FTP log files and store them in a database so people could report on them. I ended up developing a small C# app to do the following: Look for all files in a dir that have not been loaded into the db (there is a table of previously loaded files). Open a file and load all the lines into ...

How do I use the OLE DB provider for Directory Services in a SQL Server Integration Services project?

I can setup a connection manager that uses the Directory Services OLE provider and points to one of our domain controllers. Then, in the Data Flow area, I create a OLE DB Source and set the Data Access Mode to "SQL Command". I then use this query to pull data from Active Directory: Select employeeid, sAMaccountName From 'LDAP://MyComp...

How can I specify a variable file name using SSIS?

I'm a newbie to using SSIS for data imports - I have a couple of files that I want to import into my database schema, but I'm going to need to run this once every 3 months; the file names change based on the quarter e.g. SKU1QTR will become SKU2QTR. How can I handle a situation like this with SSIS? Is there a way to specify the file ...

SSIS Converting a char to a boolean/bit

I have an SSIS package to load data; as you may recall there are flags that are in data files as Y/N char(1) when I am trying to load them as bit flags into SQL Server. I am specifying the columns in the data file as String [DT_STR] and I have a data conversion task to convert them to booleans based on the following expression (I receiv...

Execute an SSIS Package created with VS2008 on SQL Server 2005

Maybe a stupid question but... I created an SSIS package using VS2008 Professional. I want to deploy and execute it on a server running SQL Server 2005. When I try to run it I get an error stating the version of the Execute Package Utility on the server is different than the version of the package I'm trying to execute (makes sense, si...

Problems executing a SSIS package deployed to the file system

I have a SSIS master package, which executes several child packages. It works great, but when I deploy it to the file system on the server, I get an error code "0xC00220DE". "The system cannot find the file specified." When I run the package on the server by double-clicking it, it works correctly. But when I use DTExec: dtexec /FILE "d...

SSIS - Saving to Office 2007 Format

I'm trying to save an excel spreadhseet from SQL Server Integration Services 2005. Unfortunatly I only seem to be able to save in 2003 format but I want to be able to save in 2007 format. Is there wa way to do this? ...

Crazy SSIS Component Arrangements

Is there a way to tell Visual Studio 2005 to not rearrange your SSIS package components when you close and re-open the .dtsx? VS2005 always moves my components around in the Control Flow tab and adjusts the flow pointers to seemingly random positions and it gets quite irritating. Anyone know the setting (if any) to stop this? Thanks in ...

Are my issues with SSIS justified?

I've only been using SSIS briefly, but I find that my complaints are numerous. Here are my current issues: In order for a package to store a password, you need to encrypt it. Even if the package is part of a larger solution, you need to supply a password anytime to open any of the encrypted packages. Why can't you just encrypt the w...

Using integration service, how can I copy a record, with all the records that the main record has a foreign key relationship to

I am working on creating the necessary views, triggers and stored procedures so I can make it easier for people to use Integration Service to copy data to and from our database, which is an entity-attribute-value schema, so the foreign key relationships are not always explicitly stated in the schema, but in my view I can hopefully make i...

Recommendations for good SQL Server Integration Services (SSIS) examples/samples for ETL?

Hi Everyone, I'm looking for some decent examples/samples using SSIS to do some ETL from one SQL Server database to another not necessarily within the same instance. The idea is to migrate rows of data with their heirarchies (relationships) from one OLTP database to another. There are some advantages SSIS offers us which makes it a go...

SQl server log

Hi, In SSIS packages i want the see the compelete detail of logs like how many rows are inserted throug my packages etc please help me how can i configure it ? ...

SSIS Problem reading from flat file

My SSIS program reads as input from a .csv file. The file has about 60,000 rows. And my SSIS package fails during the read, saying cannot convert a certain column due to potential loss of data. Now, I am certain that the majority of the rows are correct. As I have tried pasting random subset of the file and the SSIS reads fine. But I ...