Extending SSIS Container?
Jul 24, 2006
Has anyone investigated extending any of the SSIS Container classes?
I have been looking into it because we'd like to add a set of standard logging calls on events, standard startup procedures, etc. on any package that we execute.
I've been looking into the Sequence Container, For-Each, etc. They are all sealed classes. I'm not sure why MS has sealed them.
We're currently thinking of implementing our own version of the Sequence Container -- we'd really like to be able to extend the functionality of a standard container class, but we don't want to have to implement the actual container class itself.
Any insight appreciated.
View 5 Replies
ADVERTISEMENT
Dec 8, 2006
I am confused with this topic extentded ssis.What i am diong here is creating a pipeline component and i am trying to use this component and transfer data.
What is happening here is that i create it but i dont find it when i try to include this(component) in the toolbox ->Choose items.I find my project with in tools->Attach process.
Is this a problem with anyone too....
please help.
View 6 Replies
View Related
Sep 7, 2006
Everything I've read says that custom data flow components are built by inheriting from the Microsoft.SqlServer.Dts.Pipeline.PipelineComponent class.
But the stock components such as the Derived Column data flow transformation must each be implemented by their own class. So how do I base my custom components on those classes? The documentation for the PipelineComponent class doesn't list any such subclasses.
View 1 Replies
View Related
Mar 12, 2008
Hi,
I am using For each loop container in my SSIS package which will loop through more than one input file in the input folder.
All the input files in the input folder are picked by one by one. All the input files in the input folder are executed with the same ExecutionInstanceGUID.
Now, my requirement is to get a new ExecutionInstanceGUID during the execution of each input file in the For each loop container. My package only contains the For each loop container with some logic inside that.
Please let me know if any one can help me for the same.
Regards,
Sriram.
View 6 Replies
View Related
Aug 13, 2007
WHAT IS THE USE OF THE FOR LOOP CONTINER
PLEASE GIVE ME THE EXAMPLE
REGARDS
KOTI
View 5 Replies
View Related
May 24, 2006
Could someone send me any links or information on how to loop through an ADO.NET dataset in SSIS? I need step by step information please.
Thank you,
Shiva
View 32 Replies
View Related
Jul 10, 2006
Hello,
I would like to modify "Files" attribute of the Foreach Loop of type File
Enumerator. This attribute is used to set the mask (for example *.txt) to
specify which files to include in the selection. I need to be able to change
this mask dynamically depending on package global variable. Is this possible?
Thank you!
Michael
View 3 Replies
View Related
Nov 28, 2005
I am creating a staging database in which I am loading required tables from 2 different sources.
I have 30 different tables to load from source 1 and 10 different tables from source 2.
This is the way I am doing, in Control flow task I am using Sequence container and in that I included the data flow task, the data flow task
has source OLD DB connection from where I select the table and then destination OLE DB connection where I load the data.
So for 30 tables I have one Sequence container with 30 different data flow task and each data flow task has OLE DB source and OLD DB destination.
I wanted to find out if this is the efficient way to do, or if there is any other way to do this.
And for source 2 shall I put in another package or shall I use the same package with different sequence container and follow the same steps
as for Source 1 tables.
Please advice.
Thanks,
View 19 Replies
View Related
Aug 15, 2006
Hi -
I've seen variations on this question in this forum, but none of the suggestions work for me, so any ideas would be greatly appreciated!
I have a SSIS package whose function is to monitor a directory for flat files and distribute the information in these files into a SQL 2005 DB for further processing later. I've configured Transactions for the package as serializable and supported, and I have a ForEach container set to serializable and required. All SQL and Data Flow tasks take place within this container. I have a single Connection Manager to pull in the flat input file, and another for the connection to the SQL DB. This setup will function, and roll back if necessary, but I take a huge hit on the continual establishment of connections to the DB. If I set the retainsameconnection property on the SQL DB Connection manager, I get an error when executing my first Data Flow task to move data from the flat input file to working tables within the DB: 0xC001A004 - Incompatible transaction context was specified for a retained connection. If I turn off Transaction support, the retainsameconnection property works as expected.
I've been running Profiler Traces, and can verify that no connection has been established to the SQL Server prior to execution of this task, so it seems like the context should be OK. I would welcome any feedback, suggestions or alternatives.
Thanks,
Jim SweeneySLC
View 2 Replies
View Related
Mar 24, 2014
why when I make a change to a foreach loop container in a SSIS package it does not save?
I am trying to change the base file name and if I change it through either the edit or properties windows, the changes does not save. Fore example i change the Files: or File Spec: from oldfile*.txt to newfilename*.txt and when I save and og bak into the edit area to confirm the change, the Files: section shows as "oldfile*.txt.
View 4 Replies
View Related
Jul 25, 2007
Has anyone ever seen this issue?
If i place a loop container inside a sequence container the connectors within the loop container disappear.... is this a bug - will the loop tasks still run in the correct order?? or will they fire off willy nilly??
View 4 Replies
View Related
Oct 24, 2007
I am getting the following error when trying to load multiple excel files using for each loop container in SSIS, I tried to put the quotes in several different ways but still can't get rid of this error. I was able to successfully load single excel file, but when I use the for each loop container that's when I am having problems. Any help is greatly appreciated. Thx.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package1 [Connection manager "SourceConnectionExcel"]: The connection string components cannot contain unquoted semicolons. If the value must contain a semicolon, enclose the entire value in quotes. This error occurs when values in the connection string contain unquoted semicolons, such as the InitialCatalog property.
Error at Package1: The result of the expression ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::Folder] + @[User::file] + ";Extended Properties="Excel 8.0;HDR=NO";"
" on property "ExcelFilePath" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
View 8 Replies
View Related
Jun 9, 2006
Here's the set up:
ForEach Loop Container:
Collection: Foreach File Enumerator
Variable Mappings: Variable = User::DailyFile, Index = 0
Execute SQL Taks:
Connection: OLEDB
ResultSet: None
SQLStatement: EXEC spGetFile ?
ParameterMapping: VariableName = USER::DailyFile, Direction = Input, DataType=VARCHAR, Parameter = 0
This works great it iterates through a file and looks at all the files checks to see if they have been loaded into the db table, if not it loads the file.
My spGetFile has RETURN 1 if a file is loaded and RETURN 0 if the file is not loaded.
Now I add a new variable:
Step 1: add to Foreach Loop Container
ForEach Loop Container Name = Return, Scope = ForEachLoop, Data Type= Int32 Value=0
Step 2: Add to Execute SQL Task:
VariableName = User:Return, Direction = ReturnValue, DataType = Long, ParameterName =1
This produces the following error:
~~"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.~~
I tried with an ADO.Net connection ... works until I add the ReturnValue parameter ...
Any ideas??
Thanks
View 4 Replies
View Related
May 18, 2015
Can we drag and drop Existing SSIS package into for each loop container ?
View 3 Replies
View Related
Jul 9, 2015
in a my SSIS 2012 pkg I'm using a Foreach ADO Enumerator container that reads an object variable in order to get an id value.This identifier is passed as an input parameter to an Execute SQL task to update an Oracle table: if this task fails the id is written on a SQL Server table. After the Execute SQL task execution, with success or failure, the flow go to another task in the container.
When an error occurs for the update on Oracle table, each tasks inside the container are executed but the container fails and the loop ends.I'd like to complete the entire loop respect to the identifiers present in the object variable also if the update operation on Oracle table goes in error.
View 5 Replies
View Related
Sep 8, 2015
I am having a challenge to pass the filename variable from SSIS Foreach Loop Container to SQL store procedure. I have an "Execute SQL Task" inside "Foreach Loop Container" which will receive the input filename parameter passed by the Foreach Loop Container. The store procedure command line is defined as "EXEC sp_mySQLStoreProcedure ?" within the "Execute SQL Task". The Foreach loop container will gather the filenames from File System then put it in the filename variable one by one. I would like to pass the filename variable as the input parameter to the sp_mySQLStoreProcedure in the "Execute SQL Task". How can I connect the variable and the store procedure so that it will process all the input files gathered by the Foreach Loop Container?
View 4 Replies
View Related
May 6, 2015
In order to update an Oracle table target from a SQL Server table source I need to use a Foreach Loop Container, so I can loop on the rows of the SQL Server table source. This source table has two columns: the old identifier to update and the new identifier to apply. I must use the value of the old identifier to filter the Oracle rows to update, while the new identifier is the new value to assign to the filtered old identifier.
I already know how to use the Foreach Loop Container when it is necessary to loop on an unique column of a table/view (using an object variable, using a Foreach ADO enumerator, etc.), but I need to loop on two columns.
View 8 Replies
View Related
Apr 21, 2008
Hello
I am trying to use Foreach loop container - Foreach File Enumerator
Is there easy way to retrieve enumerator configuration folder from variable - so I can easily move package from server to server?
I would like to have name of the folder we getting files from retrieved from variable by using this control
Thank you in advance
Armine Bell
View 3 Replies
View Related
Jun 18, 1999
Is there a way to add functions to SQL (other than stored proceedures) to emulate functions in other systems?
I would like to create a FOXPRO type OCCURS function:
Select * FROM Table1 WHERE OCCURS('Y', FLAGS) > 3
If FLAGS is a VARCHAR(7) and equals "YYYYNNN" then OCCURS would return 4 and the row would return. The database that I am
accessing has >165,000,000 rows and >500 columns per row so you see that I need all the speed I can get!
'
' This is a VB function which works.
' Can this be compiled into a DLL and
' somehow made available to SQL?
'
Public Function OCCURS(expr1 As String, expr2 As String) As Integer
Dim i As Integer
Dim count As Integer
For i = 1 To Len(expr2)
count = count + Abs((Mid(expr2, i, 1) = expr1))
Next
OCCURS = count
End Function
View 2 Replies
View Related
Feb 14, 2001
I seem to have a problem extending the log portion of tempdb onto a log device used by another user defined database. Is this a known problem?
Thanks
View 2 Replies
View Related
Sep 21, 2005
I need some help forming a query
I have 3 Tables...
Table1: Users
Fields: UserID Int
Username Varchar(50)
Table2: User_Categories
Fields: User_CatID Int
User_ID Int
Table3: Categories
Fields: CatID Int
Catname Varchar(100)
Now consider Table 3 has the following values
CatID | Catname
1 | cat1
2 | cat2
3 | cat3
4 | cat4
5 | cat5
6 | cat6
7 | cat7
8 | cat8
9 | cat9
Now I need to select users who fall into the categories with CatID 5, 1, 3. Also users can fall into more than one category.
The users should be sorted by the 1st the users who fall under 5, then users who fall under 1 and then the users who fall under 3 and then finally by the userID in the descending order.
SELECT userid, username
FROM Users, User_Categories
WHERE userid=user_catid AND user_catID IN (5, 1, 3)
ORDER BY userid DESC;
How do I extend this to get the above needed result? Any help will be highly appreciated.
View 13 Replies
View Related
Jun 16, 2008
Hi,
If anyone can help me extending the following query I'd be really grateful! :)
I have 4 tables, Product, RawProduct, RawProductPriceHistory and RawProductPromotionalHistory. The relationship is a Product can have multiple RawProducts (one for every retailer the product is stocked in), and the PriceHistory and PromotionalHistory tables keep track of when the RawProduct's price changes, and when it comes on or off of promotion.
I have the following SP to return prices for the given Product for the given Date.
I need to extend the SP so that it also returns details from the RawProductPromotionalHistory table if a PromotionalHistory entry occurs for the passed date (@Date). We determine whether a PromotionalHistory exists by whether the passed @Date >= RawProductPromotionalHistory.StartDateTime and @Date <= RawProductPromotionalHistory.EndDateTime. The EndDateTime can also be NULL - indicating an ongoing promotion.
Here is the schema of the RawProductPromotionalHistory table:
http://www.boltfile.com/directdownload/rawproductpromotionalhistory.jpg
And here is the current SP:
ALTER PROCEDURE [dbo].[GetProductPricesForDate]
-- Add the parameters for the stored procedure here
@Date datetime,
@ProductId uniqueidentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
ph.[DateTime], p.Name AS 'ProductName', ph.UnitPrice, rp.RawProductId, r.LogoFileName AS 'ShopLogoFileName', r.ShopId, r.Name as 'ShopName'
FROM
Shops r
INNER JOIN
RawProducts rp
ON
rp.[ShopId]=r.ShopId
INNER JOIN
Products p
ON
p.[ProductId] = rp.ProductId
INNER JOIN
RawProductPriceHistory ph
ON
ph.[RawProductId] = rp.RawProductId
INNER JOIN
(SELECT RawProductId,MAX([DateTime]) as maxdate
FROM
RawProductPriceHistory
WHERE
[DateTime]<=@Date
GROUP BY
RawProductId
)temp
ON
ph.RawProductId=temp.RawProductId
AND
ph.[DateTime]=temp.maxdate
WHERE
p.ProductId = @ProductId
GROUP BY
ph.[DateTime], p.Name, r.Name, ph.UnitPrice, r.ShopId, rp.RawProductId, r.LogoFileName
ORDER BY
ph.UnitPrice DESC
END
If anyone can help me extend the query I'd be really grateful!
thanks in advance,
dan
View 2 Replies
View Related
Jun 9, 2008
Hi, I am trying to write a stored proc that would allow word proximity. I would like to define the proximity of the word so that I could ask for a word1 within 10 of word2.
I am wondering if I could get some suggestions on how to go about writing this? Should I put this in a stored proc? Should I just cursor through each row and then cursor through each word after word1?
I hope that this is clear... Thanks in advance.
View 4 Replies
View Related
Apr 29, 2008
How can I create a source extension that reads a text file and returns a XML file? I would like to have an xml File as output, because the information is already normalized.
If I extend the class €œPipelineComponent€? I can just define IDTSExternalMetadataColumn90 as output. And I do not want to have columns. I would like to return a xml file
Is it possible to achieve this?
View 1 Replies
View Related
Jul 18, 2006
Can anyone provide any direction in extending existing SSIS components with a custom component, if it's possible at all. In some cases there are just slight bits of functionality missing that I think I could add in myself. But, I'm not much of a programmer and need a bit of help in the declaration of the component in my own custom component.
Thanks.
View 1 Replies
View Related
Apr 18, 2006
Hi,
I need to write an add-in, that when installed it will add a virtual folder to the project tree (in which I can later add other stuff).
The problem is even more complicated since the project I want to extend is an SSIS project (SQL Server Integration Services), and it doesn't let you customize the project structure even from the project explorer itself (can't add folders).
I appreciate any help on this.
Thanks.
View 5 Replies
View Related
Jul 8, 2004
Hi All
We have requirement in Reports. When ever report is sent to file share using Reporting Services File Share extension than details of that file should be saved in database.
How can we achieve this functionality? What is the best possible way of achieving this?
Please let me know the solution.
Thanks in advance
Rehan Mustafa Khan
HCL Technologies
Noida,India
View 1 Replies
View Related
Apr 17, 2006
Hi,
I've had requests for a version of my product ViEmu, a VS.NET 2003-VS2005 add-in, which will work within SQL Server 2005 Management Studio. I had a look at the Mgmt Studio, and it seems to be a version of the VS2005 environment customized for the product. There is a registry hive along the line of the one in Visual Studio, but located in HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90ToolsShell.
I looked for any hint online, but it seems largely undocumented.
I tried plugging my software by writing the right 'Packages' and 'AutoloadPackages' keys, and running "SqlWb /setup", which seems to do something. Nothing happened afterwards, anyway, the DLL doesn't seem to be loaded into the process. I tried using the '/log' option, but an error message comes up (which doesn't describe the /setup option either).
Is there a way to load a VS package into SQL Server 2005 Mgmt Studio? Is this a problem with the PLK? Is it unsupported? Has anybody been successful in such a feat? Would it be ok if I hack some way, no matter how obscure? Is this deliberately turned off, as in the Express editions?
Best regards and thanks in advance,
- J
----------------------------------------------------
ViEmu - vi/vim emulation for Visual Studio
View 1 Replies
View Related
Jun 7, 2007
Is there a way to extend the number of rows that will be returned so that Reporting services doesn't display such a large number of pages for the users to page through? It would be easier for them to "wheel mouse" through a long page on the screen.
Anyone out there have any thoughts on how this might be accomplished?
Thanks!
Travis
View 1 Replies
View Related
May 18, 2004
Hi,
I was wondering if anyone has extended the standard CDOSYS Mail Stored Procedure (SP) to allow it to send the results of a query as an attachment?
I have set up a SP for CDOSYS Mail as outlined in the following link:
http://support.microsoft.com/default.aspx?id=kb;de;312839&sd=tech
Currently I am using the old SQL Mail (xp_SendMail). But due to the problems with losing the MAPI connection and other limitations, I have been forced to find another solution. Using SQL Mail, I was able to add a query parameter and attach the results of the query to the email. I need to have the same functionality in CDOSYS Mail
Thanks,
Kim
View 3 Replies
View Related
Feb 16, 2007
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages.
The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select.
Is this possible ?
What other approaches could I take to building about 30 re-usable functions?
View 7 Replies
View Related
Apr 18, 2008
I have an execute sql task that uses the following query: (modified for this thread but normally its going to return all records in the table)
Code Snippet
SELECT
'PrimaryID' AS 'Parameter/name',
PrimaryID AS 'Parameter/value',
'LastName' AS 'Parameter/name',
LastName AS 'Parameter/value'
FROM ParkingUpload
WHERE primaryid = '1234' or primaryid = '4321'
FOR XML PATH('T2Method')
The output of this query looks like this: (I know normally this would be a bad xml file, but its needed for my purpose)
Code Snippet
<T2Method>
<Parameter>
<name>PrimaryID</name>
<value>1234</value>
<name>LastName</name>
<value>Abiola</value>
</Parameter>
</T2Method>
<T2Method>
<Parameter>
<name>PrimaryID</name>
<value>4321</value>
<name>LastName</name>
<value>Aboud</value>
</Parameter>
</T2Method>
That xml is stored in a user variable in the Execute SQL Task. I need to take that user variable and send out that xml to a webservice. The web service is expecting one record at a time(a new record is defined by the <T2Method> node.
So question is, does my approach seem fine so far? I was wondering if my next step should be to put this variable in a For Each Container and send out one record at a time to the webservice. If so, how would I go about doing that?
If anyone else has a different suggestion as how to approach this, or be able to help with the For Each portion (I am brand new to SSIS), I would really appreciate it.
View 5 Replies
View Related
