ssis

Using Web Services in SQL Server Integration Services (SSIS)

hello guys can any one tell me the Real Time Usage of a Web Service? After calling a web service using the Web Service Task in SSIS, how can we use that web service? ...

SSIS Transactions - Large Record Sets

I am wondering how SSIS deals with transactions with large data sets. I have a 'large' data set of about 150,000 rows, each of which needs to be validated against business rules as part of an ETL from a staging database to the live database. If any of the records fail their business rules, no records should end up in the live database (...

How to compare data of two tables transformed in SSIS package

Hi, A table has been ETLed to another table. My task is to verify the data between two tables programmatically. One of the difficulties I m facing rite now is: how to use the expression that I can get from, let s say, derived column task and verify with the source and destination. or in other words, how can I use the expression to wor...

Execute SQL Server SSIS Package From Stored Procedure

I have a SSIS package that is stored in a SQL Server 2005 DB. I am trying to execute this package from a stored procedure on the same server. Is there a better way than exec master..xp_cmdshell 'dtexec /SQL... I am running into (I think) file system permission issues with xp_cmdshell that is preventing execution ...

How do I make SSIS FTP task execute a FTP script rather than just upload a file?

I am working on a SSIS package that extracts some data from DB to a file then uploads it to a FTP server. I need to execute several FTP commands but seems like the SSIS FTP task only allows me to specify source & destination locations. Does any one know how to make SSIS execute a FTP script without having to create a Script Task and wri...

Relational table copy with transformation in most generic way ( declarative rules and etc... ) - Microsoft sandpit

I guess, it is quite common task. There is a table A let's say in Visual Foxpro and needs to be transformed to table B in SQL Server 2008. Operation is going to be repetitive. Column names may be changed , some input columns dropped , column types may be changed ( string <=> int ), some output columns generated ( GUID , identity ) . Als...

SSIS Union All problem - is there a bug in SSIS for this item?

I am using SQL 2005 and SSIS.............. I have 2 data sources. One from table A and one from table B. I want to move data from table A to table B. But first i get the MAX date from both and compare them. If they are the same then i must either stop the SSIS package or use the Conditional Split. But when the MAX date from table B g...

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...

Visual Studio having trouble painting SSIS tasks

I've got this weird behavior in Visual Studio 2005 when working with SSIS packages. Visual Studio appears to be having problems painting/repainting the tasks. When I open a package, I see the label of the task items and the line connecting tasks, but I don't see the box outline or the icon of the task type. When I click on the task item...

Row Count Transformation in SSIS

Hi, I am using the 'Row Count'component to count the rows in the data flow. I have decalred a new variable called In_rec. But it is not populating the correct rowcount. Please let me know the Input Column properties for the same. ...

Access denied error when trying to load a package on Intergration Services

I am getting an error when I try and upload a import an ssis project onto our server. The server only has integration and reporting services installed as the live sql server is on another server. I log onto the integration service fine but as soon as I try to right click the MSDB folder to import a package I get the error listed at the e...

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...

SSIS how to write to log table without being rolled back by a transaction failure?

When processing a batch of records, if any of them fail business rule validations the entire set of inserted data is rolled back for data integrity. During this process I need to log such errors to a table in the database - which shouldnt be rolled back when the overall transaction is. What is the best way to do this in SSIS? Different...

How to Recover or Reset SSIS Package Password?

I have a few SSIS packages that were password-protected (their protection level is apparently EncryptAllWithPassword) by a developer who left the company and can't be reached anymore, and trying to open them gives the following error since the password can't be supplied: Error loading 'Package.dtsx' : Failed to remove package protect...

Is there any way to reorder fields in an SSIS flat file source?

I have an SSIS package using a tab delimited flat file source with a TON of fields. Recently the provider of the tab delimited flat file has decided to change the format of the flat file by sprinkling a couple dozen new fields at random into the file. Needless to say, this hosed the package. Rather than rebuild another flat file source ...

help needed on SSIS package concept

When data is copied over from source to destination in a SSIS package, source being a sql query with 'group by' keywords used and destination being a table, is it necessary that the data at a row position has to match the data at the same row position at the destination table?? sagar ...

How to stop Rounding Values in SQL Server Integration Services?

I'm using the SSIS package, there is nothing being changed from source to destination. However, the column which I'm paying attention right now has a slightly different datatype (which I hope doesn't matter) in the source and destination; the source has numeric (15,2) and the destination has numeric (16,2) and the data in the source is 4...

Using/implementing exsl function node-set() in SSIS's XML Task

Hi there. I'm attempting to apply an XSL transform to an XML file inside an SSIS package XML Task. All well and good, but unfortunately my XSL is slightly less "portable" than normal as I need to use the function node-set(). A simplified example of my XSL is: <xsl:for-each select="msxsl:node-set($familyNames)/token"> <xsl:call-templa...

What does error 0xC02020C4 mean in SSIS?

I get this error with this description. Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error i...

Handling nulls in Datawarehouse

Hi all, I'd like to ask your input on what the best practice is for handling null or empty data values when it pertains to data warehousing and SSIS/SSAS. I have several fact and dimension tables that contain null values in different rows. Specifics: 1) What is the best way to handle null date/times values? Should I make a 'default' ...