Can not add reference to microsoft.sqlserver.dtsruntimewrap.dll
-
I have been tasked with what seems to be a pretty simple ETL task.
My company gets two different file feeds both with a flat file delimited format. The feeds both come in to the same directory and I need to simply just load the data in the files into the db (no transformations are necessary). The catch is that some of the files are comma delimited and some are tilda (~) delimited and the only way to know is to actually open the file and eyeball the data.
I thought that this shouldn't be such a big deal. I will create a package with a foreach loop to loop through the directory where the flatfiles are located. Foreach file read the first line and see if there are tilda's in the string, (If it is a comma delimited file there will be no tilda characters in the file,) and set a variable to either "~" or ",". I would then be able to set the delimiter to use to parse the file at runtime.
I got it all set up the package runs great. However, I can not figure out how to use the variable in defining the delimiter. I have read a number of articles on line which all make reference to the Microsoft.SqlServer.Dts.Runtime.Wrapper which I believe will help me in being able to set that property. However for some reason, I can not add that reference. I can not find the dll on my machine. I was able to identify that it seems to be installed in the GAC, however, I can't use the libraries included in that assembly.
Can someone please give me some pointers, on how to get this issue solved?
Thank you
-
I do not see why you need a reference for that. Note that for DataFlow Script Task it is already included.
What I would try is create two variables
FileName String
Delimiter char
at the package level
Then with your loop assign FileName to the current file (full path)
Then inside the loop have a script task that takes in the ReadOnly variable of FileName and the readwrite variable of Delimiter
This is sort of the body of the script
Dim sr As System.IO.StreamReader
sr = New System.IO.StreamReader(Dts.Variables("FileName").Value.ToString())
Dim line As String = sr.ReadLine()
If (InStr(line, "~") > 0) Then
Dts.Variables("Delimiter").Value = "~"
ElseIf (InStr(line, ",") > 0) Then
Dts.Variables("Delimiter").Value = ","
End If
Dts.TaskResult = Dts.Results.Success
I didn't flush it out yet so you would want to put try catch blocks around the stream reader code.
Then assign your RowDelimiter property of your file connection to the variable. This can be done in the Expressions under the advance properties. Provided you have the same columns and types, that should work. Again maybe I will post back when I finish the test solution.
-
Ok. I have figured out (though not sure I understand why it is this way) that in a script task for some reason you can not reference the wrapper library, however in a script component you can.
Either way, I have created a similar code that you have posted below. The issue I have is that I need to set the ColumnDelimiter and not the RowDelimiter. As I understand it, a RowDelimiter tells SSIS which character(s) defines a new row and a ColumnDelimiter defines which character(s) defines a new column.
For some reason it seems that Microsoft has not added the ability to add an expression for setting the ColumnDelimiter as there is for setting the RowDelimiter.
The code I have written for a Script Component is this. However, I can not get it to do what I am trying to do.
ConnectionManagerFlatFileClass responseFile = Connections.ResponseFile as ConnectionManagerFlatFileClass;
string theFile = Variables.varFileName.ToString();
if (responseFile != null)
{
theFile = responseFile.ConnectionString.ToString();
if (theFile != string.Empty)
{
FileStream fileStream = File.OpenRead(theFile);
StreamReader streamReader = new StreamReader(fileStream);
string firstLine = streamReader.ReadLine();
if (firstLine != null && firstLine.Trim() != string.Empty)
{
if (firstLine.ToLower().IndexOf("ldc") == 0) //this is header record
Variables.varColumnNamesInFirstDataRow = true;
else
Variables.varColumnNamesInFirstDataRow = false;
if (firstLine.Contains("~"))
Variables.varDelimiter = "~";
else
Variables.varDelimiter = ",";
responseFile.ColumnNamesInFirstDataRow = true;
foreach (IDTSConnectionManagerFlatFileColumn100 column in responseFile.Columns)
column.ColumnDelimiter = Variables.varDelimiter;
}
}
}
The code which I have written in a script task is this, however, I can not figure out what to with the ColumnDelimiter variable to ensure that it is used for parsing the file.
ConnectionManager responseFile = Dts.Connections["ResponseFile"];
//RunttimeWrapper.ConnectionManagerFlatFileClass responseFile = Dts.Connections["EcInfo814Response"] as RunttimeWrapper.ConnectionManagerFlatFileClass;
if (responseFile != null)
{
string theFile = responseFile.ConnectionString.ToString();
if (theFile != string.Empty)
{
FileStream fileStream = File.OpenRead(theFile);
StreamReader streamReader = new StreamReader(fileStream);
string firstLine = streamReader.ReadLine();
if (firstLine != null && firstLine.Trim() != string.Empty)
{
if (firstLine.ToLower().IndexOf("ldc") == 0) //this is header record
Dts.Variables["varColumnNamesInFirstDataRow"].Value = true;
else
Dts.Variables["varColumnNamesInFirstDataRow"].Value = false;
if (firstLine.Contains("~"))
Dts.Variables["varDelimiter"].Value = "~";
else
Dts.Variables["varDelimiter"].Value = ",";
//responseFile.ColumnNamesInFirstDataRow = true;
//responseFile.delimiter = Dts.Variables["varDelimiter"].Value.ToString();
if (responseFile.Properties.Contains("Columns"))
{
Microsoft.SqlServer.Dts.Runtime.DtsProperties pkgProperties = responseFile.Properties;
foreach (Microsoft.SqlServer.Dts.Runtime.DtsProperty property in pkgProperties)
{
if (property.Name.ToString() == "Columns")
{
string tmp = property.Type.ToString();
}
}
}
}
}
}
-
You're right, sorry to lead you astray
To answer your original question after I added the reference
Microsoft.SqlServer.DTSRuntimeWrap
I was able to type the imports statement
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Now what you can do with it is a whole other issue
Also I am using version 9.0.242.0
So try
Also try using ConnectionManagerMultiFlatFile instead of ConnectionManagerFlatFileClass
IDTSConnectionManagerFlatFileColumn90 instead of IDTSConnectionManagerFlatFileColumn100
Those classes came up in the intellasence.
-
Ok, I have figured out how to do this.
For some reason the Microsoft.SqlServer.DTSRuntimeWrap assembly was installed in the GAC and the dll was deep in a GAC folder which you can not view by trying to browse to the folder.
I was therefore not able to add a reference to that assembly and make use of the classes and interfaces defined in it. However I had noticed that when creating a Script Component on the data flow tab, SSIS automatically added a reference to this dll. So what I did was add this Script Component open the script, click on the reference and copy the path to the dll from the properties of that reference. I then pasted the full path to the dll in the browse window when trying to add a reference to the script for my Script Task. (For me the dll was found at :\WINDOWS\assembly\GAC_32\Microsoft.SqlServer.DTSRuntimeWrap\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.DTSRuntimeWrap.dll.)
I was now able to add the using clauses and make use of those classes. With a little help from Ivan Peev from CozyRoc.com, (who by the way, extends the abilities of SSIS by offering a number of plug in tools for SSIS, I was now able to loop through each file and read the first line to determine the delimiter and whether or not there are column headers, and then set the connection manager accordingly, by simply setting the ColumnNamesInFirstDataRow property to a boolean value. Setting the columnDelimiter was slightly more complex. All you need to do is loop through each column in the Columns property of the connection manager and set that column.ColumnDelimiter property. One catch is, that you need to ensure that you set the last column on the row to the end of line (or row delimiter value) so that SSIS knows to start a new row. The code for this script is below.
.....
using Microsoft.SqlServer.Dts;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
......
private string delimiter = ",";
private bool ColumnNamesInFirstDataRow = false;
private void SetVariables(string file)
{
if (file != string.Empty)
{
FileStream fileStream = File.OpenRead(file);
StreamReader streamReader = new StreamReader(fileStream);
string firstLine = streamReader.ReadLine();
if (firstLine != null && firstLine.Trim() != string.Empty)
{
if (firstLine.ToLower().IndexOf("ldc") == 0) //this is header record
//Dts.Variables["varColumnNamesInFirstDataRow"].Value = true;
ColumnNamesInFirstDataRow = true;
else
//Dts.Variables["varColumnNamesInFirstDataRow"].Value = false;
ColumnNamesInFirstDataRow = false;
if (firstLine.Contains("~"))
//Dts.Variables["varDelimiter"].Value = "~";
delimiter = "~";
else
//Dts.Variables["varDelimiter"].Value = ",";
delimiter = ",";
}
}
}
public void Main()
{
try
{
RuntimeWrapper.IDTSConnectionManagerFlatFile100 responseFile = Dts.Connections["EcInfo814Response"].InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
string theFile = Dts.Variables["varFileName"].Value.ToString();
SetVariables(theFile);
if (responseFile != null)
{
responseFile.ColumnNamesInFirstDataRow = ColumnNamesInFirstDataRow;
int totalColumns = responseFile.Columns.Count;
foreach (RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 column in responseFile.Columns)
if (column == responseFile.Columns[totalColumns - 1])
column.ColumnDelimiter = "\r";
else
column.ColumnDelimiter = delimiter;
Dts.TaskResult = (int)ScriptResults.Success;
}
else
Dts.TaskResult = (int)ScriptResults.Failure;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
-
I have same issue. I can not add Microsoft.SqlServer.DtsRuntimeWrapper. i have tried adding using full path c:\windows\assembly\Microsoft.SqlServer.DtsRuntimeWrapper.dll. how do i add reference to Microsoft.SqlServer.DtsRuntimeWrapper.dll.
-
You can add the reference using browse option (Please see attached image below)
HTH!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply