SQL Server Integration Services, an ETL tool sold by Microsoft and bundled with SQL Server versions since SQL Server 2005.

learn more… | top users | synonyms

1
vote
1answer
261 views

Sending SQL Server query as CSV via e-mail

Currently I'm using SSIS w/ a hacky solution to get around it not supporting auth on SMTP servers. Is there a better way I can save a query to a CSV file, then mail it as an attachment? Thanks
1
vote
2answers
7k views

SSIS Script Component - How to modify the Output0Buffer

I've a script component which accepts records from SQL Azure database table. The script then invokes a web service, which returns the number of failed and success records. For all the records, I ...
1
vote
2answers
1k views

Flat file manager TextQualifier not working

I've a CSV text file which contains col1,col2,col3 "abc,cdef",a,b When I use the Flat File Source and preview the a bc,cdef appears on first column. But when i run and view the result in ...
1
vote
1answer
35 views

Dealing with Violation of PRIMARY KEY constraint SSIS

How do i deal with this error in SSIS. I'm trying to load a data into an archive table. The package failed and came up with the below error message. I know that there are conflicting keys in the ...
1
vote
0answers
15 views

update new ID_MergeTo from operation table ID if MergeFlag is true and MergeTo defined?

I have problem I don't know how to solve it. I have two tables Staging and Operation.I create Data Flow Task using SSIS to move data columns from ...
1
vote
0answers
66 views

Error when trying to deploy SSIS package

I am trying to deploy my 2012 SSIS project to the Integration Services catalog on the SSIS server (also 2012). I am getting the error below. I checked that there is plenty of space in SSISDB and ...
1
vote
0answers
41 views

Cross Server and Instance Login SSIS SID or ID Issues

I have some challenging login/SID/ID issues with authentication between databases on separate instances and servers. We have a Development, Test, Pre-Production and Production server/instance ...
1
vote
0answers
39 views

Will redeploying a project to the SSISDB invalidate current jobs

I’ve been given 2 dtsx packages to replace 2 in production. This is for a SQL 2012 server with the packages deployed in the project model. The only way I see to do the update is to export the project, ...
1
vote
0answers
61 views

Errors in the metadata manager. The process type specified for the Effect dimension is not valid since it is not processed

Trying to hunt down an issue with an SSIS package we have and I don't know what to look for to help correct this issue. Error: Errors in the metadata manager. The process type specified for the ...
1
vote
0answers
86 views

How to copy a subset of SQL objects across databases within a transaction?

We already have a .dtsx package to copy a subset of tables (about 20) from one database to another (at least once in every month). It is using "Transfer SQL Server Objects Task". However this does not ...
1
vote
1answer
47 views

How to deal with multiple error columns in data flow

I'm writing a simple package to load data into tables and a I have multiple destinations within the same package. I'm attempting to first attempt batch inserts but want to fall back to row by row in ...
1
vote
1answer
2k views

Creating Excel files in SSIS based with a predefined format

I need to create a statistical report in Excel and wondering if I can use SSIS to read data from the database (calculate the aggregated numbers) and load them into an Excel file with a specific ...
1
vote
0answers
83 views

What type of fact table / loading solution for a reservation system?

I am designing a Data Warehouse with SQL Server 2012 and SSIS. The source system handles hotel reservations. The reservations are split between two tables, header and header line item. The ...
1
vote
0answers
224 views

Timeout while using table valued parameter

I'm trying to find the cause for this error while calling a stored proc with a table valued parameter: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target ...
1
vote
0answers
339 views

In the Import/Export Wizard inside SSDT, why is the MSAccess DB Engine data source looking for a workgroup information file?

Running MSSQL12, VS10 Shell, MSA2013 with local .accdb files, Microsoft Access Database Engine 2010 Redistributable (x86_64) and Microsoft Access Database Engine 2007 Redistributable (x86) installed. ...
1
vote
0answers
244 views

CDC with SSIS: process all changes in order of occurrence

I have a SSIS package that I'm using to sync (in an on going basis) all changes from one table to another. Note that I need to sync ALL changes and not just NET changes. My problem is that I cannot ...
1
vote
0answers
1k views

MS SQL Server - Login timeout expired

I am doing this tutorial of Microsoft: Basic Data Mining Tutorial http://technet.microsoft.com/en-us/library/ms167167.aspx I am using SQL Server 2012 and SSIS - SQL Server Integration Services but I ...
1
vote
0answers
45 views

Integration Service cannot be upgraded/removed

I have Sql Server 2005 Integration Services installed in our server whose version is 5292. It needs to be upgraded to version 5324. After running the patch the summary report says it is upgraded ...
1
vote
0answers
128 views

What is the best process for merging packages to use SSIS package configurations?

I have several packages with hard coded connection managers which I would like to merge into one package. Each individual package is currently loading a single data file to a single table is a single ...
1
vote
1answer
986 views

