Showing posts with label ssis. Show all posts
Showing posts with label ssis. Show all posts

Monday, March 12, 2012

Problems with FTP Task

I'm relatively new to the SSIS package creation scene and I'm running into a problem with the FTPTask. I'm trying to create a package that will let me download a file from an FTP. I'm trying to use a variable and an expression to set the filename at runtime because the name of the file changes on a daily basis (read: timestamp on the end of the file).
I've managed to get the connection to point to the proper directory, but every time I attempt to run the package I receive a file not found error. If I change the remotepath variable to a static file instead of an expression I do not receive an error.

Any help would be greatly appreciated as I'm losing my mind on this one.

Thanks
Where are you building the expression for the variable?|||Within the variable properties window of BIDS. I set EvaluateAsExpression to true, when I check the parameter on the values portion of the properties window it looks correct.

|||What OS is the FTP server running?|||Near as I can tell Unix, from what I've been reading it's looking like I have to find another way around the problem.
|||

E_DJ wrote:

Near as I can tell Unix, from what I've been reading it's looking like I have to find another way around the problem.

If it's Unix, then yeah, you'll have to implement FTP using the script task.|||Thanks for your help, do you know if MS has any sort of plan to deal with this issue or is it like most things where we're going to have to wait for the next release before anything is addressed?
|||Let's see:

[Microsoft follow-up]

MSFT - When do you plan on fixing the FTP Task when used against non-Microsoft FTP servers?|||

You said "I'm trying to use a variable and an expression to set the filename at runtime".

Can you give me a little more information how you are setting the variable and expression? I just want to make sure I understand your problem.

Also, what's the error message(s)? Thanks.

|||

Cho Yeung - msft wrote:

You said "I'm trying to use a variable and an expression to set the filename at runtime".

Can you give me a little more information how you are setting the variable and expression? I just want to make sure I understand your problem.

Also, what's the error message(s)? Thanks.

Cho, forget about the variable and expression. The issue here is FTP Task support for non-Microsoft FTP servers.

|||

Cho Yeung - msft wrote:

You said "I'm trying to use a variable and an expression to set the filename at runtime".

Can you give me a little more information how you are setting the variable and expression? I just want to make sure I understand your problem.

Also, what's the error message(s)? Thanks.

Specifically, the problem lies in the requirement to have a leading "/" on all remote file paths. This isn't correct and should be removed.

Connect items already out there:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281893

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=150196

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125356

Basically, having the leading "/" requirement and then implementing a change directory to that "/" can cause the FTP Task to fail because it cannot find the file (because you've changed to the FTP root instead of the logged-in user's root path). Instead, I propose you guys look into the following: First, don't change directories. Second STOP running a directory listing to compare the file requested to the returned list. (Why? Because based on some of the Connect submissions above, the task doesn't know how to parse it.) PLEASE, just implement an FTP task that submits the commands directly instead of trying to make an "intelligent" FTP Task.

[Microsoft follow-up]

|||

I am asking because the original post says

" If I change the remotepath variable to a static file instead of an expression I do not receive an error."

It sounds to me like the package is working if the file is specified explicitly.

|||

That is the case, and hence the question, doesn't really make a whole lot of sense as to why it wasn't working.

I know it's not a directory change issue, I took into account that the actual path on the FTP doesn't match the path I see when I log on via the command prompt, once I worked out the filepath from the root dir it fixed that problem.

This is a bit of a moot point now, I wrote a script task to do what I needed it to. Little odd that it took me a half hour to get something working writing a script that took several hours to get to the same point using the FTPTask component.

|||

I am going to log a bug in our system so we can track this potential problem.

Friday, March 9, 2012

Problems with executing ssis package by job

