Random File And Script Component
May 27, 2008
guys, I'm trying to create a SSIS package to extract a file from a source database (OLEDB source) using a script component(VB or C#) or a variable.I have hundreds of files(datasets) sent to me daily by several clients in the format date/filename/client_name. the package will read the client_name at random and load its data to the corresponding client destination database (OLEDB destination) using a a conditional split:
if client_name is client1
OLEDB_destination1 <- client1
else
if client client_name is client2
OLEDB_destination2 <- client2
if client_name is client3
OLEDB_destination3 <- client3
thanks in advance for your help.
View 10 Replies
ADVERTISEMENT
Aug 13, 2007
I have a text file that come from our client that is Column deliminated by ~ and row deliminated by {CR}{LF}.
There is a comment field that appearently is not cleaned up and has {CR}{LF} within the comment field.
I am new to SSIS and I'm wondering if there is a way to detect and correct the bad rows?
example file formet:
ORDERID~DATE~Comment~Address
1~2/3/2007~Some Comment~1234 oak st
2~2/3/2007~Some messed
up comment~345 oak st.
3~2/3/2007~Another comment~3214 asdf blvd.
Thank you.
View 8 Replies
View Related
Oct 26, 2007
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
View 7 Replies
View Related
Jun 2, 2007
On my company's website, we have a quote of the day. I would like to be able to type a hundred or so quotes into a mdf file. Then, I would like to have code that randomly selects one of the quotes every day and posts it.
....What I want is very similar to the "Image Of the Day" section on many websites.
Any ideas?
View 3 Replies
View Related
Jan 23, 2007
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
View 4 Replies
View Related
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
Mar 12, 2008
Hello,
I get errors during import process from flat files to sql table (random missing rows) when I have more files to load through a "for each loop" cycle.
If one of the files is not present (because not yet generated by an other process) many of the rows present in the next file are skipped during import operation. This happens even if the "maximumErrorCount" is set to 10000.
The error reported is Warning: 0x8020200F at Import File Bolle , Source_Bolle [1]: There is a partial row at the end of the file
Sql 2005 has Service Pack 2 installed.
Can some one help me?
Thanks and regards
View 5 Replies
View Related
Aug 3, 2015
I'm importing comma-delimited text files into a SQL table. The data imports in a seemingly random order. One time I import and the lines appear one way and the next time I import they import another way.
Is there a way to force the text files to import in the same order the data is found in the file?
View 10 Replies
View Related
Oct 14, 2004
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Help is greatly appreciated.
View 1 Replies
View Related
Oct 17, 2007
My process checks values in certain files in a "Source" folder against a table in a database. If these values do not match, those files are moved to a "Fail" folder and if they are validated then they are moved to a "Success" folder. My issue is caused by the fact that these files (which are notepad text files) are not being moved over to the success folder for the following error:
"The process cannot access the file '\Server1c$Source239598.TXT' because it is being used by another process."
I have checked whether these files were actually being used by another process but even cutting off users from the directory and even copied a test file into the folder, the issue will still pop up and for random files.
I've tried using two different components to do the same thing as the "Move" component --- "Copy files from source to destination" and then a separate component "Delete file from Source folder" which is essentially a move. The issue persists on the Delete process because it sees that the file is still being used by some other "imaginary" process it seems to me.
I've tried upping the MaxErrorCount on the Delete and even using the Move components to try to ignore this issue but the package still fails. Are there any other ways to get around this problem?
View 5 Replies
View Related
Jan 31, 2007
Hello,
I apologize in advance if this seems like a relative easy answer, however, I can't find it anywhere, and I can't figure it out personally, I am relatively new to SSIS and C#.
I am attempting to write in C# a simple program, where I am taking a table from an OLE DB Source and transfering it to a comma delimited flat file.
I have been trying to work through samples and other methods of help, but I continue to get stuck, and this is my latest problem.
When trying to excecute I get the error: No column was sepcified to allow the component to advance through the file, for my flat file destination.
I know what this means, I just do not know how to fix it.
Below is my code so far. I highly doubt my code is the best it can get for this type of example, so if you see anything that I do not need in the code for it to perform what I want it to perform, please let me know.
(The code to excecute this package is in a different file).
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace Microsoft.SqlServer.Dts.Samples
{
class Program
{
static void Main(string[] args)
{
// Create a package and add a Data Flow task.
Package package = new Package();
Executable e = package.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
// Create Application
Application app = new Application();
// Add an OLE DB connection manager to the package.
ConnectionManager conMgr = package.Connections.Add("OLEDB");
conMgr.ConnectionString = "Data Source=ROSIE\ROSIE2005;" +
"Initial Catalog=AdventureWorks;Provider=SQLNCLI;" +
"Integrated Security=SSPI;Auto Translate=false;";
conMgr.Name = "SSIS Connection Manager for OLE DB";
conMgr.Description = "OLE DB connection to the " +
"AdventureWorks database.";
// Create and configure an OLE DB source component.
IDTSComponentMetaData90 source =
dataFlowTask.ComponentMetaDataCollection.New();
source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Create the design-time instance of the source.
CManagedComponentWrapper srcDesignTime = source.Instantiate();
// The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties();
// Assign the connection manager.
source.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr);
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand",
"Select * from HumanResources.EmployeePayHistory");
srcDesignTime.SetComponentProperty("OpenRowset", "[AdventureWorks].[HumanResources].[EmployeePayHistory");
// Need to set the ConnectionManagerID
if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManagerID =
conMgr.ID;
source.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr);
}
// Connect to the data source,
// and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Add an flat file source connection manager to the package.
ConnectionManager conMgr2 = package.Connections.Add("FlatFile");
conMgr2.ConnectionString = "C:\Documents and Settings\ddoorn" +
"\My Documents\Visual Studio 2005\Projects\" +
"DennisSampleProgram1\EmployeePayHistory.txt";
conMgr2.Name = "SSIS Connection Manager for Flat File";
conMgr2.Description = "Flat File Destination Connection";
// Create Destination Component
IDTSComponentMetaData90 destination =
dataFlowTask.ComponentMetaDataCollection.New();
destination.Name = "Flat File Destination";
destination.ComponentClassID =
"DTSAdapter.FlatFileDestination.1";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Assign the connection manager.
destination.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr2);
// Set Custom Properties
destDesignTime.SetComponentProperty("Overwrite", true);
// Assignment an ID to the ConnectionManager
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManagerID =
conMgr2.ID;
destination.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(conMgr2);
}
// Create the path from source to destination.
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
destination.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput90 input = destination.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn90 vColumn
in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
destDesignTime.SetUsageType(
input.ID, vInput, vColumn.LineageID,
DTSUsageType.UT_READONLY);
}
//map external metadata to the inputcolumn
//int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn =
input.ExternalMetadataColumnCollection.New();
//(IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];
exMetaColumn.CodePage = inputColumn.CodePage;
exMetaColumn.DataType = inputColumn.DataType;
exMetaColumn.Length = inputColumn.Length;
exMetaColumn.Name = inputColumn.Name;
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);
}
// Verify that the columns have been added to the input.
// This is only really required for debugging purposes
Console.WriteLine("Below are the columns that have been added " +
"to the input. Press Enter to Verify");
foreach (IDTSInputColumn90 inputColumn in
destination.InputCollection[0].InputColumnCollection)
{
Console.WriteLine(inputColumn.Name);
}
Console.Read();
// Connect to the data source,
// and then update the metadata for the source.
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
// Save Package to XML
app.SaveToXml("C:\Documents and Settings\ddoorn\My Documents\" +
"Visual Studio 2005\Projects\DennisSampleProgram1\" +
"DennisSampleProgram1\DennisSampleProject1.xml",
package, null);
} // main
} // program
} // namespace
View 3 Replies
View Related
Jun 29, 2007
Hi, I have a flat file source going into a script component which manipulates some values and puts those values in a flat file destination. What I now need to do is total a column within the script component and add that value as a row to the destination. At the moment data from source looks something like
ABC, 3
DEF, 5
FGH, 6
I have a variable in script component holding the running total I now want to add that to destination as a row e.g.
ABC, 3
DEF, 5
FGH, 6
Total, 14
This is probably very easy but I don't now how to add the Total row via the component. At the moment all rows are manipulated in the ProcessInputRow sub of component and output column values are assigned here. How do I add the Total row as I need to do this once last input row is complete.
Thanks
View 4 Replies
View Related
Jun 23, 1999
Does anyone have any examples of using VB to automate a DTSPumpTask to import and transform a flat file. How do you use the dtsffile.dll OLE DB provider for an ADO connection or command object? I would like to create a task to transform a flat file and be able to pass in various parameters to make the load flexible. If possible respond to ScottMiles@Epotec.com
Thanks for any help.
View 1 Replies
View Related
Jan 7, 2008
I have created a Script Component in my Data Flow that basically reads records from an SQL Table and outputs 3 rows for each input row. My problem is that the Script is outputing a blank row at the beginning of the file. I have requested no headers for the delimited file. I know it is the script because my data viewer from the table doesn't show this additional line where the data viewer from my script and the file has the additional blank line.
Any help would be greatly appreciated
View 2 Replies
View Related
Jul 6, 2007
The standard flat file connection component does not handle flat files with different column counts on different rows. Since that's the type of file I have to read, I like to create a modified version of the flat file connection manager that can do this. I have found some info in how to create a connection manager. But I can't find anything that tels me how to build one that would be recognized by the flat file source dataflow component.
So how do you build an connection manager that can used by the standard flat file source component? Anyone ever did this or knows were to get the docs?
I know my problem is also solvable by reading the row into a single column and then using a script component to split them but that is not the solution I want.
View 1 Replies
View Related
Mar 8, 2007
Hi there,
after we've upgraded to SP2 my flat-file-source component hangs in the validation phase.
normally at 50% or 66%.
i see no activity what-so-ever cpu-wise/memory-wise.
have no idea how to fix it.
(sorry if i am posting this twice, couldn't see my previous post)
View 1 Replies
View Related
Jul 11, 2006
After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.
On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"
The readonly User: variables that start with gs can be read in the PreExecute method
The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow
The xml code is adapted from an idea in Donald Farmers book
enjoy
Dave
Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat!
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
'In addition to using the Imports System.Xml statement a reference must be added to the
'System.Xml assembly (Select Project-Add Reference from IDE)
Dim xWriter As XmlTextWriter
Dim OutputFileType As String '.csv or .xml
Public Overrides Sub PreExecute()
'Read Only variables
Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid'
Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
Dim fileName As String = gsPickUp & "" & gsPickUpFilename
fileName = fileName & (Format(Now(), "yyMMdd").ToString)
'MsgBox(fileName)
OutputFileType = gsPickUpFileExtn
If OutputFileType = ".csv" Then
fileName = fileName & gsPickUpFileExtn
sw = New StreamWriter(fileName) 'connection to dest file
'Header records
sw.Write(gsRunMode)
sw.Write(Environment.NewLine) ' end of line
sw.Write(gsMemoText)
sw.Write(Environment.NewLine)
sw.Write(gsStatementText)
sw.Write(Environment.NewLine)
sw.Write(Environment.NewLine) 'Spacer
End If
If OutputFileType = ".xml" Then
fileName = fileName & gsPickUpFileExtn
'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
'xWriter.WriteStartDocument()
'xWriter.WriteComment("Customer file parsed using script")
'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteComment("Customer file parsed using script")
xWriter.WriteStartElement("x", "customer", "http://some.org/name")
xWriter.WriteAttributeString("FileName", fileName)
End If
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
If OutputFileType = ".csv" Then
Dim delim As String = ","
sw.Write(Row.ProjectID.ToString + delim)
sw.Write(Row.TransactionRefNum.ToString + delim)
sw.Write(Row.CustomerNum.ToString + delim)
sw.Write(Row.AccountNum.ToString + delim)
sw.Write(Environment.NewLine) ' end of line
sw.Flush() 'send the stream to file
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("CUSTOMER")
xWriter.WriteStartElement("ProjectID")
xWriter.WriteString(Row.ProjectID.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("TransactionRefNum")
xWriter.WriteString(Row.TransactionRefNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("CustomerNum")
xWriter.WriteString(Row.CustomerNum.ToString)
xWriter.WriteEndElement()
xWriter.WriteStartElement("AccountNum")
xWriter.WriteString(Row.AccountNum.ToString)
xWriter.WriteEndElement()
End If
End Sub
Public Overrides Sub PostExecute()
If OutputFileType = ".csv" Then
'Create the trailer
sw.Write(Environment.NewLine) ' blank line
sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible
sw.Write(Environment.NewLine)
sw.Flush() 'send the stream to file
'Close file
sw.Close()
End If
If OutputFileType = ".xml" Then
xWriter.WriteStartElement("RecordCount")
xWriter.WriteString(Me.Variables.giSuccessCount.ToString)
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End If
End Sub
End Class
View 6 Replies
View Related
Feb 13, 2008
At our business we are getting a lot of PDF documents that are being hand keyed into a database. Has anyone heard ior know of a SSIS Data Flow Source component that I coud use to read thos documents into a data stream (?) and process?
View 5 Replies
View Related
Apr 5, 2007
Hi all,
I have created a dts (.dtsx file). which gets the data from my log file and insert into my sql 2005 table. In dts i am using 'Flat File Source' and 'OLE DB Destination' (becoz i need to acess my db remotely). When i execute this through 'SQL Server Business Intelligence Development Studio' it's working fine but when i execute this through my c# code, it throws me an error 'The product level is insufficient for component Flat File Source (1)'.
My c# code :
string strLocalPath = @"d:FTPServiceMyPackage.dtsx";
Application app = new Application();
Package package2 = app.LoadPackage(strLocalPath, null);
// Now that the package is loaded, we can query on
// its properties.
int n = package2.Configurations.Count;
DTSProtectionLevel pl = package2.ProtectionLevel;
DTSExecResult result2 = package2.Execute();
string strErrorDesc = "";
if (result2 == DTSExecResult.Failure)
{
foreach (DtsError local_DtsError in package2.Errors)
{
strErrorDesc = local_DtsError.Description;
}
}
Output of all objects :
n = 0
p1 = EncryptSensitiveWithUserKey
result2 = Failure
strErrorDesc = The product level is insufficient for component "Flat File Source" (1).
Please help me to solve this problem ASAP.
Thanks
Harsh
View 3 Replies
View Related
Jun 29, 2007
Hi there,
I have a flat file source in my data flow that is suppose to feed through a couple of columns to a script component. All of the columns have data but when I debug in my script component (with the aid of messageboxes) I can see that one of then is empty.
This is wrong because when I preview that data in the flat file source, I can see that the columns are all populated.
Has anyone ever encountered that a flat file source doesn't communicate with a script component properly? I mean it does pass through the data of all other columns except for one.
I have replaced the components in case it had become corrupt and I even rebuild the package but the problem still persists.
Any advice would be welcome
Regards
Mike
View 11 Replies
View Related
Jan 14, 2008
Hi,
I am using custom dll in script component in SSIS package. This dll is looking for some configuration settings and dsplays the message as "Configuration section could not be found in the configuration source" . Please tell me the configuration source it looks for.
View 3 Replies
View Related
Jan 22, 2008
Hello,
Kind of a .Net question, although specifically related to VSA.
We have a script task that references an external DLL. That DLL is a proxy for a web service and it takes its configuration info (e.g. location of the web service) from a configuration file.
We have created a quick-and dirty test harness exe to consume data using the proxy and it works fine. All you need to do is dump the proxy, the config file and the test harness exe into a folder.
This doesn't work so well when you substitute SSIS for the test harness. We are trying to use a script task which references the proxy DLL to get a response from the web service but when we execute we get:
"Cannot find default endpoint element that references contract 'blah blah blah' in the ServiceModel client configuration section. This might be because no configuration file was found for your application..."
[See a screenshot here: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/Misc/ssis_script_task_config_error.JPG]
This makes sense of course, right? The DLL needs the config file. However, where do you put the config file so that the DLL (which, remember, is running inside SSIS) can find it? We've tried putting it in the same folder as the .dtsx file, in the same folder as dtexec.exe...nothing works.
To try and debug we put the following code into the script task:
Code Snippet
Dim rst_Path As String
rst_Path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName.CodeBase)
MsgBox(rst_Path)
and the resultant message box was contained "file:C:WINDOWSassemblyGAC_MSILMicrosoft_VsaVb8.0.0.0__<GUID>"
which makes complete sense of course.
So, my question is, how can we configure this whole kaboodle of "stuff" so that it works?
Thanks
Jamie
View 5 Replies
View Related
Aug 5, 2015
How to load .xlsb file using Excel source component in SSIS. Below is the connection manager i see in the properties window.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties="Excel 8.0;HDR=YES";
Do I need to change any values here to process .xlsb file
View 2 Replies
View Related
Mar 16, 2007
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
View 3 Replies
View Related
Aug 13, 2007
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
View 4 Replies
View Related
Mar 30, 2006
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
Does anyone have any suggestions?
TIA . . . Ed
View 7 Replies
View Related
Jul 18, 2006
I am using a Foreach loop container to go thru all the files downloaded from the ftp site and I am assigning the file name of each file to a variable at the foreach loop level called filename. In the dataflow task inside the foreach loop container, I have a source script component that uses a flat file connection. The connection string of the flat file connection is set to the filename variable declared at the foreach loop level. However the script component has a error System.ArgumentException: Empty pathname is not legal.
Please let me know how to correct this? The connectionString property of the flat file connection is set to the complete filename including the path. Does a script component need to have a flat file name specified in the flat file connection that it is using? I need to have a script source component as the flat file I am reading from is not in any of the standard formats.
The flat file connection manager's connection string property is blanked out the moment I specify an Expression for the connection string. Is this a defect or is it expected behavior.
Any inputs appreciated.
PS: I looked thru Jamie's blog at
http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx
when implementing the above package.
Thanks,
Manisha
View 3 Replies
View Related
Nov 27, 2007
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain
Public Sub Main()
Try
Dim xmlDoc As New XmlDocument
xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml")
MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up"
Catch ex As Exception
MsgBox(ex.Message)
UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message)
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub UpdateXML(ByVal fileName As String, ByVal message As String)
Try
Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4)
Dim rd As StreamReader = New StreamReader(fileName)
Dim xml As String = rd.ReadToEnd()
Xml = Xml.Replace(invalidChar, String.Empty)
xml = xml.Replace("", String.Empty)
xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[")
xml = xml.Replace("]]>]]>", "]]>")
MsgBox("replaced")
rd.Close()
Dim wr As StreamWriter = New StreamWriter(fileName)
wr.Write(xml)
wr.Close()
Dim xdoc As XmlDocument = New XmlDocument()
xdoc.Load(fileName)
Catch ex As Exception
UpdateXML(fileName, ex.Message)
End Try
End Sub
End Class
View 4 Replies
View Related
Jul 28, 2006
This error occurs when the ActiveX task tries to execute:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438.
Anybody know how to troubleshoot these errors? I can't find anything on this error code. The same script works in DTS.
View 8 Replies
View Related
Apr 11, 2007
Hi,
I have no "Foreach File Enumerator" oprtion in the Enumerator Property of the Foreach Loop Component.
I have this enumerator in the c:Program FilesMicrosoft SQL Server90DTSForEachEnumerators folder.
Also I check it in the GAC - it does not here. I try to execute gacutil.exe -iF ForEachFileEnumerator.dll, but it is failed with "Failure adding assembly to the cache: The module was expected to contain an assembly manifest." Seems it is not managed enumerator.
Please help me.
Also information on how to regeister unmanaged enumerators are welcome!
View 3 Replies
View Related
Nov 26, 2007
OBJECTIVE: I would like to read a text file from SQL Server 2000, read the text file content, and load its conntents in a RichTextBoxTHINGS I'VE DONE AND HAVE WORKING:1) I've successfully load a text file (ex: textFile.txt) in sql server database table column (with datatype Image) 2) I've also able to load the file using a Handler as below: using System;using System.Web;using System.Data.SqlClient;public class HandlerImage : IHttpHandler {string connectionString;public void ProcessRequest (HttpContext context) {connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NWS_ScheduleSQL2000"].ConnectionString;int ImageID = Convert.ToInt32(context.Request.QueryString["id"]);SqlConnection myConnection = new SqlConnection(connectionString);string Command = "SELECT [Image], Image_Type FROM Images WHERE Image_Id=@Image_Id";SqlCommand cmd = new SqlCommand(Command, myConnection);cmd.Parameters.Add("@Image_Id", System.Data.SqlDbType.Int).Value = ImageID;SqlDataReader dr;myConnection.Open(); cmd.Prepare(); dr = cmd.ExecuteReader();if (dr.Read()){ //WRITE IMAGE TO THE BROWSERcontext.Response.ContentType = dr["Image_Type"].ToString();context.Response.BinaryWrite((byte[])dr["Image"]);}myConnection.Close();}public bool IsReusable {get {return false;}}}'>'>
<a href='<%# "HandlerDocument.ashx?id=" + Eval("Doc_ID") %>'>File
</a>- Click on this link, I'll be able to download or view the file WHAT I WANT TO DO, BUT HAVE PROBLEM:- I would like to be able to read CONTENT of this file and load it in a string as belowStreamReader SR = new StreamReader()SR = File.Open("File.txt");String contentText = SR.Readline();txtBox.text = contentText;BUT THIS ONLY WORK FOR files in the server.I would like to be able to read FILE CONTENTS from SQL Server.PLEASE HELP. I really appreciate it.
View 1 Replies
View Related
Jul 20, 2005
Hi there.I need to fetch ONE random row from many. How can i get it? I try toexecute "SELECT field.table FROM table ORDER by RAND()" no effect.Thank you.
View 1 Replies
View Related
Apr 11, 2007
Hi,
i wanna fetch random data using sql query. is there any query that returns random row? Please help me.. i found some but they didnt work..
View 8 Replies
View Related
