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

0
votes
1answer
51 views

SSIS unpivot returns different results when run via a SQL agent job

I hope someone can help me. I have an SSIS package which grabs data from an excel (.xls) file, unpivots it from a rubbish format into a 'proper' format, transfers it to a SQL server 2008 R2 db, and ...
3
votes
0answers
90 views

Can't use Fuzzy Lookup in BIDS

A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows(For Testing Purposes). The weird thing is that when he tries to execute an ...
0
votes
1answer
57 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
2answers
374 views

SSIS Failed to start project

I've created sereral SSIS packages in Visual Studio 2012 and they can be executed without any problems. For a project I'm working on I generate packages using BIML. In my BIML script I create a ...
1
vote
0answers
31 views

Run SSIS task regardless of checkpoint

Is it possible to make a task always run, regardless of whether the package starts from scratch or from a checkpoint restart?
0
votes
2answers
153 views

CSV data export to SQL Database with double quote problems

I am trying to import data from falt files (.CSV) into SQL table. I am able to import them no problem but the data is coming with double quotes. What is the best way of resolving this problem? ...
0
votes
0answers
743 views

SQL Server Agents Jobs failed when executed on schedule but succeeds when executed manually

I have a SQL job running an SSIS Package. This job can run manually but failed to run on schedule. The below is the error message generated. Any help will be highly appreciated. Code: 0x4004300A ...
0
votes
1answer
338 views

How to deploy SSIS package with XML config, using SQL agent

I wonder if anyone can help me with a question. Context I've created a database which needs a front end in MS Excel. When the user presses upload button I want it to start an SSIS package which moves ...
0
votes
0answers
156 views

SSIS - Multiple data source and single excel destination, causing SqlDumper error

Brief Architecture: I am creating a SSIS package to generate Excel reports with single excel, but multiple sheets. User can select single or multiple reports for export. There are different types of ...
0
votes
1answer
278 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
89 views

Problem with SSIS Variable Expression containing getDate()

Brief Architecture My SSIS package is generating dynamic excel files through Data flow. The file name format is "XYZ_Year_Month_Day_Hour_Minutes.xlsx". So I am using variable with expression which ...
1
vote
0answers
53 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 ...
3
votes
1answer
333 views

Conditional data flow task in SSIS

Brief Architecture: I am creating a SSIS package to generate Excel reports. There are different types of reports, So I have created a table to track each user request for report generation and ...
0
votes
1answer
75 views

How can I restore ##MS_SSISServerCleanupJobLogin##?

