SQL Server 2012 :: SSIS Package Worked And Then Stopped?
Jun 22, 2014
I create a package that has a foreach loop container, inside the FE container the is a dataflow, script task and a file system task, on the outside of the FE loop I have a SQL execute SQL task. I had it working then it just stopped. Been looking to see why but can't see why, is there something I missed. I changed the onError script task event handler, to propagate = false, because the last file in the source directory is being written to until 11:59 pm and is locked, and I get error file is being used by another process. I am at a loss as to why it would just stop working.
I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75
The Table:
/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/ IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL DROP TABLE [dbo].[#TestProject2] GO CREATE TABLE [dbo].[#TestProject2]( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[Code] ....
The data:
--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #TestProject2 ON INSERT INTO #TestProject2 ("ID","PersonID", "PercentLoad","MonthID") SELECT 1,123456,0,1 UNION ALL
I was assigned a project to make a backup of data from 3 lists in a sharepoint with the objective of using that data to do reports on it using Report Builder.
I was abble to do the downloads using the add in to do downloads Sharepoint List Source and Destination "plug in" in SSIS, now the issue is how to update the backup table only with updated and new rows. Daniel
Using server 2012 on local machine, I created an SSIS package that will execute in integrated services and Visual Studio solution but will not work when creating a job. Other solutions work well except when exporting data. The program pulls data from query and exports into .csv file. The messages I get are -
Data flow task 1:error:destination- Stage.csv failed the pre-execute phase and returned code 0xC020200E and Data flow task 1:error:Cannot open the datafile "pathStage.csv".
Version- Microsoft SQL Server Management Studio11.0.3128.0 Microsoft Analysis Services Client Tools11.0.3128.0 Microsoft Data Access Components (MDAC)6.1.7601.17514 Microsoft MSXML3.0 6.0 Microsoft Internet Explorer9.11.9600.17041 Microsoft .NET Framework4.0.30319.18444 Operating System6.1.7601
I created a DTS Package. It worked fine if I execute the DTS from DTS designer. It failed after I right clicked to Schedule Package, and than right clicked on the job to Start Job from Management - SQL Server Agnet - Jobs. When I clicked View Job History, the error was "The job failed. The last step to run was step 1 (DTSPackageName).
Why did it fail from scheduled DTS? Thanks. DanYeung
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
I had a Send email task linked to my Sequence Containers in my package and it was working fine. Everytime the container fails it would send an email to myself.
At some point all Failure constraints stopped working. Failure constraints work if I add brand new tasks, but with the existing tasks, they don't work. The Task which fails, turns red and execution stops. Next failure task is not executed.
I am not sure what triggered it to stop working. I cannot get anything on the log
This might be kinda long so please bear with me. I have a SSIS package that creates 2 other packages to pull in data from our as400. To do this I have a SQL 2005 table setup with as400 table names and columns and the matching sql tables names (I reuse the column names). I also use a flag to determine if the table name should be added to the data retreival that way if there is a need I can just run the procedure for a specific as400 table instead of all of them. The main SSIS package loops through the table to create the package to pull in the as400 data and put it into staging database. Then the main package creates another package that pushes the data from the "staging" sql to the "live" tables. This has been working great for about 5 months now. Yesterday I went to create a new job that would allow me to run the same jobs just with making change to the flags so that only certain tables are pulled in at certain times. This works very well in our test environment. For some reason it appears that it has corrupted the production packages. I can not get them to run at all now. The untouched jobs all of a sudden quit. I changed the new job to run as operating system and not SSIS and it says that it can not find it. I can see the file, I am using the SSIS on the file system. I can open the file and see all of the properties, I can copy and paste the file. I just can not run it. I was able to finally get the package to run using the command line but now there is something odd that it is sending to the as400. The kicker to me is that nothing changed with the SSIS package and nothing changed with the existing jobs I just added new ones and now it does not work. Could this be some corruption? Anyone else seen similar behaviors?
I have a SQL 2012 Enterprise edition HADR cluster with 2 nodes and my event log on the second node (current primary for the AG) is being filled with these messages
The Service Broker endpoint is in disabled or stopped state
I wasn't able to find too much on the issue. I did find this one open Microsoft connect bug report from back in April 2013 [URL] .....
O/S: Windows 2012 SQL Server 2012 v11.0.3412 Currently 1 AG with 1 DB
We have SQL Server 2012 running on Windows 2008 Server. We would like to use a SSIS Package to generate a text file and then secure copy it to a vendor's ftp site. Would it be best to use an FTP Task or Execute Process Task (to call the batch file)? Would I need to install some software like winscp or does the Windows O/S has some secure copy or ftp programs that may be used?
We had a scenario where we used to run the Process from front end thru application. on the back ground the the process call the SP & from there it calls the SSIS package then again to SP.
after SSIS package ran succesfully it will be updated on a table with sucess then call the SP & deletes the entry from the other table but in one scenario Package was success but the entry was not getting deleted as the process takes almost 2-3 hours loading 60 millions records. but the process was running in SQL 2008 but once we upgraded to SQL 2012 its not working for one application. its not returning any error as timeout also. we tried changing the server level setting for remote query time out also to 0 but no luck .
We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. I have a SSIS Package which connects to a FTP Site and downloads a file. Then it truncates a table and loads the file data into a table. This package works okay when executed from within VSS and SSMS (In SSISDB, right click on the package and execute). However, when I execute it as a Job it does not run and appears to be failing on the first task which is the FTP Task. SQL job step - Type: SSIS Package; Run as: SQL Server Agent Service Account (domain account called playuser); Authentication: Windows Authentication.In the All Executions Standard Report for the SSISDB Catalog, it only says: FTP Download File: Errors: There were errors during task validation.
Is this because my domain account does not have access to the FTP Site?Is this where I need to come up with a Proxy Account with Credentials?Do I need to set up a SQL Server Login (Proxy Account) with the same username being used in the FTP Batch file?
FTP Commands in a batch file: username password cd omb asc get STRMASTER quit
I have a bat file that kicks off a master package, which kicks off about 300 child packages. My bat file is working correctly, however there seems to be issues executing packages in 64 bit mode.
So my question is...
Is there a way to specify in the bat file to execute packages in 32 bit mode and not 64 bit?
bat file: "C:Program FilesMicrosoft SQL Server110DTSBinnDTExec.exe" /f "E:MasterPackage.dtsx"
I have been handed a datawarehouse environment to look at however I cannot find the ssis packages (.dtsx).
I see that the packages have been deployed to SSISDB but I can't find the files to load into Datatools.
Once the files have been deployed is it possible to remove the package files? Is there a way of retro'ing the deployed packages out of SQL so I can view them in Datatools?
We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.
We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.
Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'".
I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication).
We require to convert a list of SPs in to SSIS packages. Most of the SPs do the below steps:
mainly our store procedure r to have compare the present date to past date , and comparing emp id between the files and also some joins. updating table r take place.
I am working on FTP TASK in SSIS Package. i have to get files from FTP that file names are like 20141110.txt. i want to download any particular date file from ftp. How to i set expression in Remote path?
Currently I am running a SSIS package scheduled daily at 7 A.M. It expects two feed files from two different folders. The first step in my package will rename the input files in those folders to names which the package can understand. I have created two variables in my package to read the files with those file names. I used these variables in connection managers.
If any of these folders doesn't have input files when the package runs, the package will fail.how to make the package the run successfully even there is no input feed?
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
I have a data flow task in which I have a ADO NET source and OLE DB Destination. I have in the ADO NET source a sql command which pulls all the columns in a table. My requirement is to ignore a particular column,say column99. I opened advanced editor and deleted the mapping between the external and output columns for column99. I had also set the Error and Truncation to "Ignore Failure" for column99. I had also mapped the destination column to <Ignore> in OLD DB destination.
But this still throws the error-
Description: The ADO NET Source was unable to process the data. Field table-column99 missing an escape character for a quote.Unable to update PK WHERE clause.Error processing data batch.
I have a package on the default instance which runs and completes successfully. When that package is moved to the same SQL server, but a different instance, running under the same service account, it fails. The error is below with some specific stuff removed:
Delete Data from Level 1:Error: Executing the query "-- Variable to capture FileID's DECLARE @DeleteFil..." failed with the following error: "The DELETE permission was denied on the object '[name removed]', database '[]', schema '[]'.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This makes me think that the package under the non-default instance ends up running under a different security context.
I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
I'm after running into something in SSIS 2012 that I fail to grasp.
I have a package that provides a service to other packages. In order to provide that service it needs 4 parameters provided by the caller. So naturally I'm thinking I make those 4 parameters 'required'.
The caller uses Execute package task and provides the 4 parameters on the parameter mapping tab.
Yet the packages fails with the error message that one or more required parameters weren't provided.
We have a large number of SSISDB packages running happily, connecting to our SQL Servers using ADO.Net or Sql Native Client, making their connection using NTLM. (We don't have our SQL Server SPNs correctly configured to support Kerberos).
The SSISDB packages are hosted on and run on a dedicated SQL server, different to the SQL Servers they are connecting to.
Very occasionally, the connection attempt is made using Kerberos instead of NTLM, and the connection attempt to sql server fails. (This is going by the Windows Security event log, which reveals a Kerberos login - a successful one at the Windows level - at the precise time that the calling agent job is informed of a connection timeout and fails, approx 23 seconds after the job starts).
The correct configuration of our SPNs is something we may wish to look into for security best practice, and would of course fix this. However, that may not be my decision to make.
I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.
The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.
I've tried returning the values as:
Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update' and Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'
I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).