Hi All,
I have created ssis package and am trying to execute it using sql
server agent job.
The problem is, that the job failes to find or load the package. It
tries to run it from the server side and failes to find it. I don't
really understand how can I publish the package on the server from the
client side.
Thanks in advance.Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||Qwert wrote:
> Hi All,
> I have created ssis package and am trying to execute it using sql
> server agent job.
> The problem is, that the job failes to find or load the package. It
> tries to run it from the server side and failes to find it. I don't
> really understand how can I publish the package on the server from the
> client side.
> Thanks in advance.
Hi
Have you saved your package in SQL Server ?
from file menu in Microsoft Visual Studio select Save copy of package
as and save your package in SQL Server.
or
Connect to intergation service in Management studio and import package.
Regards
Amish Shah
http://shahamishm.tripod.com
Regards
Amish Shah|||amish wrote:
> Hi
> Have you saved your package in SQL Server ?
> from file menu in Microsoft Visual Studio select Save copy of package
> as and save your package in SQL Server.
> or
> Connect to intergation service in Management studio and import package.
>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>
> Regards
> Amish Shah
Hi, thanks for replying,
Yes I tried both things, what I get is
Executed as user: CLUSTERNET\admin. The package could not be loaded.
The step failed.
Yours Qwert

Wednesday, March 7, 2012

Problems with creating Custom Log Provider

I am new to SSIS but I am learning fast. Any help is greatly appreciated.

I have written a custom log provider derived from LogProviderBase. I want to programmtically add the custom log provider to my package, but when I try this:

myPackage.LogProviders.Add("ACustomLogProvider");

I get the following exception:

"The log provider type "ACustomLogProvider" specified for log provider "{..GUID..}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name".

I have followed the instructions in BOL and as suggested in this forum, adding the DtsLogProvider attribute:

[DtsLogProvider(DisplayName="ACustomLogProvider", LogProviderType="ACustomLogProvider", Description="a description")]

Another way I tried is that I added a GUID attribute to my custom log provider class and then tried this:

Type aType = typeof(ACustomLogProvider);

Guid aGUID = (Guid)aType.GUID;

myPackage.LogProviders.Add("{"+ aGUID.ToString() +"}");

This also gives me the same exception "The log provider type ""{..the actual GUID for ACustomLogProvider..}" specified for log provider "{..a different GUID..}" is not recognized as a valid log provider type....

My questions are: Is it possible to add a custom log provider programmtically or must it be done through the SSIS Designer?

