Menu
  • HOME
  • TAGS

Incremental Appending Value in SSIS

ssis

You could do the following: Add a variable to hold the value Add a script component to the dataflow Add the code below to the script task, modifying references to the correct column and variable name: int count = 0; public override void Input0_ProcessInputRow(Input0Buffer Row) { count = Row.count +...

for each loop container nested SSIS

ssis

Yes, you can create a ForEach loop nested within another ForEach loop with both applying separate object variables. Also, each object variable can be defined by one or multiple parameters (Variable Mappings) originating from field value results of SQL queries. If I understand correctly, you wish to filter the object...

Execute Data Flow Task based on condition in SSIS

sql-server,ssis

The dummy script approach: In the expressions for the two precedence constraints you evaluate a variable that is set either by the first SQL task or the script task. Now, the data will flow down one of the two data flow paths based on the value of the variable. The...

SSIS Dataflow CSV to SQL Server with DateTime2 column

sql-server,ssis

To expand on my comments since this appears to be an acceptable answer: I would handle this in SSIS with a Derived Column. It has a few major advantages: first, it uses the same parsing method as the rest of your import process so you don't have to be concerned...

Uploading Excel Report Data to Sql Server

sql,sql-server,ssis,ssis-2012

When you're in the "select source tables and views" page of the wizard click "Edit Mappings": Then select "Append rows to destination table" ...

How to specify a specific Windows Event Application log for logging from SSIS package

ssis

No. The default 'SSIS log provider for Windows Event Log' will only log to the Application log. Your alternative is to create a custom log provider....

How to scale concurrent ETL tasks up to an arbitrary number in SSIS?

concurrency,ssis,scale,etl

This is controlled by the Package Property: MaxConcurrentExecutables. The default is -1 which means machine cores x 2, and usually works well. You can also affect this by setting EngineThreads on each Data Flow Task. Here's a good summary: http://blogs.msdn.com/b/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx...

ssis swap some values in a data flow if they match a lookup table

ssis,sql-server-2008-r2,transform,lookup

You could use a lookup component and then: Set it up to Ignore Failure Values that do not match will return null for the lookup value Use a derived column expression to populate where the lookup succeeded ISNULL(Value2) ? Value : Value2 ...

SSIS Execute Package Task variable path

ssis

I recommend using Environment Variables to set the path for each environment, this links to SSIS Package Configuration which should be enabled.

SQL server force multitheading when executing SSIS package

sql-server,multithreading,sql-server-2008,ssis

There are a few options for optimizing your query to handle multiple simultaneous operations... or at least improving performance. Apply OPTION MAXDOP in your query to apply the maximum number of processors (parallelism) available with the operating system. Listed below is an example and here is a link with more...

excel datasource returning nulls with sql command

excel,excel-vba,ssis,integration

I have found out that cast and convert does not work in this context. I used CSTR(). I was getting error displaying this cell in the preview but the end product was good.

SSIS - How to modify Event Handler

visual-studio-2013,ssis

Every object at the control flow has the ability to have an Event Handler. Based on what is or isn't selected will influence the scope of Event Handler that is defaulted to. You might have created the OnError event handler on your Task or it might be on the package...

Replacing long Hypen in SSIS code (Find and Replace)

sql,ssis

NCHAR(8211) for en dash, NCHAR(8212) for em dash. INSERT INTO Product.Market.Products (ProductId, ProductSourceCd, ProductTitle) SELECT MP.ProductId, MP.ProductSourceCd], REPLACE(MP.ProductTitle, N' ' + NCHAR(8211) + N' ',N' ') FROM ... If you are unsure what the character is, convert the unicode character to an integer value: select UNICODE ('–') ...

SSIS Execute SQL Error: “ ”

sql,sql-server,ssis,sql-server-2012,ssis-2012

