New answers tagged

1

The Import/Export wizard builds an SSIS package under the covers but the package the wizard builds only performs the E and L of ETL (Extract, Transform, Load). What you're looking for is T because you needed to transform this weird data into something more manageable. For the weird header row, you can always specify that there is no header row and the ...


2

Firstly, the IF/IIF you are looking for is the "?" Conditional Operator ? Conditional One way to implement the Derived Column would be to parse the string like this TOKENCOUNT( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), "." ) == 1 ? "" : REVERSE( TOKEN( TOKEN( REVERSE( RTRIM( name ) ), "\\", 1), ".", 1) ) In this case, "name" is the column that holds ...


2

This isn't in the expression language (it's T-SQL) but you should be able to use this as either a pattern or to pull the data from SQL (if that's where it is). CREATE TABLE #test (Col1 varchar(100)) INSERT INTO #test VALUES ('\\Server\Share\named resource'), ('\\Server\Share\this.looks.like.a.file_but.it.isnt\its_a_directory'), ...


3

I was not able to reproduce your error (although I only tested with one table in the mix). An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility. Set up I created a simplistic table --- a key, a value and an empty field that will be populated, via ...


1

The following will show you if the triggers are enabled. If they are, they will fire, assuming the DML meets the trigger requirements. You should run this while the SSIS package is running. SELECT TableName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name) , TriggerName = t.name , t.is_disabled FROM sys.triggers t INNER JOIN sys.objects o ON ...


0

We are using Linked server to connect to Oracle through OLE DB. the using openquery command to pull data down to SQL server. This will help


1

Since you are getting the error when saving and not executing the package, you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012. Your user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server). You need to map the login to an SSISDB user and configure ...


4

I can watch the database size grow from 2GB to 12GB (max space available) where it runs out of space and returns the following message: The issue is either you're simply running out of disk space and the physical file cannot grow to complete the transaction, or you have a limit on the SQL file growth settings and the physical file cannot grow more ...


0

If you're still stuck, be sure to check that: Integrations Services is installed on the Instance you are working with If you have a previous SQL version installed - check that you are using the matching version of SSMS to connect to your Instance SSIS for various reasons is version specific and offers limited to no backwards/forward compatibility. It is ...


0

I had a similar issue where I still got the same "access denied" error, even after editing the DCOM permissions as described in the linked MSDN post. In my case, the solution was to add the domain account in question to the local Distributed COM Users group on the SSIS host server. The DCOM permissions were still necessary, just not (in my case) ...


2

It looks like your installation of Visual Studio 2015 registered itself as the editor for script task instead of what it was before. What you need to do is make sure the right editor is used again to edit script components. You could get away with re-registering the correct components for use with script tasks, but it could be needed to remove some ...


2

For posterity I got this working via the following: Altering the sproc called by the users (Admin.RunImport) to 'Execute as' the account used by the SQL Service. The Sql service account (an AD Managed Service account) is modified to have permissions to execute the Admin sproc allowing the use of execute as above The Sql service account is modified to have ...


3

I had similar issue before with my SQL Server 2012 Enterprise Edition production box. Even though I would make sure everything was refreshed under SSIS packages before I deployed to the production box, when it executed on the production box it would just not work, saying object doesn't exists. I also had all the validation turned on, but that didn't help. ...


4

The biggest number you can fit into numeric(5,2) is 999.99. Therefore 123.556... is fine, but 1234.556... is too big. You can find the rules for truncation, rounding, and errors during implicit conversions in the documentation: CAST and CONVERT (Transact-SQL) I don't know SQL-server that well, so I don’t know if you can configure the behaviour. One idea ...



Top 50 recent answers are included