New answers tagged

0

It turns out, I had done something really stupid. My SSIS package, as it turns out was perfect, I had named the transactions and set "RetainSameConnection" to true. Begin/commit/rollback commands were all specified correctly. My problem was that one of the stored procedures called as part of the SSIS package had a unnamed transaction in it, causing SQL ...


2

On your server, there is a file named MsDtsSrvr.ini.xml which is located at C:\Program Files\Microsoft SQL Server\120\DTS\Binn (for sql server 2014). When I examined this file it stated "..\packages" for the location, so I went to the DTS folder then into the packages folder and the files were there. Depending on your version, architecture, and installation ...


0

If this is a simple Excel file load, you can just use the Import Wizard to create an SSIS package for you (available by right clicking the Database, and selecting Tasks -> Import Data) and even save that package for future use. If this is a process where you need to take data from an Excel file, transform it, then load it, then yes a custom SSIS package ...


1

Have you tried using dynamic sql? Something like this: declare @fieldLogID nvarchar(256) = ? declare @accountTypes int = ? declare @sql nvarchar(1000) = '' SET @sql = 'select fl.[Version] ,fl.ObjectID ,[FieldLogID] ,Cast(fl.WorkStartDateTime as date) as WorkStartDate ,Cast(fl.WorkStartDateTime as time) as ...


0

Your "IN" statement will not work unless the @fieldLogID variable is of "TABLE" type and not single value type. You can either: Switch the "IN" to a LIKE" and reverse the two, as such (this is a bit lazy and taxing on resources, at LIKE is sort of expensive): WHERE @fieldLogID LIKE '%' + fleja.FieldLog_FieldLogID + '%' Or (and my preferred method) you ...


0

For SQL 2008R2, you should be able to install SSMS and BIDS on your Win10 machine, I am actually working that way right now. Also, if you have SQL 2008R2 Developer Edition, you should be able to all the SQL components on a Win10 machine. I have all of the SQL components installed on a Win10 machine (database, olap, ssrs, integration services).


0

Have you tried to give permission to Domain1\NewUser to MSDB(db_owner for tests)? Here you have a great article about the SSIS Proxy users: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ Especially excerpt: EXEC msdb.dbo.sp_update_proxy @proxy_name = N'proxy_name', @enabled = 1


-2

I've recently discovered the same problem. It seems to be a problem when using Native Client 10.0 (even in SQL2008 R2 SP3). Install a later Native client, eg. 11. Or use the older "Microsoft OLE DB Provider for SQL Server".


1

If users are not accessing the 2016 Instance itself, there's no need to licence them for CALs to it, just need a licence for itself for production purposes - Server or (in this case, avoid) Core. It is unusual that you would use a Server licence without CALs, Microsoft would probably be interested on an audit... but as you note, your users are on 2014 CALs ...


2

You could use the nullif function: select nullif(test, 0) as test from ... It's standard SQL, but I'm not sure whether it is supported in ssis.


0

I would not assume it will have huge consequences to performance. Handling this is what Varchar(Max) is for and it's very unlikely to have any impact. Regarding your specific question about alternatives however, the only one is to use sparse column sets. This allows you to pack a lot of potential but usually null columns into one actual XML column. Then ...


0

I solved the truncation fiasco by Casting every output field first in my stored procedure, then continue with my code. This way I have full control and know my lengths. Then I can match them in my SSIS references. IF 1 = 0 BEGIN SELECT CAST(NULL AS varchar(50)) AS InsurancePlan, CAST(NULL AS varchar(50)) AS PolicyNumber, CAST(NULL AS date)...


0

This simple query on MSDB should get you started: SELECT s2.subsystem AS [TypeOfJobStep]-- when subsystem = 'SSIS' then 'SSIS job step' , s2.command AS [JobStepCommand] , s.name AS [JobName] FROM dbo.sysjobs AS s JOIN dbo.sysjobsteps AS s2 ON s2.job_id = s.job_id You can identify SSRS subscriptions because their name will be alternating ...



Top 50 recent answers are included