I've developed SSIS package and able to Launch and Execute dtsx package using Web Service/Console application. If I run it from my local it has no issues. If I ask my client to run this web service it is throwing error "Unable to connect to remote server".
If I ask my client to directly run the package itself they get following error "AcquireConnection method call to the connection manager failed"...
Even after my client having SQL Server, Integrations Service and BIDS installed on his machine he gets the above error. How to set this up. SQL Sever Agent is not an option for us.
I want to achieve the following in (SSIS/SSDT for SQL 2012) -
I have a generic SSIS package which simply sends out email notifications using SMTP email task (this package is within its own project, and has project level input parameters).
I need to be able to call this package in the Event handler section of every package (numbering in about less than 60) that we have. These packages are within their own respective projects.
I thought I could use the "execute package task", but it turns out , using this, I cannot call a package that is part of some other project. I also cannot call a package that is stored in the CATALOG. Is there any way I can do this ?
When I call the child package , I should be able to send in parameters like - error information and package name of the Parent package.
I created a SSIS package which loads CSV into database. The package is called in a C# console application which is set as scheduled task in a server. The problem I having is that the package hangs during validation stage: "Truncation may occur due to inserting data from data flow column "Reading Type" with a length of 50 to database column "ReadingType" with a length of 2."There is no problem loading same data from development machine.
We are executing a SSIS package using a xp_cmdshell command in a SP as shown below. This package does consumes time to execute almost 90 minutes and does get executed successfully too. But the strange thing is we don't get the result in @result variable just because somehow the next sql statement after the below highlighted statement doesn't get executed at all. After checking execution stats for the SP using the query attached below we observed that somehow the SP vanishes out of the execution stats for the server.
I'm executing Oracle procedure, which has three OUTPUT parameters and returns results in table type variable. i should not use ODBC, MSDAORA providers to call the procedure. So I'm planning to using Oracle OLEDB provider. I'm able to execute the procedure successfully, but when i do check (while dr.Read()) its not returning any records. But I know as per stored procedure results, it should return 66 records.
Dim conn As New OleDbConnection Dim cmd As New OleDbCommand Dim dr As OleDbDataReader Dim QSQL As String
I am using SQL Server 2012 SP1. I have built an SSIS package that imports flat file data from various files to SQL Server. I have got it to do everything I want it to do when things are going well, and am now on what I want it to do when it encounters a failure executing specific tasks and containers. For example, I have a Foreach Loop container that executes a dedicated stored procedure for each csv file in the target folder. If any of the store procedures fail to run for any reason I want to carry out certain actions.
For the most part I think I will be fine using the Event Handlers. What I can't seem to find is how to tell the package to stop executing on a Failure event after carrying out the actions defined by the relevant Event Handler. Or, perhaps it isn't necessary as that would be the default behaviour on a failure?
I'm currently looking at refactoring an existing, large SSIS 2012 implementation that consists of about 55 projects and 360+ packages. The ETL framework that is in use has a "main" control package that reads from a database table and determines which packages are ready to execute (based on some dependency logic) and then uses an Execute Process task within a loop that calls dtexec with the arguments: /C start Dtexec /SQL "Some Package Path" /SERVER "someserver"
This design allows the loop to execute a package and then immediately iterate because it doesn't wait for the package to respond (aka complete with a failure or success) so it can quickly kick off as many packages are ready to execute. A SQL Agent job is used to call this package every few minutes so that it can pick up any packages that have had their dependencies satisfied since the last execution and kick those off.It's a a clever design but has some problems such as decentralized exception handling (since the parent package is unaware of what is happening in the "asynchronous" dtexec calls.My biggest concern is that by executing packages, not with the Execute Package Task but with the Execute Process Task, and spinning up many dtexecs, the framework is not leveraging SSIS's ability to handle threading, memory consumption, etc. across all running packages and executables because it is simply unaware of them. It's essentially like using an Execute Package Task with the ExecuteOutOfProcess property set to true.
I have a sql agent job which runs the Masterpackage, Master package has aroung 35 child packages calling in it. Issue with this Master package is it running upto 30 child packages and I get success message on job. It is not executing packages beyond 30 what could be the problem. I disabled the all the 30 child packages and ran it again it ran those other 5 child packges without any problem.
I have to execute a .bat file on a remote server (that is used to stop and start services of an appl). The remote server doesn't have SSIS,SSMS installed. I want to create a package on my desktop the uses Execute process task and execute the .bat file on the remote server and then schedule it using the SSMS.
I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).
So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )
I'm trying to execute an SSIS package from an ASP.NET web page. Using some code from http://msdn2.microsoft.com/en-us/library/ms403355.aspx I have managed to get this working when executing a package that contains a stored procedure, however it does not work when there is an Analysis Services cube to build.
Here is my code for the webservice
Code Snippet
Public Class SCCBuildDW
Inherits System.Web.Services.WebService
' LaunchPackage Method Parameters:
' 1. sourceType: file, sql, dts
' 2. sourceLocation: file system folder, (none), logical folder
' 3. packageName: for file system, ".dtsx" extension is appended
_
Public Function LaunchPackage( _
ByVal sourceType As String, _
ByVal sourceLocation As String, _
ByVal packageName As String) As Integer 'DTSExecResult
I'm using windows authentication and impersonating an identity. The identity has access to the analysis services role but the package still fails to run.
if there is any way to accurately size a single server using SSIS. The server will be a virtual machine. The data being loaded will be approximately 200 MB per load with loading to a 150 GB database on a separate server.
I have scheduled SSIS package through Sql Agent and when I right click on job start job as step package runs successfully but when I schedule job it dosent run.
Can anyone help regarding the SQL server integration Services(SSIS), ETL We have requirement like this: We have Live Database( LIVE_DB ) and Reports Database (REP_DB) I want to trasfer the few tables data from LIVE_DB into the REP_DB for end of the day using SSIS If any new records are added, updated or deleted in LIVE_DB, these should reflect in the REP_DB, Our requirement is not to delete the old data, we should append or delete or insert the new transaction data in REP_DB.
Thanks in advance, if anyone help me in resolving this issue.
I have a maintenance plan which consist db full backup and log backup ( in two subplans), I execute both on SQL agent, and both failed.The DB Log Backup : DB FULL BACKUP LOG:
I have a ssis package with an oledb connection using windows authentication. i want to understand when i promote this package to the server and add it to a job, then a user login to the server with sql server authentication and run this job. Which/what windows authentication this package gonna to use to connect to the server ?
I have installed VS community edition 2013 on a widows 7 PC. When I try to create a ssis package the ssis tool bar comes up empty.It just says "loading" on the panel .
Win 7 SP1 x64 PC. I installed SQL Server 2014 Dev Edition + Visual Studio 2015.
I'd like to create some basic ETL SSIS packages, and I worked very comfortably in 2008R2.
For 2014, I started with this tutorial:[URL]However, it says to go to Start->All Programs->Microsoft SQL Server->SQL Server Data Tools.
I did explicitly install SSDT when I installed VS2015. I also installed it separately. I see SSDT listed in Programs, and SSIS is running according to SQL Server Config Manager, and Services. Half of Microsoft's docs seem to be 2012 era, which is a shame because 2014 is out and it's nearly 2016...
how do I get to the GUI where I can design ETL packages?
We'll be using 2014 enterprise to populate some excel spreadsheets. We may not have the option of using ssrs so here is the question.
The excel spreadsheets have some pretty fancy heading structures, multiple tabs and in a few cases graphs that i'm sure are dependent on data sitting somewhere in the spreadsheet. The heading have variable info in them (eg FYxx where xx is a parameterized fiscal year, month names etc). Sometimes those headings have sub headings (with variable info) and so on.
Generally speaking how (if at all) do folks deal with this kind of excel challenge when limited to using ssis? I don't know if templates are going to be a good idea. I don't know if i'll be looking at a fair amount of c# etc code behind the scenes. I can be more specific depending on the feedback.
This is part of a conversion from 2 of the larger BI and statistical tools out there to the MS sql stack. Obviously with some shortcuts that we may wish we didn't take.
we have a table with xml column. This column has a large xml data . I am trying to use ssis to import xml from sql column (table a) to destination (another table).
steps which i did in ssis:
1. execute sql task:
fetch the xml column by query and store "full result set" into an object variable.
2. foreach loop:
select Ado enumerator option and select variable which has reset set of execute sql task. In variable mapping selected a new variable of type string.
when I run package I get below error:
"Error: ForEach Variable Mapping number 1 to variable "User::variable" cannot be applied".
Have Visual Studio 2008 R2 with SP 2 installed. Due to a merger we now have a MySQL database that we need to update from SSIS. Everything works except for the table insert or update. Would upgrading to SP 3 or SP 4 maybe useful with that?
We have installed the latest driver from MySQL. Have tried the ADO.Net and ODBC drivers with similar results when we try to update the database.
I have a ETL ( SSIS ) Process in which i am loading around 150 tables in each run. ( Truncate and Insert ). I have four packages each from different sources. ( Each package loads different tables and different numbers )These are run on weekly basis one after the other. Each package is taking around 60 to 90 minutes each. Now i want to track the progress of the ETL on my front End application.
We want this in two ways.
First Way : I need to show the user how much percent of ETL Process is completed
Second Way : I need to show the No of tables completed and how many rows have been completed in the ongoing table ( which is in process )
Im newto SSIS. I want to develop package for data validation.
FirstName
1. Mandatory field checking: if Null, reject the record 2. If field length > 50, then reject the record
SSN
1. If field length > 12, then reject the record 2. If SSN is not in valid format, issue warning and process rhe record without SSN value. 3. Valid format: 9 digit numeric values should present after striping off all non-numeric characters. 4. Only send 9 digits to MDM
Like these i have 30 rules. And I have to shop the error msg if the validation fails like "Mandatory feild is missing".
I'm unable to find a solution to this truncation error on google.This happens only on one field which has comments. The offending Excel row/column has text that was entered in two lines i.e they entered the data and pressed "enter" and wrote a new line in the same row.Im using an Excel file source in SSIS and an OLEDB Destination (SQL Server) but one column keeps erroring out and I have tried to do the following:
1) Change output column width in advanced editor (still errors)
2) Data conversion tool between the source and destination (still errors)
I have created SSIS package where it generates .txt output file. But my client added new requirement that generated .txt has to be convert into excel or CSV (Depends on client selection on portal where it stores values in table to know in which format they requires).
