ssis

How to test the SSIS Packages?

Hi, How to test the SSIS Packages ? what are the things should be taken care while testing the ssis packages? what are the testcase steps should be written while testing ? regards, kumar ...

Lookup Transformation Task

What is the main purpose of Lookup Transformation Task ? ...

selectively execute task in ssis control flow

I have a SSIS package with a control flow containing a bunch of execute sql tasks in a sequence. I need to check a flag for each of the tasks and run the task if it is set, if not skip and go to the next one. Each of the these task executes a stored proc. So i can check in the proc and "Return" if not set. I was looking for a "SSIS" s...

SSIS 2005 - How to Import a Fixed Width Flat File?

I have a flat file that looks something like this: junk I don't care about \n \n columns names\n val1 val2 val3\n val1 val2 val3\n columns names \n val1 val2 val3\n I only care the lines with values. These value lines are all fixed width format and have the same line length. The other junk lines and column names c...

SSIS: How to transfer data from table A to table B and then update table A flag column for each row

Hi, I need to crate a package in SSIS which will selectively transfer data from Table A to table B based on a flag in Table A and after the transfer update the flag in Table A. Please advice on the approach to this scenario and the different ways in which this can be done effectively. I would also like to mention that i have never wor...

Is there a size limitation for an Access DB destination in SSIS?

I'm creating an SSIS package, which will read through a user's SQL database and populate the tables in an Access database. However, for the largest user databases, I start getting errors around the time the Access file reaches approx. 2 GB. Has anyone run into this problem? Is this a size limitation for this operation? More information...

SQL Server 2008 - Script Data as Insert Statements from SSIS Package

SQL Server 2008 provides the ability to script data as Insert statements using the Generate Scripts option in Management Studio. Is it possible to access the same functionality from within a SSIS package? Here's what I'm trying to accomplish... I have a scheduled job that nightly scripts out all the schema and data for a SQL Server ...

Does SSIS perform connections Lock?

I have read that SSIS performs variable lock in scripts. So when scriptA and script B use the same variable one of them will wait for the other to finish. Still I dont know id this is the same for Connection Strings. For example I have two HTTP Connections being used in two webservices. if they are called constantly I am testing what...

Combining two tables with SSIS into one destination table

Hi all, I am new to SSIS, so please bear with me. I created an Integration Services Project for SQL Server 2008 to import data from an old db to a new one. One of the things I need to do is import data from two old source tables into one new destination table. What is the best way to do this? I can easily see the results I want wit...

SSIS Handling Extenal Issues

I have an SSIS package that works fine. The package runs every night and takes about 4 hours to complete. I have am a newb to SSIS, so I want to see what my options are. I am not finding anything on the web about these two issues, so any advice is greatly appreciated. What to do when I have an external issue such as a power failure/acc...

Script Task - Add Reference Issue

How come I cant maintain the files which I had added for Reference? Each time when I go for Edit Script, I have to add the file for Reference for resuming the coding. Is there any other settings for maintaining the files added for reference ? And I am not getting some of the methods like "Response" then namespace like System.Web.HttpRes...

How to create SSIS package to update from one database to another database within same server

My query is related to the answers i got for questions i had posted earlier in the same forum. I have a copy of a client database which is attached to SQL Server where the Central Database exists. The copy already contains the updated data. I just want to transfer the updates from that copy to Central Database both holding same schema an...

Import data from an SSRS report via SSIS package

First, I ask that you not ask 'why.' In the famous words of Tennyson "Ours is not to reason why. Ours is but to do and die." It's one of those, "This is what you have, deal with it." situations. The source data comes from SSRS report. The goal is to load the data into a database via SSIS. The hopeful goal is to avoid human interventi...

SSIS Data Transformation

Hi, I am new to SSIS, so please bare with me. I am trying to transfer data from one db to a new one. i am fetching data from one table say i fetch name of person, then i insert this into say Table Person. this will generate a personID which i want to insert into say Address Table. What should be the approach using SSIS. Any suggestions....

SSIS DTSX File Repair Tool

I'm working with an SSIS 2005 file that crashes Visual Studio 2005 on my workstation. This happens when I open the data flow diagram and Visual Studio attempts to validate the package. I can open it successfully on another computer though. The package itself is fairly simple and only has two control flow tasks and maybe ten tasks in t...

Using SSIS, how do you read a datetime field into a variable that is of Data Type string?

This one has bugged me for the longest time and a great question to ask the Stackoverflow users I think. I have a rather large SSIS flow that uses a string variable to store the datetime. I would now like to dynamically read the datetime value from the database, but how would you construct the SSIS to do this? My first obvious thought...

SSIS how to split a single record in to two different records?

I have a Product record that has multiple "zoned" prices, one for each store that sells the product. ProductID int Name string PriceA money PriceB money PriceC money In SQL Server Integration Services, I need to split this in to multiple records: ProductID int Version string // A, B, or C Price money // PriceA ...

Using version control with SSIS packages (saving 'sensitive' data)

Hi. We are a team working on a bunch of SSIS packages, which we share using version control (SVN). We have three ways of saving sensitive data in these packages : not storing them at all storing them with a user key storing them with a password However, each of these options is inconvenient while testing packages saved and committed...

Avoid writing SQL queries altogether in SSIS

Working on a Data Warehouse project, the guy that gave us the tutorial advised that we stick to using SQL queries over defining a lot of data flow transformations, citing points like it'll consume a lot of memory on the ETL box so we'd rather leave the processing to the DB box. Is this really advisable? Where's the balance between relyin...

Excel Import how would you do it?

Ok i have a Excel import written. It uses excel automation to go through all the records and get the job done. BUt how would you do it if you had to do it? Would you use SSIS? Would You use a Dataconnection? I am really confused as to the best way to get this done properly. So that it doesn't slow down the actual application for the o...