Tag: ssis

SSIS: Transform data to fixed width text with variable rows

I’m trying to make a plain text with SSIS from a SQL select. The problem is that SSIS makes the file like a table, and I need it in a specific format. For example, I have this result: bill_id | concept |concept_type |amount | ———–|————|————-|——-| 334455 |concept1 |product |12 | 334455 |concept2 |product |10,5 | […]

Execute SQL Task in SSIS – OLEDB Vs Ado.Net connection which one is better?

I am going to run a stored procedure which updates 3 tables of 60 million records each and all in same server. I am currently using OLEDB connection instead of ADO.Net connection. Points: Currently It is running for 21 minutes in management studio. If i use OLEDB connection it runs in 32 minutes If is […]

Refreshing Non-Production Environments (Data Warehouse)

My question has to do with regard to refreshing non-prod environments (Dev, Test, Staging). My question: What is typically best practice in terms of how you do it and frequency? A couple options that I thought of: Every night or periodically run the ETL jobs against their respective source environments i.e. in Dev I would […]

SSIS dataflow: SQL Command from Variable, with parameters

I defined a variable with the text of a parameterized query like this select * from t where col = ? Now, I am using that variable @[User::sqltext] in an OLE DB source for a dataflow task. Problem is I don’t see a way to set the parameters as I would have if I had […]

SSIS DF step failed but output file got created

I have an SSIS package that takes data from SQL table and then has a Multicast step splits the data 20 times.. Then I have 20 Derived columns to create different rows, then union step, then a sort step and finally a flat file destination step. One of the Derived columns steps failed (all the […]

SSIS web service script throwing binding error?

I am new to scripting (new to SSIS as well); I am trying to consume a web service URL and trying to generate XML from it. The way the web service is configured, I cannot use a Web Service Task in SSIS, so I am trying to use the script task but I am getting […]

How to delete records that don't match by source table in SSIS using hashbytes

I need to load table from source to destination in SSIS. Table contains approx 1.5 million row. So far, I was able to insert new records into destination table that didnt match on ID column, and also update those rows that matched on ID but didnt match on HASHBYTES value. This is what I’ve done: […]

Bing Geocode API, SSIS, C# Script

I’m trying to replicate what I found here to use bing’s geocode API: https://sqlmd.wordpress.com/2012/03/27/using-the-ssis-scripting-task-to-geocode-addresses/ When I run what I have I get this error: Error: 0xFFFFFFFF at Get Lat Long Bing, Error:: Could not find default endpoint element that references contract ‘bing.geocode.IGeocodeService’ in the ServiceModel client configuration section. This might be because no configuration file […]

C# – Running a SSIS on a SQL Server from a wpf Application

I cannot seem to find any useful documentation for the specific situation i need. I am trying to Launch and pass variables into an SSIS package that is deployed on a sql server. In management studio the package can be found under Integration Services Catalog\SSISDB\IhsDataSync\Projects\LoadHeader\Packages\Package.dtsx. From the sources I have found it appears that I […]

SSIS 2012: Packages runs in SQL agent, not in BIDS or cmd-line

I have a development server that will run packages from SQL Agent (according to schedule, and manually via Job Activity Monitor), but not by running it from BIDS or by two different ways on the command-line. The error we get in BIDS is: 0xC0010026 at ACME Raise Error: The task has failed to load. The […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.