ssis

ssis flat file source trim blank spaces

Is there any way i can trim the blank spaces when I read data from CSV file? Thanks. ...

SSIS Excel Destination with format

Hi guys, I try to export excel file with some format like: Interior.Color HorizontalAlignment Font.Bold and etc.. So the question is, can we do like that is SSIS.. and how to do it? ...

Handling primary key duplicates in a data warehouse load

I'm currently building an ETL system to load a data warehouse from a transactional system. The grain of my fact table is the transaction level. In order to ensure I don't load duplicate rows I've put a primary key on the fact table, which is the transaction ID. I've encountered a problem with transactions being reversed - In the transac...

Trying to Export Data from an unusal Access Database with Synonyms

We use a product called SalesOutlook which is a type of CRM system. I need to export data from this using SSIS or some other program. When you install the program, it creates a DSN on my system called SalesOutlookReports. It use the Microsoft Access Driver (*.mdb). When I try to look at it using Crystal Reports I see a list of tables...

What's the best way to parse an Address field using t-sql or SSIS?

I have a data set that I import into a SQL table every night. One field is 'Address_3' and contains the City, State, Zip and Country fields. However, this data isn't standardized. How can I best parse the data that is currently going into 1 field into individual fields. Here are some examples of the data I might receive: 'INDIANAPOL...

SSIS For Each File Loop and File System Task to copy Files

I'm using a files system task inside a for each loop container, just as described here: link text However, when I execute the package I get this error: [File System Task] Error: An error occurred with the following error message: "The process cannot access the file 'C:\Book1.xlsx' because it is being used by another process.". I do no...

SSIS DTS Package flat file error - "The file name specified in the connection was not valid"

I have a pretty basic SSIS package that is attempting to read a file hosted on a share, and import its contents to a database table. The package runs fine when I run it manually within SSIS. However, when I set up a SQL Agent job and attempt to execute it, I get the following error: Executed as user: DOMAIN\UserName. Microsoft ...

How to parse a date from an SSIS Excel filename

I want to use the foreach container to iterate through a folder matching something like: "Filename_MMYYYY.xls". That's easy enough to do; but I can't seem to find a way to parse the MMYYYY from the filename and add it to a variable (or something) that i can use as a lookup field for my DimDate table. It seems possible with a flat file ...

Table Variables in SSIS

In one SQL Task can I create a table variable DELCARE @TableVar TABLE (...) Then in another SQL Task or DataSource destination and select or insert into the table variable? The other option I have considered is using a Temp Table. CREATE TABLE #TempTable (...) I would prefer to use Table Variable so that it remains in memory. But ...

How do I configure SSIS logging to overwrite the log file?

My SSIS package has logging configured with a SSIS log provider for text files, which works fine, but each time the package is run the log appends to the end of the log file. I want it to truncate the file and only keep the log from the most recent execution of the package, but I don't see an option anywhere to do that. I've tried both...

SSIS IsNumeric expression Error

Hi am using following exression in ssis package !ISNULL((DT_I4)Route) ? (DT_WSTR,50)("SB" + SUBSTRING(RIGHT(Route,2),1,1)) : (DT_WSTR,50)Route when the Route value is Numeric it is sucess, when it is Non-numeric failing with following description. Any help,how to resolve this issue [Derived Column [111]] Error: SSIS Error Code DTS_E_IN...

Aggregate analog of row count component?

I'd like to have a way to get the sum of a field stored into a variable from inside a dataflow task. The rowcount transform does this, but I can't see how to use any other aggregate functions. Is scripting a transform component my best bet? ...

Multiple SQL Server Integration Services (SSIS) database instances on one machine

Hi, i want to configured the configuration file to use more than one MSDB database, which are stored in separated database instances in Stored packages of SSIS database. if any body knows let me know ? write in steps how to do..? Regards. kumar ...

SSIS package in SQL Server 2005 can send data to SQL Server 2008?

Hi SSIS Geniuses, I Have an SSIS package in SQL Server 2005 that takes data from a flat file and puts it in a table in SQL Server 2005. Now I want it to send it to an additional location along with the 2005 location. This new table is in SQL Server 2008. Can it be done without porting my SSIS package to SQL Server 2008? Thank you for yo...

cant start SSIS service on sql server 2008

Hello, In Sql Server Configuration Manager I Sql Server Integration Services has status stopped, when I try to start it aftger few seconds there is an error message: The request failed or the server did not respond in a timely fashion. Where the problme can be ? ...

Can't install Server tools with SSIS on top of Express Edition

I have a SQL Server Express edition with management studio and i'm trying to use the enterprise disks ("SQL Server 2005" Enterprise Edition 32-bit Server Applications") and I can't get it to give me the option of installing the server components or a server instance. What am I missing? ...

Within SSIS - Is it possible to deploy one package multiple times in the same instance and set different ConfigFilters (I'm using SQL for config)

In my environment my Dev and QA Database Instances are on the same server. I would like to deploy the same package (or different versions of the package) into SSIS and set the filter to select different rows in the Config table. Is this possible? This is SQL 2005. For the sake of this question lets say I have one variable, which is a ...

MaximumErrorCount has now effect

Hi I'm quite new to SSIS - using 2008 version. I have a job that uses a few data flow tasks. On the third one I'm getting a primary key violation on the last row that it needs to insert, but only sometimes! I'd like to ignore this problem for now and let the job continue. I have set the MaximumErrorCount property to 10 for the DataFlo...

Package Confugurations

I had created a package with necessary connections. And I had enabled Package Configurations. But I am unable to execute the package. Error is shown. And its related to connection failure , I think. ...

SSIS FTP task connection error

Hi, I have a FTP connection manager in SSIS. I have configured it with serevr IP, port number, user name, and password. It was working fine. But suddenly its stopped working. But i am able to login directly to the FTP web site directly .It was not working temporarily.But now its working with same login credentials. But my FTP connect...