I am trying to figure out how to loop a list of connection strings in an SSIS package. MSDN suggests I can have an Execute SQL task generate a result set into a variable and use a For Each task to loop the results, but it's none too clear to me exactly how to create this. So I have a table with a single nvarchar column with a list of Sq...
I'm getting this error when running an SSIS package through SQL Agent Failed to acquire connection "ORACLE ADO.NET". Connection may not be configured correctly or you may not have the right permissions on this connection. When I log on as the SQL Agent User and run the ssis package directly it is fine. When I then execute it through the...
Hi I have an ssis package that uses excel files. I am using the Run As with an admin account. when I call it from a batch file it runs fine "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\SOME_SSIS_PACKAGE" /SERVER "MYSERVER,PORT" /DECRYPT "Password" /VALIDATE /REPORTING E when I schedule it as a job, it...
I'm currently attemping to display the progress of an SSIS package that's running on the server by implementing a callback in WCF. This is working to a point; basically the OperationContext.Current is returning null after a few listener events have already happened (roughly 30 seconds after the initial SSIS package execute call). Here's...
We have multi GB files coming every day to be loaded to SQL Server 2005 instance via SSIS packages. To load those large data files to SQL Server asynchronously, we built multi threaded .NET application. This application uses DTSExec command line executable to invoke SSIS packages. It also instantiate 5 queues and 5 streams for each queue...
Hello, I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development work for a project, and in most ways I would like to use SSIS for the project. The one area that my research and googling is leaving...
I am moving data into a DW using SQL Server SSIS and have the following SQL to populate one dimension SELECT DISTINCT cast (datename(dw,datum) as varchar(10)) as veckodag FROM XXXXX.dbo.Bought as I have VARCHAR in the target column I need to CAST/CONVERT Question how to convert Unicode to Varchar? I get *> Validation error. Datu...
In SSIS 2008, the ability to reference other class libraries has been added. Is there anyway to resolve the references to a non-GAC location when deployed to a non-developer environment? For instance, if I add a reference to c:\lib\mylib.dll in an SSIS script task then deploy that package to another machine. Can I simply place mylib.dll ...
Good Afternoon All, I have written an SSIS 2005 package that contains a conditional split. The expression evaluates a variable, strDepts, as Department (fieldname) != User::strDepts. The variable strDepts contains 6 department numbers separated by commas. When I run the package, the conditional split does not work correctly. No reco...
Hi All, I've an SSIS package that runs a stored proc for exporting to an excel file. Everything worked like a champ until I needed to a do a bit of rewriting on the stored proc. The proc now takes about 1 minute to run and the exported columns are different, so my problems are the following; 1) SSIS complains when I hit the preview ...
I have a datareader source firing some sql which takes longer than 30 seconds to run, so it's timing out. I can not find a command timeout property on the datareader source component, or the data flow task. Is there some way to set a longer command timeout in SSIS? ...
I have a situation where I am trying to lookup a value in one table based on values in another table, using a BETWEEN operator and not an = operator. In one table, I have a value "EffectiveDate". I want to get a Weight number from another table, but the other table has two fields: "Inception" and "Termination". What I want to do is ex...
I have a server that has a SQL Agent job setup to run a two part datawarehouse build and Analysis Services cube build. The job ran successfully until there was a problem with Management Studio and .NET framework 2.0 SP1 was re-installed. The first step in the SQL Agent job is unable to kick off the SSIS package. If the Agent job is start...
Hi, I need to execute 29 ssis packages. So planning to create one master package which will execute all these packages. I don't know how to implement this. Can you please explain in brief. Thanks in Advance ! ...
I want to know how to import .mdb (MS Office 07) file into Sql server 2008 by SSIS. I need to run this ETL packet in such a way that it checks for duplicates, and if any doesnt re-insert them, but only inserts new records. If someone has a tutorial link or can explain here in steps would be very helpful ...
Hi, I am executing a parent package which will excute the child packages. In the parent package I have established connection to child packages using file connection manager. It works fine in my loacl machine. When I move to development server, it is throwing error that it couldn't find the child package. Actually it resides in the same...
Hello All, I have written a SSIS 2005 script task that connects to Active Directory and reads user accountnames to store in database. I was able to successfully test this on my local system by executing dtexec.exe cmd. However, when I try the same on the server, the ldap query times out. The same ldap query as a console application ...
Hi I need to populate a table B by selecting a from table A but for every single record selected from Table A for some of the fields I need to pass it to a WCF webservice and do some tasks. Can somebody show me an example on how to invoke a WCF webservice for every single row fetched from a database through a script component A step b...
Hello all I got a situation where need to maintain version controlling on SSIS packages. Subversion is in place for other .net apps. Now want to move ssis packages to subversion. got ssis solutions as below: ProjectA solution dtsx1 dtsx2 dtsx3 ProjectB solution dtsx1 dtsx2 dtsx3 what are the best practices to do this. How c...
Hello, I have a requirement to "flatten" a resultset in an SSIS package - this is, take a result set consisting of a number of rows and produce a single string with the concatenated values. The result set is returned to me via a stored procedure that for a number of reasons I cannot change. I do not want to duplicate the logic into an...