Must I deploy my custom log provider class for it to work programmatically? (I had perhaps wrongly assumed I would not have to deploy if I don't want it to show up in the SSIS Designer).

I would prefer to use a custom log provider instead of just logging within the SSIS event handlers because I can override the OnPostExecute, etc. but I cannot (can I?) override the specific event handlers like PackageStart, PackageEnd, etc because they are not exposed through DTSEvents.

Any help I would be very grateful for. Thanks.

I think you need to pass fully qualified type name to Add method:

myPackage.LogProviders.Add(aType.AssemblyQualifiedName);

|||

Thank you very much, Michael. That is working great.

I appreciate your help, especially since this seems like it was a trivial question. I should have been able to see that!

Thanks,

Amy

|||

Hi,

I've trying to create a custom log provider following msdn directions from:

http://technet.microsoft.com/en-us/library/ms365184.aspx

However, the example shown there for HtmlLogProvider does not work for me. I created the dll put it in every single folder it is indicated and also used gacutil and nothing. When I go to the SQL Server Business Intelligence Development Studio and I select "Logging" from the control flow, the only available log proivders that I see are the ones that came by default with the the IDE but not the one I created (HtmlLogProvider). I would like to know if you tried it and also had the same problem and if you could give me some advice of what to do.

Thanks so much

|||Try restarting the SSIS service.|||

I finally made it work for the HtmlLog Provider but sometimes it works and sometimes it does not. By some reason sometimes it does fill data inside the html and sometimes it does nothing with it. I have wondering what may be the reason.

The steps I followed once I had the Log Provider installed in the GAC and copied into the log provider folder was to do:

Logging

Add Custom log provider

and choose the package level and som of the events (On error, etc). I also tried selecting everything.

I also tried clickng on "Log Events" but what it does is only show a window with the logged events.

Please tell me if there is a bug with the log providers in SSIS or if I am not following the right steps.

Thanks a lot

|||

I'm not aware of any bug with the log providers. The stock log providers work using the same extensibility mechanism as you should be using, so if stock log providers work OK, you need to debug why yours is not working.

It is unclear what do you mean by "sometime it does nothing with it" - have you debugged whether the log provider receives the events you expect it to receive?

|||

I am sorry I was not too specific with what I meant. Let me try to make myself more clear. First I downloaded an example of an HtmlLogProvider from MS and tried it and worked. Now I was looking at the methods that are overwritten and I wanted to create my own log provider. So I tried to do it as simple as possible. So in my dummy example I just wanted to see the errors of my dtsx pacakge thru my cutom log provider. The code is as follows:

namespace HtmlLogProviderCS

{

using System;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Web.UI;

using System.Web;

// The LogProviderType property is required but not used.

// The custom log provider will not appear in the list

// if a LogProviderType is not provided.

[DtsLogProvider(DisplayName = "MyLogProviderTest", Description = "A simple log provider.", LogProviderType = "Custom")]

public class HtmlLogProviderCS : LogProviderBase

{

#region Variables and constants

// Constants.

private const string SUBCOMPONENT = "HtmlLogProviderCS";

private const string PACKAGE_END_EVENT = "PackageEnd";

// Variables.

private Microsoft.SqlServer.Dts.Runtime.Connections _connections;

private Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents _events;

private string _configString;

private string _logFile;

private HtmlLogWriterCS _htmlLogWriter;

private MemoryStream _logStream;

private StreamWriter _logStreamWriter;

// Status flags.

private bool _fireEventsAgain=true;

private bool _loggingAlreadyStarted;

private bool _packageHasEnded;

#endregion

public override void InitializeLogProvider(Microsoft.SqlServer.Dts.Runtime.Connections connections, Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events, Microsoft.SqlServer.Dts.Runtime.ObjectReferenceTracker refTracker)

{

_events = events;

System.Windows.Forms.MessageBox.Show("Init");

}

public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events)

{

System.Windows.Forms.MessageBox.Show("Validate");

return DTSExecResult.Success;

}

public override void OpenLog()

{

System.Windows.Forms.MessageBox.Show("OpenLog");

}

public override void CloseLog()

{

System.Windows.Forms.MessageBox.Show("CloseLog");

}

public override void Log(string logEntryName, string computerName, string operatorName, string sourceName, string sourceID, string executionID, string messageText, DateTime startTime, DateTime endTime, int dataCode, byte[] dataBytes)

{

System.Windows.Forms.MessageBox.Show("Logging...");

System.Windows.Forms.MessageBox.Show("Message is: " + logEntryName + "--" + computerName + "--" + operatorName + "--" + sourceName + "--" + sourceID + "--" + executionID + "--" + messageText);

}

}

}

Many of the variables declared in there are just there cuz they were in the example I took form the HtmlLogProvider but other than that there's no reason for them to be there. So I really do not know why this Log is not working. The weird thing is that the message that am putting for every method does pop op correctly in every method EXCEPT for the LOG method which is not even invoked.

Problems with creating Custom Log Provider

I am new to SSIS but I am learning fast. Any help is greatly appreciated.

I have written a custom log provider derived from LogProviderBase. I want to programmtically add the custom log provider to my package, but when I try this:

myPackage.LogProviders.Add("ACustomLogProvider");

I get the following exception:

"The log provider type "ACustomLogProvider" specified for log provider "{..GUID..}" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name".

I have followed the instructions in BOL and as suggested in this forum, adding the DtsLogProvider attribute:

[DtsLogProvider(DisplayName="ACustomLogProvider", LogProviderType="ACustomLogProvider", Description="a description")]

Another way I tried is that I added a GUID attribute to my custom log provider class and then tried this:

Type aType = typeof(ACustomLogProvider);

Guid aGUID = (Guid)aType.GUID;

myPackage.LogProviders.Add("{"+ aGUID.ToString() +"}");

This also gives me the same exception "The log provider type ""{..the actual GUID for ACustomLogProvider..}" specified for log provider "{..a different GUID..}" is not recognized as a valid log provider type....

My questions are: Is it possible to add a custom log provider programmtically or must it be done through the SSIS Designer?

