ssis

SSIS - Sharepoint list data transfer

Is there a limit on the number of rows that can be transferred from a Oracle database(OLEDB source) to a sharepoint list using SSIS? I am getting exception after transferring 19000 records ...

Interview - What questions asked by Project Managers

Hello everybody, Been for an interview in regards to ETL Developer (Microsoft Toolset / SQL BI suite) - been success through technical interview - reference check done - all good. As the PM was not there during interview period, he wants to have another interview before finalising something. What normally PM interview questions are...

SSIS Packages are not listed in integration services

Guys, We have multiple SSIS packages deployed on a production box. All the scheduled jobs that call these packages run fine. Whenever I create a new package and try to deploy, I can see the folder structure and deploy fine. Here is the problem I am facing: When I log into integration services from SSMS, none of the deployed packages ...

Performing a slow update with SSIS

I have a raw file that contains 2m rows ; an ID and a text column What I want to do is use this raw file and update a table on our live database. The problem I have is I want this to happen in batches/slowly as I dont want to impact live too much whilst it is doing this. The process will need to open the raw file using a Dataflow task...

SSIS- Sharepoint list data transfer issue

Hi , We are trying to transfer data from oracle database (about 60,0000) records only to a sharepoint list using SSIS. But we are getting following error when records reaches around 19000 . The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020 and System.ServiceModel.ProtocolException: The remote server...

How do I keep a table synchronized with a query in SQL Server - ETL?

I wan't sure how to word this question so I'll try and explain. I have a third-party database on SQL Server 2005. I have another SQL Server 2008, which I want to "publish" some of the data in the third-party database too. This database I shall then use as the back-end for a portal and reporting services - it shall be the data warehouse. ...

Installing Sql Server Integration Services In Visual Studio As Plugin

I ve looked around on Google for a while now without any joy. I have SSIS at work and need it at home - I ve tried installing / uni stalling SQL server with no joy Does anyone have a link to details of how to install? This should be easy!! ...

Executing an SSIS DataFlow task with different variable value

Hi, I need to query three different database and dump them into csv files. Its the same procedure for the three databases. The only difference is the database and the name of the csv file. Can I do this without cutting and pasting? Is there a way to pass parameters to the data flow task? Thanks! ...

SSIS: How to read flatfile and add a new row to the file.

Hi! I have a text file and needs to read it and change some text and add some new text in a new row. How do I add e new row with some text in it? I now use a script component to read existing rows and change in them but I can't add a new row :-( Thanks in advance Regards Brno ...

Using IMEX=1 in SSIS excel 12.0 connection manager causes export to fail

Hi all, I'm using SSIS VS 2008 to export a bunch of data to an Excel 2007 file. I've used the IMEX=1 option many times in the past to export data to excel 2003 files with great success. For some reason though, this time when I add it to the connection string it falls over at the export step. If I take it out, everything works like a ...

Data Warehousing Postgres

We're considering using SSIS to maintain a PostgreSql data warehouse. I've used it before between SQL Servers with no problems, but am having a lot of difficulty getting it to play nicely with Postgres. I’m using the evaluation version of the OLEDB PGNP data provider (tiny.cc/qLoS2). I wanted to start with something simple like UPSERT o...

SSIS Package won't execute when called.

Hi, I have an 2005 SSIS package that I'm calling in a service created in VS 2005. The package will not run. The purpose of the package is to parse a file and put data into a "Load Table". The package runs perfectly on its own, but will not run at all when executed programatically - when I'm stepping through the code. The Event Viewer ...

Problem validating in SSIS XML Task

I have an XML file that I'm trying to validate against an XSD file in an SSIS XML Task. The XSD does do a handful of imports and includes to other files and utilizes multiple namespaces. I have opened these all in VS2005 and verified that no errors occur when validating the file there. However, when I execute my SSIS package, it does ...

SSIS Package Config File Encryption

We have SSIS package config files that contain DB encryption passwords or PGP encryption passwords. I came to the conclusion that there is no "silver bullet" solution for encrypting SSIS package config files like with web.config files ect. Should we consider not using config files at all for SSIS packages and if so what other options d...

Microsoft SSIS 2005. Can't Use Transactions

I have set the package TransactionOption to Required and DataFlowTask's TransactionOption to Supported, but when I execute the package, I get an exception: [ADO NET Destination [82]] Error: The transaction has already been implicitly or explicitly committed or aborted Why does this happen, and how can I fix it? ...

What are SSIS packages deployment and change-tracking best practices ?

I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual. How can I automate the SSIS package upgrade process (right now, I copy my packages manually to ea...

SSIS - Importing variable format text files

By "variable format" I mean that each row in the text file begins with a single character code denoting the record type, and each record type has a different format. I believe this is some sort of fairly common mainframe data export method. What makes matters worse is the fact that the record types are actually hierarchies. The codes in ...

How to export tables from sql server to ms access on the fly?

I would like to publish some data of a sql server 2k to msaccess databases. I'd like to do that given a table supplying datatransformation info, for example : tablenameOnServer | pathToPub ------------------------------------------------------ Clients | D:\Data\Pub1\ClientData1.mdb Orders | D:\OtherData\Pub...

Using Temp tables in SSIS

I am using a Temporary table in Stored Procedure in SQL Server. I am trying to use that SP in OLE DB Source Editor. I can see the data output returned in the Query Builder that comes with Build Query button. But when i Click the Columns tab, I am getting the below error. Blockquote - TITLE: Microsoft Visual Studio Error at Da...

SSIS and MySQL - Table Name Delimiter Issue

I am trying to insert rows into a MySQL database from an Access database using SQL Server 2008 SSIS. TITLE: Microsoft SQL Server Management Studio ------------------------------ ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.0.51a-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL serv...