After wasting many hours on this, it turns out this is expected stupid behavior by SSIS. Apparently, Microsoft thinks that using a $( is only for internal variables and would NEVER be used by someone in an actual query. This would run is SSMS but fail in SSIS: SELECT 'Total:...

How do I add a column to the Lookup No Match Output in the SSIS Lookup Transformation?

ssis

I would avoid setting things up in the output columns of transformations - it can lead to some funny behaviour and can also be a pain for someone to support in future. If you're sending the "no match" rows out, adding a null, and then doing a Union All to...

SSIS: How to check if string value from one column of a table matches with string value (from the right) in a column of another table?

ssis,string-comparison

I would use an SSIS Lookup task using the Partial Cache option: https://msdn.microsoft.com/en-us/library/ms137820.aspx This will execute the specified SQL statement row-by-row. The SQL statement design would essentially be what you have coded in your question - getting SQL to do the complex join requirement. For performance reasons, I usually have...

Import custom text format without separators [closed]

c#,sql-server,regex,ssis,etl

Instead of a DataTable I'm now writing directly to SQL Server. You need to enter a connection string and the name of the SQL Table in the Insert SQL. If you are really adding that many lines I would consider using SQLCMD.EXE which comes with SQL Server. it accepts any...

SQL Server or SSIS code for incrementing

sql-server,ssis,increment

If performance is not key constraint use this declare @Id as int, @Rownumber as int = 0 declare @tempTable table (Id int, Rownumber int) declare tempCur cursor for select Id from yourtable OPEN tempCur FETCH NEXT FROM tempCur INTO @Id WHILE (@@FETCH_STATUS=0) begin declare @tempId as int select @tempId=id from...

Load data for yesterday's date and specific date

ssis,ssis-2012

Add a parameter to Package ParamDate. This parameter has to be manually provided a value(e.g. 03-21-2015). Leave it blank(NULL value) if yesterday's date has to be considered. Now define a variable inside your package VarDate Have an expression for VarDate like this - ISNULL(@[$Project::ParamDate])?DATEADD("day", -1, getdate()):@[$Project::ParamDate] I am assuming you...

WinSCP .NET assembly throwing “The winscp.exe executable was not found at location of the assembly” when installed to GAC for SSIS

ssis,gac,winscp,winscp-net

Quoting documentation on Installing the WinSCP .NET assembly: Installing The package includes the assembly itself (winscpnet.dll) and a required dependency, WinSCP executable winscp.exe. The binaries interact with each other and must be kept in the same folder for the assembly to work. In rare situations this is not possible (e.g....

Check Excel File Sheet for missing columns

vb.net,excel,ssis,excel-2010,vb.net-2010

You can use a simple SELECT to verify your file: Dim blnMissingColumns As Boolean = False Dim cmdTest As OleDbCommand = xlConnection.CreateCommand cmdTest.CommandText = "SELECT TOP 1 trayid, trayname, description, quantity FROM [Tray Header$]" Try cmdTest.ExecuteNonQuery() Catch ex As Exception If TypeOf ex Is OleDbException Then If CType(ex, OleDbException).ErrorCode =...

Execute SQL task while importing Excel file to SQL server [closed]

sql,excel,ssis

I think I have a solution. I left the SSIS solution unchanged and instead used a trigger in the SQL Server to insert the current date when the table in question is changed.

Convert or output SSIS package/job to SQL script?

sql-server,linq,entity-framework,linq-to-sql,ssis

SSIS packages are not exclusively T-SQL. They can consist of custom back-end code, file system changes, Office document creation steps, etc, to name only a few. As a result, generating the entirety of an SSIS package's work into T-SQL isn't possible, because the full breadth of it's work isn't limited...

Convert SSIS (Visual Studio 2013) to work on SSIS 2012

visual-studio-2013,ssis,sql-server-2012

The short and long answer, unfortunately, is no. If you want to develop SSIS/SSDT for SQL Server 2012, you must use the Visual Studio environment that came with 2012. Here's the long version of the background info. To build SSDT packages for SQL Server 2012, you actually use Visual Studio...

Script Task in SSIS Input0Buffer Error

ssis,vb.net-2010

The assumption made in your code is that the inputrow contains a NEW column for each OLD column. My guess is that you have added an OLD column without a corresponding NEW column. You need to pass to the component something like Column1OLD Column2OLD Column3OLD Column1NEW Column2NEW Column3NEW I suggest:...

How to map variable to column

ssis

Hi looks like you want to add extra column in target column mapping ?? If that's the case then you can use either SSIS Derived Column Transform or Use SSIS Script Transform. For simple expressions I would suggest just use derived column transform like suggested in this tutorial....

Transform Flat File without delimiters

c#,regex,text,ssis

With Regex for example: class Program { private static Regex reg = new Regex(@"COD/ID:\s(?<id>\d+)\r\nPRJ/NAME:\s(?<name>.+?)\r\nPRJ/EMAIL:\s(?<email>\[email protected]\S+?\.\S+?)\r\nPRJ/DESCRIPTION:\s(?<description>.*?)(?:\n|$)"); static void Main(string[] args) { string original = @" COD/ID: 37 PRJ/NAME: Josephy Murphy PRJ/EMAIL: [email protected] PRJ/DESCRIPTION: test37, test37, test37 ... COD/ID: 38 PRJ/NAME: Paul Newman PRJ/EMAIL: [email protected] PRJ/DESCRIPTION: test38, test38, test38 ..."; string result =...

SSIS - execute N concurrent instances of the same task

sql-server,sql-server-2008,parameters,ssis,parallel-processing

I would probably do this with a Script Component. It would contain a loop from 1 to N and call SqlCommand.BeginExecuteNonQuery which is asynchronous (ie, it returns immediately). This would give the parallelism that you require....

SSIS execute sql task get value from a query statement

ssis

As mentioned, you need to change the SQL Task to give a Result Set on a 'Single Row', you can then output that result set to a variable. From here you can use the constraints within the Control Flow to execute different tasks based upon what the outcome variable will...

Transform and copy table data

sql,ssis,sql-server-2008-r2

I recommend a third approach applying a new staging table and history table in Database B on Server B. The staging table will mostly mirror your Table B, but will not contain any constraints and will have an additional bit column defining Status. The history table will mostly mirror the...

SSIS Foreach Loop failure

sql-server,foreach,ssis

This is a known issue Microsoft is aware of: https://connect.microsoft.com/SQLServer/feedback/details/742282/ssis-2012-wont-assign-null-values-from-sql-queries-to-variables-of-type-string-inside-a-foreach-loop I would suggest to add an ISNULL(RecordID, 0) to the query as well as set an expression to the component "Load missing Orders" in order to enable it only when RecordID != 0....

visual studio version for XP users support ssis

sql-server,visual-studio,ssis

Visual Studio 2010 is the latest version that supports XP, but you will have to use have a computer running vista or later to install SQL 2012 Server though. If you want to run everything on that XP machine then you must use visual studio 2008. Strongly recommend upgrading your...

Data Flow Task with variable source

c#,excel,ssis

As a general answer, no, you can't do this with SSIS. Since you tagged this with C# however, you can use OLE to add sheets to an Excel file and add data to those sheets, http://jayadevjyothi.blogspot.com/2013/01/step-1-create-new-project.html . This can be done outside of SSIS, or if your solution needs to...

SSIS package execution succeeds but doesn't do its job

sql-server,ssis,package,sql-server-agent

I think it maybe related to permissions. Executing the SSIS package will use your security context but running it from the agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.

How to run an SSIS package having excel source on a server where excel is not installed using SQL Server Agent Job

sql,excel,ssis

Based on the discussion above, the job was referring to F drive but F is a different drive to expected when running on the server.

How to import a package from SSMS to Visual Studio for local machine?

visual-studio-2010,visual-studio,ssis,ssms

You have two options: Creating a new project in SQL Server Data Tools (SSDT) and import the project from existing catalog. Exporting the project into ispac file via SQL Server Management Studio (SSMS). For detailed steps visit http://www.mssqlgirl.com/editing-published-ssis-package-in-sql-server-2012.html...

Sorting files with the same header names using SSIS

ssis,ssis-2012

I am going to try and explain this as best I can without writing a book as this a multi stepped process that isn't too complex but, might be hard to explain with just test. My apologies but I do not have access to ssdt at the moment so I...

SSIS Package Component Intermittently Failing

sql-server,ssis

I cannot read the SSIS tasks. The reason one does things in parallel in SSIS is to gain performance by using the built in parallelizing features of both SSIS and SQL Server. I once had to process millions of rows and by doing it in parralel I got it done...

Automated file import with SSIS package

sql-server,ssis

I would say it all depends on the amount of data that would be imported into your SQL server, for large data sets (Normally 10000+ Rows) it becomes a necessity to utilize the SSIS as you would receive performance gains in your application. Here is a simple example of creating...

SSIS ScriptTask hangs on Debug

ssis

To be able to have the debugger hit your breakpoint, you should change your SSIS Project's "Run64BitRuntime" propery's value from its default True to False. But that's not enough. Because your "Script Task" is still compiled as 64bit. You should edit your Script Task with "Edit Script" and save it...

Sql code to get multiple columns out of single column

sql,sql-server,ssis,multiple-columns

Thanks all for your suggestions and solutions. I got the solution. After little experimenting, Assuming that the column will be as mentioned, I used substring and charindex to get the desired result. Though the query looks a bit big, still it worked out. I would rather wish it to be...

How to use SSIS API to read a package and determine task sequence from code (PrecedenceConstraints)

sql-server-2008,ssis

There was another object under the constraint, PrecedenceExecutable, that represents the "preceeding" object, and it also has an ID property. I'm not sure how I missed it. I just needed to look at it fresh, it seems. foreach (var precedenceConstraint in package.PrecedenceConstraints) { Microsoft.SqlServer.Dts.Runtime.TaskHost constrainedExecutable = (Microsoft.SqlServer.Dts.Runtime.TaskHost)precedenceConstraint.ConstrainedExecutable; Microsoft.SqlServer.Dts.Runtime.TaskHost precedenceExecutable =...

Distance between two zip codes using Google Maps API and a set of pairs of zip codes in Excel [closed]

sql-server,excel,api,google-maps,ssis

The Analyst Cave has a great write up on using the Google API for exactly this: http://www.analystcave.com/excel-calculate-distances-between-addresses/ Public Function GetDuration(start As String, dest As String) Dim firstVal As String, secondVal As String, lastVal As String firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" secondVal = "&destinations=" lastVal = "&mode=car&language=pl&sensor=false" Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP") URL =...

With SSIS, the flat file connection manager messes up with csv files having “,” in values in the range of thousands

csv,ssis,connection-manager

Use Text Qualifier as shown here: This will take care of the columns that have quotes inside. Sometimes it gets really bad with CSV data, and I've had to resort to script components doing some cleanup, but that's really rare....

Control flow task in SSIS stops after first one

sql,ssis

I figured it out. As SFrejofsky mentioned, having two conditional statements leading to the next "check if file exists" block is what was disallowing the package to continue. The package runs as intended after I made two changes: Switching the precedence constraint to Completion between importing file 1 and checking...

SSIS 2012 Transaction in ForEach Loop

ssis,transactions,ssis-2012,foreach-loop-container

Change the maximum error count of the Loop container and all parent containers to 3, and changed the "stop package on failure" flag to false. This should allow it to continue looping. While this should work, I don't advocate this approach. I would split the three transactions into separate containers,...

Can I prompt user for Field Mappings in SSIS package?

sql-server,ssis,etl

SSIS packages are generally headless because they typically will run as a scheduled job somewhere on a database server. That said, there are definitely ways to do this. One option that I have used is SQL Management Objects (SMO) to connect to the SQL Server Agent where the job is...

How to Parse text file using SSIS Script Component with Complex and Varied Data

c#,sql-server,regex,ssis

I would try to keep the Script component as simple as possible - just splitting the input rows into separate text files, perhaps adding Row Numbers or similar to keep track of sets of rows. I would use Strings.Split to chop each Tag C row into separate columns, e.g. all...

Can a SSIS 2012 package be deployed to SQL Server 2008 R2?

ssis,sql-server-2008-r2

Pretty much none of the MS BI stack (SSIS, SSRS, SSAS) is backward compatible, meaning you cannot develop in a newer environment and deploy to an older SQL Server. And if you think about it, it's easy to understand why: SSIS packages, SSRS Reports, and SSAS databases all have one...

SSIS Flat File Source Text Qualifier being ignored

sql-server,ssis

So I found a solution for this problem. Thanks to LukeBI answer here Create a string variable called TextQualifier and assign the value " (double quotes) Select the connection manager, and in the Properties window select 'Expressions'. See below. Click ..., add the property 'TextQualifier' and assign the variable @[User::TextQualifier]....

SQL Server 2008 SSIS SQL Provider not found

sql-server,sql-server-2008,ssis

Unsure of why the migration requires it, but creating a fresh connection manager on SQL Server 2008 R2 with an SQLOLEDB provider resulted in the ConnectionString specifying SQLOLEDB.1

Accessing parent parameters from child package SSIS 2012

visual-studio-2012,ssis

I think I found my answer and want to share it here in case somebody else needs the exact same answer: 3 parameters defined in master package level are to be mapped in execute package task editor that is used to run the 7th child package under the master. To...

Access denied SSIS w/ Parameters via xp_cmdshell

ssis,sql-server-2008-r2,xp-cmdshell

The only way that I could currently find (Without resturcturing permissions in SQL and the network was to give read/execute permission to SQL server. Not my ideal solution, but it works. Hope this helps someone...

Exporting to CSV file in SSIS from query is mixing up rows and columns

ssis

Data length shouldn't be an issue. You'd get a truncation error in your Flat File Destination if the data were longer than your defined field size. Usually when I've had things like this happen, it's because the source schema changed and I wasn't told about it. SSIS doesn't do well...

SQL Agnet,SSIS packet and Error: DB2 OLEDB not registered perhaps no 64 bit registered SSIS Packet

sql-server,ssis,agent

The driver for the 64 bit was missing, after installing it everything went fine :) .

How to do File System Task in SSIS depending on Result of Data Flow

ssis

I have used VB or C# scripts to accomplish this myself. Since you do not want to use scripts I would recommend using a different path for the project to flow. Have your success path lead to moving the file to completed and failure path lead to moving the file...

Parent-Child parameters in SSIS 2012

sql,sql-server,ssis

Yes. By default when you create a new project for SQL Server 2012/2014/2016, it is going to use the Project Deployment Model. You will right click on the project and select Package Deployment Model and then you will be able to do all of the things you were doing in...

pivot not working in SSIS

sql,sql-server,ssis

That statement is equivalent to: select listid, max(case when [order] = 1 then val end) as [1] from gmt_listsvals group by listid ...

SSIS “Failed to lock variable” error in File System Task within foreach Loop

variables,foreach,ssis,source,ssis-2012

The issue turned out to be that the parameter for the import file path was being used where the User::FilePath was needed in the source Connection Manager.

RESTful API call in SSIS package

vb.net,ssis

This is Javscript Object Notation AKA JSON and you need to deserialise it. The problem is that using SSIS is tricky with third party tools that are popular (and fast) but VB.Net actually has a built in class to serialise and deserialise JSON called JavaScriptSerializer First add a Reference to...

Converting to SSIS 2012 Project Deployment Model

sql-server,visual-studio-2012,ssis,ssis-2012

Because the packages have those connection managers in them, they will show up on this screen. That doesn't mean they are being used though. If they were only ever used for getting your configurations, they should be unused now but the wizard won't actually delete them. In your two packages,...

Using SSIS, how to insert new data from Excel sheet into my existing SQL Server table

sql-server,excel,ssis

i can pick up the trader and FYear from that excel sheet by using OLE DB Source but i don't actually know how to insert that data to each column of that table like above Use your existing method to populate two SSIS variables with the trader and FYYear...

SSIS package data flow tasks report

reporting-services,ssis,etl

For your first question, we use user variables in SSIS to log the number of rows processed by each step along with the package name and execution id. You can then run reports on the history table, and if any of the executions have a large variance in the...

How to use Oracle connection in SSIS?

oracle,ssis,oledb,oracle12c

You need to install 32-bit ODAC 12c for Windows x86.

SSIS 2012 connection manager red arrow

ssis

the answer was to take connection manager ofline and connection manager worked for me in this format Data Source=xxx;User ID=xxx;Password=xxx;Initial Catalog=xxx;Provider=SQLNCLI11.1;Auto Translate=False;

SSIS error connecting to Excel Source

reporting-services,ssis,sql-server-2012-datatools

Installed the following (32 bit) https://www.microsoft.com/en-us/download/details.aspx?id=13255...

Visual Studio 2013 opens sln in text editor

visual-studio-2013,tfs,ssis

I ran a repair install for the Team Explorer for Visual Studio 2013 and the problem has been resolved.

Assign Dimension value to SSIS variable

sql-server-2008,ssis,ssas,ssas-2008,data-cube

You can use a query-scoped calculated measure to create the alias. As an example, I'm using the AdventureWorks cube. The following query would give me the last child in the calendar hierarchy for the member I provided. SELECT [Date].[Calendar].[All Periods].[CY 2014].[H1 CY 2014].lastchild on 0 FROM [Adventure Works] As you...

SSIS, Foreach Loop, Event Handler when it writes 0 records due to file containing zero records

ssis,event-handling

Create an integer variable. Place a rowcount task between the source and destination of the dataflow, and map the step to the variable. Create an email task and connect the dataflow task to it with a "Success" constraint and an expression evaluation. Insert the following value into the expression:...

How does not | (Bitwise OR) work in SQL Server/SSIS 2012?

sql-server,ssis,bitwise-operators,bitwise-or

I don't have a whole lot of experience with that flavor of SQL, but a bitwise OR is not the same thing as an OR clause in the WHERE statement. The bitwise OR will OR each bit of the integer together to produce a new integer For example, the numbers...

Archive file in Script task using c# in ssis

ssis

If I understand you correctly, you are trying to append your sDateSuffix to your file name but before the .csv extension. If so, I think you just need to use Path.GetFileNameWithoutExtension(): String sDateSuffix = DateTime.Now.ToString("yyyyMMddhhmmss"); String ArchiveDir = Dts.Variables["V_ArchiveDir"].Value.ToString(); String V_FileName = Dts.Variables["V_FileName"].Value.ToString(); //Assuming V_FileName = "abc_x1.csv", FileName will be...

Changing variable scope in SSIS programatically

c#,ssis

You'd need to access the Variables collection on the specific Task/Container The following code creates an SSIS package that has an OnError event handler at the package level. On that event handler, eh, I add the variable sSourceObjectId string path = @"C:\ssisdata\p1.dtsx"; Application app = new Application(); Package pkg =...

Error by schedule ssis package in sql server 2008 agent

sql-server,sql-server-2008,ssis

Don't use the mapped drive use the UNC path instead, something like \servername\filepath. For the Job to access network folders I think you need to set up a proxy with a domain account credentials and that is configured to run jobs of type SQL Server Integration Services Packages....

How do I resolve a 0x80040e14 and 0xC0202071 in SSIS?

sql,visual-studio-2013,ssis,sql-server-2014

I have an answer for that one actually. You will need to set your connection string as an expression and either hard code or parametrize your user and password. The password is encrypted based on information from the machine that it is encrypted on. Once you move that encrypted property...

BIDS 2012 SQL Log provider: The connection xxx is not found

sql-server,visual-studio-2012,ssis,bids

A Project connection manager is different from a Package level connection manager. After converting to a Project level CM, go into the Logging screen and click the dropdown for the connection manager. As you can see below, even though I have a SportsData connection manager defined, it's not an option...

how to convert Oracle geometry to SQL GEOMETRY

sql-server,oracle,ssis,geometry

I'd bet dollars to donuts that you can use SSIS for this, with a little coercion. Specifically, well-known text (WKT) is the standard representation for geospatial data. On the Oracle side, you can use Get_WKT() against your data to return the WKT. Then on the SQL side, you can use...

SSIS take oracle variable and use them in a ssis resulset

sql-server,oracle,ssis,resultset

Try to use SSIS variables instead of Oracle. Create 3 variables, ie. s5, s6, s6 with intiger type. Create Execute Sql Tasks for every query, set result set to single row and assign results sets to previously created variables. Use filled variables in SSIS. Of course, reformat your queries to:...

How to remove a row in SSIS when a value is not a date

ssis

If the only difference between the records is the empty date value then the SSIS expression could be LEN(DateCol)>0?1:0 So if it outputs 1 you take the data downstream on the split.

Does Amazon RDS for SQL Server support SSIS?

sql-server,ssis,amazon-rds

I thought it was pretty clear when reading this: Amazon RDS currently does not support the following SQL Server features: The ability to run Reporting, Analysis, Integration, or Master Data Services on the same server as the DB instance. If you need to do this, we recommend that you either...

Create Derived Column in SSIS that resets on Change of ID

sql-server,ssis,derived

To achieve my end goal, I imported the data from the DB2 as I had been doing, but then I created a view on the data to use the Row_Number() function within SQL Server: Row_Number() Over (Partition By DCACCT Order By DCACCT) [PCMT], My next step possibly could have been...

Aggregate Transformation vs Sort (remove Duplicate) in SSIS

ssis,data-warehouse,business-intelligence,bids

My first choice would always be to correct this in my source query if possible. I realise that isn't always an option, but for the sake of completeness for future readers: I would first check whether I had a problem in my source query that was creating duplicates. Whenever a...

validation error when project parameter sensitive property is set to true in SSIS 2012

visual-studio-2012,ssis,sql-server-2012,catalog

It is erroring because you are trying to touch a Parameter that is marked as Sensitive. You cannot use the "old" approach for configuring connection managers. For the project deployment model and Connection managers, in the SSISDB, you right click on the project and select Configure. There is where you...

SSIS 2008 - Get Current Date in Variables

sql-server,ssis

Help me understand how GETDATE() doesn't exist in the SSIS Expression language for 2008. That said, I find that using the system variable @[System::StartTime] preferable to GET_DATE(). StartTime provides a consistent point in time for the duration of a package. It will always be whenever the package begins execution. Contrast...

Updating Rows In a Table That Was Used to Import From SSIS

sql-server,ssis,package

I suggest you use this pattern: FIRST update the source table and mark 1000 records as 'ready to extract', i.e. set them to 1 Next, select all records from your source table that are ready to extract Lastly, if the extract was succesful, update the 1's (ready to extract) to...

string Does not contain definition for Where, C# in SSIS Package

c#,ssis

The Where method of the string class, is an extension method and you must provide it's namespace. So you should add this namespace: using System.Linq; ...

Using SSIS tools?

ssis

Now I wonder if I have to use one data flow for each table Most developers do it this way, mostly for ease of logging, but also having multiple sources and targets in a single data flow task means it multi-threads, and you lose control over it. In my...

Data streams in case of Merge

sql-server,ssis,sql-server-2012,sql-azure,sql-server-2014

The answer to your questions a, b and c (if you're using SSIS transformation components in SSIS) is essentially “yes, all new data and existing data required for transformation will flow into SSIS instance, and the resulting merged data will flow out of SSIS instance to the target server”. More...

SSIS error - value violated the schema's constraint for the column

ssis,excel-2007

I beleive the limit for a single Excel column is actually 255 characters. I would test for longer values upstream in the SSIS flow and either split out those rows or truncate long data using a Derived Column.

Is it possible to recover the SSIS packages that are deployed in SQL Server?

sql,sql-server,ssis,ssis-2012

If you have access to the server itself, you or somebody with the appropriate permissions can login to the server that is running SQL Server Integration Services, from there you can open SSMS, connect to Integration Services, and export any package that has been deployed to that server.

ssis - No value given for one or more required parameters

sql-server,ssis

You need to map 3 parameters for that query. If you want to map only one, you can do this: declare @str varchar(max) = ? SELECT SUBSTRING(@str, 8, 2) + SUBSTRING(@str, 10, 2) + '20' + SUBSTRING(@str, 10, 2) AS File_Date ...

Using an Integration services project within a Windows Form or WPF app?

c#,sql,excel,ssis

I have a windows form created almost exactly like this but its way too much code to show. It connects to an excel you select, connects to sql db, pulls sql db into excel, modifies excel data. Edit: Posted on github for anyone to use. https://github.com/tshoemake/ProductivityReport...

How to incorporate a text qualifier in a file name used as a variable?

c#,csv,ssis

Well, based on the questions you have answered, this ought to do what you want to do: public void SomeMethodInYourCodeSnippet() { string[] lines; using (StreamReader sr = new StreamReader(filesToProcess[x, 0])) { //Read the input file in memory and close after done string fileText = sr.ReadToEnd(); lines = fileText.Split(Convert.ToString(System.Environment.NewLine).ToCharArray()); sr.Close(); //...

Project consistency check failed. has a different ProtectionLevel than the project

ssis,sql-server-2012

Solution was found by the help of billinkc in the comments above and by following this solution: Protection level changed mid project - now project won't build Problem was that the ProtectionLevel's were not the same. Some were 1 and others 2, while in the properties menu they were all...

Reading XML using XmlDocument

c#,xml,ssis,xml-parsing,xmldocument

To operate on all child nodes of <entry> node, you can stop your XPath at /atom:entry. Then inside the loop, select each child nodes as you want, for example : ...... String xpath = "atom:feed/atom:entry"; XmlNodeList nodes2 = xml.SelectNodes(xpath, nsmgr); foreach (XmlNode node in nodes2) { var title = node.SelectSingleNode("./atom:title",...