Must I deploy my custom log provider class for it to work programmatically? (I had perhaps wrongly assumed I would not have to deploy if I don't want it to show up in the SSIS Designer).

I would prefer to use a custom log provider instead of just logging within the SSIS event handlers because I can override the OnPostExecute, etc. but I cannot (can I?) override the specific event handlers like PackageStart, PackageEnd, etc because they are not exposed through DTSEvents.

Any help I would be very grateful for. Thanks.

I think you need to pass fully qualified type name to Add method:

myPackage.LogProviders.Add(aType.AssemblyQualifiedName);

|||

Thank you very much, Michael. That is working great.

I appreciate your help, especially since this seems like it was a trivial question. I should have been able to see that!

Thanks,

Amy

|||

Hi,

I've trying to create a custom log provider following msdn directions from:

http://technet.microsoft.com/en-us/library/ms365184.aspx

However, the example shown there for HtmlLogProvider does not work for me. I created the dll put it in every single folder it is indicated and also used gacutil and nothing. When I go to the SQL Server Business Intelligence Development Studio and I select "Logging" from the control flow, the only available log proivders that I see are the ones that came by default with the the IDE but not the one I created (HtmlLogProvider). I would like to know if you tried it and also had the same problem and if you could give me some advice of what to do.

Thanks so much

|||Try restarting the SSIS service.|||

I finally made it work for the HtmlLog Provider but sometimes it works and sometimes it does not. By some reason sometimes it does fill data inside the html and sometimes it does nothing with it. I have wondering what may be the reason.

The steps I followed once I had the Log Provider installed in the GAC and copied into the log provider folder was to do:

Logging

Add Custom log provider

and choose the package level and som of the events (On error, etc). I also tried selecting everything.

I also tried clickng on "Log Events" but what it does is only show a window with the logged events.

Please tell me if there is a bug with the log providers in SSIS or if I am not following the right steps.

Thanks a lot

|||

I'm not aware of any bug with the log providers. The stock log providers work using the same extensibility mechanism as you should be using, so if stock log providers work OK, you need to debug why yours is not working.

It is unclear what do you mean by "sometime it does nothing with it" - have you debugged whether the log provider receives the events you expect it to receive?

|||

I am sorry I was not too specific with what I meant. Let me try to make myself more clear. First I downloaded an example of an HtmlLogProvider from MS and tried it and worked. Now I was looking at the methods that are overwritten and I wanted to create my own log provider. So I tried to do it as simple as possible. So in my dummy example I just wanted to see the errors of my dtsx pacakge thru my cutom log provider. The code is as follows:

namespace HtmlLogProviderCS

{

using System;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Web.UI;

using System.Web;

// The LogProviderType property is required but not used.

// The custom log provider will not appear in the list

// if a LogProviderType is not provided.

[DtsLogProvider(DisplayName = "MyLogProviderTest", Description = "A simple log provider.", LogProviderType = "Custom")]

publicclassHtmlLogProviderCS : LogProviderBase

{

#region Variables and constants

// Constants.

privateconststring SUBCOMPONENT = "HtmlLogProviderCS";

privateconststring PACKAGE_END_EVENT = "PackageEnd";

// Variables.

private Microsoft.SqlServer.Dts.Runtime.Connections _connections;

private Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents _events;

privatestring _configString;

privatestring _logFile;

privateHtmlLogWriterCS _htmlLogWriter;

privateMemoryStream _logStream;

privateStreamWriter _logStreamWriter;

// Status flags.

privatebool _fireEventsAgain=true;

privatebool _loggingAlreadyStarted;

privatebool _packageHasEnded;

#endregion

publicoverridevoid InitializeLogProvider(Microsoft.SqlServer.Dts.Runtime.Connections connections, Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events, Microsoft.SqlServer.Dts.Runtime.ObjectReferenceTracker refTracker)

{

_events = events;

System.Windows.Forms.MessageBox.Show("Init");

}

publicoverride Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents events)

{

System.Windows.Forms.MessageBox.Show("Validate");

returnDTSExecResult.Success;

}

publicoverridevoid OpenLog()

{

System.Windows.Forms.MessageBox.Show("OpenLog");

}

publicoverridevoid CloseLog()

{

System.Windows.Forms.MessageBox.Show("CloseLog");

}

publicoverridevoid Log(string logEntryName, string computerName, string operatorName, string sourceName, string sourceID, string executionID, string messageText, DateTime startTime, DateTime endTime, int dataCode, byte[] dataBytes)

{

System.Windows.Forms.MessageBox.Show("Logging...");

System.Windows.Forms.MessageBox.Show("Message is: " + logEntryName + "--" + computerName + "--" + operatorName + "--" + sourceName + "--" + sourceID + "--" + executionID + "--" + messageText);

}

}

}