At some point the SSIS cleanup job login (##MS_SSISServerCleanupJobLogin##) got deleted from my sql server (2012). I'm trying to upgrade to SP2 and I'm getting an error message I believe is related to ...
1
vote
2answers
43 views

best way to deal with imperfect xls source file that needs to be imported into SQL via SSIS

The data is formatted like this: Date Type Account 1/1/14 AAA 111 112 113 BBB 114 115 ...
1
vote
0answers
47 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, ...
4
votes
1answer
415 views

Allow non-sys admin, non-owner of a job to execute it

I have a job that runs an SSIS package. Currently the owner is a proxy account. I can run the job manually from a sys-admin account. Our web service logs in using a limited account. It needs to ...
1
vote
0answers
78 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 ...
0
votes
0answers
28 views

Got error with lookup transformation in sql server 2005 BIDS

I have query to seperate the current and history DB's ** select , year(doj) from source where year(doj)<=(year(getdate())-3)* Here the moved data(<=2011) from current DB should be deleted and ...
0
votes
1answer
273 views

execute [SSISDB].[catalog].[create_execution] with 'execute as"

We are testing a 3rd party tool to schedule/execute jobs in our environment so trying to test launching SSIS packages within the integration service catalog. I have found examples of the code to run ...
0
votes
1answer
957 views

SSIS Package Validation Error when Executed From Windows Task Scheduler

I have deployed my SSIS package using the project deployment model to the SSIS catalog and experience a strange validation error when I try to execute the package. I believe this is due to a ...
2
votes
3answers
204 views

Looking for a utility to move data between databases (prod to dev) SQL Server 2012

I am looking for an easy way to copy table data from a production SQL Server 2012 machine to a development machine (same version, level). We currently just do an export from within SSMS, but we have a ...
0
votes
1answer
456 views

Invalid date format in SSIS

I am using BIDS to export data from Oracle to SQL server. I am using OLEDB source and destination. I have around 6 datetime fields in ORACLE out of which two fields give no problem while 4 fields give ...
3
votes
1answer
185 views

SQL Agent Permissions - Job Level

We currently have an SQL Job that runs an SSIS package. The output of the package is required for a report. As a result, the application that can create the report on demand first tries to run the ...
0
votes
0answers
78 views

SSIS - the 1st item in the data flow package view always imports way slower than the others

In the SSIS data flow task, I've been importing CSV files to my SQL Server via Flat File Source -> OLE DB Destination. When I'm setting things up in the package design preview, if I have just one ...
2
votes
2answers
209 views

Importing different file types to SQL Server

I'm looking for direction here. I need to automate a process that will import several different file types into SQL Server. They are all text based files (no excel) but the internal format of the ...
0
votes
0answers
364 views

How can I resolve timeout errors in SSIS around midnight every night?

I find a pair of these in my Application Event Log a little after midnight every night: The SSIS Execution Process could not write to the IS catalog: MyServerName:SSISDB Error details: Unable to ...
1
vote
1answer
921 views

Moving SSIS Packages to New Server

I have a number of SSIS packages in a number of folders on MSDB SSIS on SQL Server 2008R2. We are migrating to a new server host but remaining on SQL Server 2008R2. How can I copy/migrate these SSIS ...
2
votes
0answers
156 views

SSIS Bulk Insert Task Operating system error code 1311

I have been running SSIS 2008 R2 Packages for a long time and when ever my Packages trough SQL Server Job accesses FTP Server which is on the same domain it uses correct credentials ,e.g. ...
0
votes
0answers
94 views

How to fix type conversion error in Data Flow task?

I have an SSIS job with a data flow task having an OLEDB source and OLEDB destination. I am moving almost 40,000 rows from one DB to another DB. In my SQL query**(Source**) , i have 10 columns all ...
1
vote
1answer
1k views

SQL Server 2012 Agent Job not providing full failure message for SSIS Packages

(this was also posted at http://www.sqlservercentral.com/Forums/Topic1576641-2799-1.aspx) We are having an issue with 2012 SQL Servers not reporting why a job has failed, this is happening for jobs ...
1
vote
0answers
92 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 ...
0
votes
1answer
282 views

How to Implement a Retry Feature in SSIS 2012 When a Database Connection Fails?

We have an SSIS 2012 package that supports our internal analytics reporting database by extracting data from an external database server. Last night, for the first time, the package failed due to a ...
1
vote
1answer
51 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 ...
0
votes
0answers
570 views

ssis ado.net destination has failed to acquire the connection

I am Transferring data from sql server source db to sql server destination db (cross db). but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may ...
0
votes
0answers
54 views

Create replication sql server for DWH [closed]

I have a DWH and I need to make an update once a day from STG Database to DWH I need use replication in SQL SERVER, How I do this?
1
vote
1answer
3k 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
1answer
65 views

How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake

In deinstalling a named instance of SQL Server 2012 from a Production server, inadvertantly, I selected SSIS as a product to deinstall. Now, I need to reinstall it with the least disruption possible. ...
0
votes
0answers
325 views

SQL Server Agent - Running SSIS Package with Proxy

I have followed the below article to create credentials/proxy to run a SSIS job from SQL Server Agent. ...
1
vote
0answers
91 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 ...
2
votes
1answer
3k views

How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

i am executing query like this on sql server 2008 R2 DECLARE @returncode int EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"' ...
3
votes
2answers
355 views

SQL Server Memory Issues

We have a SQL server that appears to be leaking memory. Memory consumption linearly increases over time until the server is completely out of memory, causing major performance issues. The SQL server ...
6
votes
1answer
312 views

OLE DB Sybase Connection Failing

I have a connection to a Sybase database which we stage in a SQL Server 2008R2 database. I can connect the the database and the connection tests out fine. I am using an OLE DB source with a Sybase ...
2
votes
1answer
494 views

How can I get more information about which lookup failed in an SSIS lookup?

I'm frequently getting errors along the lines of Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup. To resolve these, I'm going back to the source and ...
0
votes
1answer
292 views

Why Does SSIS Oracle Source by Attunity return DT_WSTR for Oracle CHAR(1) Column?

Using SSIS 2012 and Microsoft SSIS Connector for Oracle by Attunity v. 2.0. When I add an Oracle Source for a particular table, it is setting the data type for an Oracle CHAR(1) field to DT_WSTR. I ...
4
votes
1answer
567 views

Deleting Rows is Increasing Index Size

We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table ...
0
votes
0answers
37 views

Merge data using Integration Service

Please Consider this scenario: I have a table in my database. I want move this data in my OLAP database using SSIS.I can move all record from my table to OLAP database.The problem is I don't know how ...
3
votes
1answer
542 views

How can I search SQL Server for maintenance plans that execute specific tasks?

Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an ...
1
vote
0answers
242 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
1answer
80 views

Return value formatted similar to if casted as money

My client's system is very outdated, and there is nothing I can do about it. What I'm trying to achieve is to extract values always with at least 2 decimal places. E.g. From 1826.5695600000 ...