Wrong error in SSIS - [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: “ResultSetType_Rowset”

I have an Execute SQL task which gives returns only one row with one column - a number. I set my result set to single row. Despite that, the task fails. Why ? How do I fix it ? [Execute SQL Task] ...
1
vote
0answers
74 views

Displaying SSIS variabe values in Integration Services Dashboard

I have an SSIS package that executes stored procedures which return output parameters into ssis variables. How can I easily display these variables content in the Integration Services Dashboard ? ...
1
vote
0answers
33 views

SSIS 2008 Connection Managers (Project Specific) and Corresponding Configurations

These are the requirements that I want to complete: a. I want the package to only use connection managers that are project/not package bound. b. I also want these connections to be bound to the ssis ...
1
vote
1answer
217 views

Avoiding table level locking [closed]

I am maintaining a queue in a database table. An SSIS package checks the queue every two minutes. If it finds anything then it runs a job. I am experiencing some sort of table level locking. The ...
1
vote
0answers
276 views

Can SQL Server 2008R2 SSIS packages access SQL Server 2012 databases with driver updates only?

We have two products running on SQL Server that we will be upgrading to SQL Server 2012 on a staggered schedule. Product B uses SSIS packages (using ODBC drivers) to extract data from Product A's ...
1
vote
0answers
226 views

Accessing environment variables at design time in SSDT?

I'm re-designing some SSIS packages for 2012 and some parameter/variable/configuration issues are not clear to me. The result that I am trying to achieve is that the user context's temp folder(stored ...
1
vote
0answers
1k views

SSIS Merge Join Trouble: 3 tables into 1

So I am having trouble merging three tables into one. All sources are DB, and I am using the MERGE JOIN function. When I join my first two tables, using standard left inner join, it returns the exact ...
1
vote
0answers
217 views

Login failed for user sa ssis 2008

I have a problem with ssis2008 I built a package that have a connection with SQL Server authentication (username : sa). When I use BI, it works fine but when I build a job that used this package it ...
1
vote
0answers
1k views

sql agent job hangs without any errors or warnings

Problem: we have a SQL Agent job which runs everyday in the morning at 4:00 am. This job hangs sometimes without providing any errors or warnings. It just hangs. BUT when i stop the job and run again ...
1
vote
0answers
131 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
1
vote
0answers
109 views

Where can I get information about AcquireConnection call?

I have a dtsx package that fails to run. When I turn on logging I get: The AcquireConnection method call to the connection manager "MY_DSN" failed with error code 0xC0202009. MY_DSN is a ...
0
votes
2answers
1k views

Doing a comparison on two csv files with no Primary Key

I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique. To give you a better picture from what I mean; In initial load ...
0
votes
3answers
181 views

Convert yymmdd to datetime using ssis derived column

Below are the strings representing date of birth (yymmdd). I want to convert it into datetime format and load into a database using ssis. How can I do it? In my derived column I have ...
0
votes
2answers
342 views

Why does my SSIS package produce different results when executed from a maintence plan

I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file ...
0
votes
3answers
291 views

How can I insert '&' symbol in data

I have a source table where one of column has data like: Column SD SD .. .. JK Jk .. .. I want to load this data into staging in format of: Column S&D S&D .. .. J&K J&k .. .. ...
0
votes
3answers
770 views

SSIS 2008 painfully slow to import CSV

I am using SSIS to import a 25,000 row CSV to an empty table. The table has a primary key that is populated as the data is inserted. I am using an ADO.Net destination. What reasons are there why ...
0
votes
1answer
173 views

BI development in Microsoft BI

I need to learn how to create a dimensional modelling for an organisation that do not has a BI tool and BI model. Where can I learn more about BI development that should be used in a company? IT's ...
0
votes
1answer
48 views

Script not emailing from Maintenance Plan/SSIS

I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL ...
0
votes
1answer
1k views

SSIS Connection String Expression at Run Time and during Execution

I have an Excel connection manager which should write to a file which does not exist prior to runtime but rather is created during execution. I have an expression set for the connection string ...
0
votes
2answers
1k views

'skip rows' in a flat file source does not work as advertised

In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data ...
0
votes
1answer
952 views

SSIS Adding column to excel sheet template destination when importing from SQL Table Source

I have an excel sheet template that has 3 columns. I also have a SQL server table that has the same 3 columns plus an additional 4th column. I need to import all 4 columns from the SQL table into the ...
0
votes
1answer
117 views

SSIS - Excel destination does not allow characters more than 255

My SSIS package has a ado.net source, which returns columns with width more than 2000 characters,but those columns get truncated when redirected output to excel destination. Any idea, how to resolve ...
0
votes
1answer
2k views

Update existing SQL Server 2008 table via Flat File using SSIS

I am trying to update existing records or add any new ones to a SQL Server table using a flat file. Also I need it work as an SSIS package. I don't get an option to do a source query like I do for ...
0
votes
3answers
1k views

SQL agent job step's connection managers

Is there any way with T-SQL to list the connection managers that are used in an SQL Agent Job step?
0
votes
2answers
443 views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
0
votes
2answers
585 views

migrate SQL Server geography data to PostGIS geometry data

I'm trying to use SSIS to transfer point data from a Geography column in SQL Server 2008 R2 to a geometry column in a Greenplum (Postgres 8.2.15) database with PostGIS. I know there's a way to do the ...
0
votes
1answer
303 views

How to load more than one MS-Access-db tables with data in SQL-Server-DB

I'm just getting into SQL-server 2008 R2 ssis, so forgive me if this is a basic question. I'm building an application using integration service. One of my computer folders contains more than one ...
0
votes
1answer
548 views

Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio

I have what is effectively a template SSIS package (well a maintenance plan actually) that is copied and used as the basis of new maintenance plans. In this package there is a Sequence (or a ...
0
votes
2answers
7k views

how to send mail from gmail smtp server by using the ssis [duplicate]

I'm just getting into sql-server SSIS, so forgive me if this is a basic question. Use the SSISwant to send mail from gmail smtp server. Smtp host =smtp.gmail.com port=587 , ...
0
votes
1answer
39 views

SSIS Load using SQL Command

I'm trying to insert the below query using SSIS. I'm doing this just because there are some overlapping ID's that are already existing ID's in the destination table. I tried using the following to get ...
0
votes
1answer
93 views

SSIS Get max value in column and create new column?

I'm importing two data sources. The problem is that one data source has a date, the other does not. Everything in the data flow has the same date and is inserted as one transaction into the same ...