Many of the variables declared in there are just there cuz they were in the example I took form the HtmlLogProvider but other than that there's no reason for them to be there. So I really do not know why this Log is not working. The weird thing is that the message that am putting for every method does pop op correctly in every method EXCEPT for the LOG method which is not even invoked.

Monday, February 20, 2012

Problems using SSIS w/ non-SQL Server data sources

I've encountered a few problems using SSIS against non-SQL Server data
sources and was hoping that others might have some experience. Google
searches and browsing MSDN hasn't led me to a solution, so any advice
on the following is appreciated:

1) When using the "Data Source Views" wizard to add a data source
view from an ODBC data source, only tables appear in the object
listing. Views do not. (I've also observed this with SQL Server 2005
databases as well, but it's a bigger issue when you can't use the native SqlClient, as is the case for many ODBC-only databases.) ODBC traces show that both table and
view metadata is being returned to SSIS correctly, so it appears as
though SSIS is filtering out views from the object listing in the
wizard.

2) When creating named queries against (non-SQL Server) ODBC data
sources, SSIS appears to use the SQL Server SQL syntax for referencing
schemas/objects (e.g. "SELECT * FROM [schema].[table]", rather than
"SELECT * FROM schema.table"). This isn't valid SQL in many databases.

Am I missing something? Is there a way to change this through some
configuration setting?

Hello Christopher,

I think you may be burning a lot of time unproductively on "Data Source Views". From my understanding, for SSIS they are only a way to assist in generating a SQL statement for use in SSIS. Once generated, SSIS stores the resulting SQL.

So to my mind, why bother? I prefer to ignore "Data Source Views" and just work directly in the package.

SSIS is so fussy and pedantic that I don't think you will get any "metadata management" benefit from a DSV - pretty much any change in the schema will be a breaking change.

Good luck

Mike

|||

I think Mike is right in his first statement. DSV are not necessary (and may be not recommended) when working in SSIS; just create your connection directly against the sources.

Problems using SSIS w/ non-SQL Server data sources

I've encountered a few problems using SSIS against non-SQL Server data
sources and was hoping that others might have some experience. Google
searches and browsing MSDN hasn't led me to a solution, so any advice
on the following is appreciated:

1) When using the "Data Source Views" wizard to add a data source
view from an ODBC data source, only tables appear in the object
listing. Views do not. (I've also observed this with SQL Server 2005
databases as well, but it's a bigger issue when you can't use the native SqlClient, as is the case for many ODBC-only databases.) ODBC traces show that both table and
view metadata is being returned to SSIS correctly, so it appears as
though SSIS is filtering out views from the object listing in the
wizard.

2) When creating named queries against (non-SQL Server) ODBC data
sources, SSIS appears to use the SQL Server SQL syntax for referencing
schemas/objects (e.g. "SELECT * FROM [schema].[table]", rather than
"SELECT * FROM schema.table"). This isn't valid SQL in many databases.

Am I missing something? Is there a way to change this through some
configuration setting?

Hello Christopher,

I think you may be burning a lot of time unproductively on "Data Source Views". From my understanding, for SSIS they are only a way to assist in generating a SQL statement for use in SSIS. Once generated, SSIS stores the resulting SQL.

So to my mind, why bother? I prefer to ignore "Data Source Views" and just work directly in the package.

SSIS is so fussy and pedantic that I don't think you will get any "metadata management" benefit from a DSV - pretty much any change in the schema will be a breaking change.

Good luck

Mike

|||

I think Mike is right in his first statement. DSV are not necessary (and may be not recommended) when working in SSIS; just create your connection directly against the sources.