Showing posts with label package. Show all posts
Showing posts with label package. Show all posts

Wednesday, March 28, 2012

How to use variables in OLE DB or OLE Destination?

Hello -

I am making good progress with my ssis package. However, there is one new thing which I cannot graps yet. That is, how to use variables when I want to update or insert a new row. I have some columns in my tables that require the datetime that the update/insert occured, the person making the change, and a few other things that are not part of the incoming data source (an excel file).

I created some user variables for these things, but I cannot figure out how to use them with my OLE DB Command and OLE DB Destination. One handles Inserts and the other handles the Updates based on whether a row in the Excel file is new (an Insert) or already exists (an update). Along with the insert or update, I'd like to set the Lastupdate, Who, etc.

Thanks for any help

- will

Use a Derived Column transform, add a new column, and use the variable as the expression. This column will now contain your variable value. It can now be used in either the OLE-DB Destination or Command just like any other column, so you can insert or update the target column as required.|||

For the LastUpdated column you should consider using a default value on the column definition. Place GetDate() in the textbox beside Default.

Otherwise, do what Darren said and use a Derived Column, and put the variable in the Expression textbox.

|||Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].|||

DarrenSQLIS wrote:

Would it not make more sense to use a consistent date value for all rows in a load? A variable is ideal for this. GETDATE would be evaluated for every row, so you could get a raneg of dates accross teh load, and it *might* cost more. Perhaps one of the system variables would actually do better than a user variable here, e.g. @.[System::StartTime] or @.[System::ContainerStartTime].

Not to mention there is no simple "DATE" type yet in SQL Server. So if you do use the getdate() function, you'll get the timestamp portion as well, which won't be unique for all rows processed in that batch. Do what Darren suggests, and use a system variable, or a variable that's been converted to a simple date prior to execution. You certainly do not want to evaluate getdate() for each row if you don't need to.|||

Well it depends on the requirements...

Do you need a consistent value for ALL rows in a load?

This sounds like an audit field so what other applications are using it? If a single user inserts or updates a record in addition to the data loads, and you want consistent behavior across your application then setting a default value for the column in the table definition makes sense. We've used GetDate() as the default value for LastUpdatedDate on many projects without any adverse effects on performance.

|||

Thanks to everyone for the advice. The idea of using the derived column transform sounds great. I have toyed with the idea of whether to use a consistent date/time (the same for all rows) or to evaluate per row, and I decided to use the same variable/value for all rows.

Thanks again for all of the help.

sql

Monday, March 26, 2012

How to use unit test in SSIS

I build a SSIS Package in Visual Studio 2005 , but i don't know how to test it automated?

Steven,

I don't know of way of unit testing SSIS packags other than buildig a framework for it yourself. There is certainly nothing provided with the product to allow you to do this.

Do you think there should be?

-Jamie

|||

I have played around with using the data unit testing features in the Database Developer's version of Visual Studio to test SSIS packages. It was a bit of a hack, and the functionality was limited for testing non-relational destinations.

I, for one, would love to see better support for unit testing in SSIS. Here's a Connect request I just created for it:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276292

|||

Nice one John. I'd definately like to see that.

I think it would be a lot easier to unit test SSIS packages if the package were not one stand-alone entity. If you could access different parts of the package outside the context of the package it would really help.

That is why I think this connect posting is such good ideas:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253500

-Jamie

|||Automated unit testing is difficult to do right now in SSIS.

It seems like when a given project has reached a size of 5+ packages (arbitrary number) , unit testing via point-and-click becomes untenable. "necessity is the mother of invention", and so I think various people will start forging their own test-automation "framework" on top of the framework provided in Visual studio Team Suite, Team Dev, or DB Pro, or perhaps nUnit. Its just too unproductive/time-consuming/error-prone to go through all the Integration Services project packages in a point-and-click fashion and make sure they all still "work" without some sort of automation system.
|||

I agree 100% percent. I'm a big fan of automated unit testing from back in my app dev days, and it's one of the biggest things I miss in working with BI tools. I think a decent tool could be built for testing control flow, but currently, I don't think SSIS provides all the "hooks" needed to really automate the unit testing of data flows, except as an end to end process. That makes unit testing data flows very similar to unit testing a method with 5000 lines of code in it Smile.

I'm hoping Katmai will help.

How to use unit test in SSIS

I build a SSIS Package in Visual Studio 2005 , but i don't know how to test it automated?

Steven,

I don't know of way of unit testing SSIS packags other than buildig a framework for it yourself. There is certainly nothing provided with the product to allow you to do this.

Do you think there should be?

-Jamie

|||

I have played around with using the data unit testing features in the Database Developer's version of Visual Studio to test SSIS packages. It was a bit of a hack, and the functionality was limited for testing non-relational destinations.

I, for one, would love to see better support for unit testing in SSIS. Here's a Connect request I just created for it:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276292

|||

Nice one John. I'd definately like to see that.

I think it would be a lot easier to unit test SSIS packages if the package were not one stand-alone entity. If you could access different parts of the package outside the context of the package it would really help.

That is why I think this connect posting is such good ideas:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253500

-Jamie

|||Automated unit testing is difficult to do right now in SSIS.

It seems like when a given project has reached a size of 5+ packages (arbitrary number) , unit testing via point-and-click becomes untenable. "necessity is the mother of invention", and so I think various people will start forging their own test-automation "framework" on top of the framework provided in Visual studio Team Suite, Team Dev, or DB Pro, or perhaps nUnit. Its just too unproductive/time-consuming/error-prone to go through all the Integration Services project packages in a point-and-click fashion and make sure they all still "work" without some sort of automation system.
|||

I agree 100% percent. I'm a big fan of automated unit testing from back in my app dev days, and it's one of the biggest things I miss in working with BI tools. I think a decent tool could be built for testing control flow, but currently, I don't think SSIS provides all the "hooks" needed to really automate the unit testing of data flows, except as an end to end process. That makes unit testing data flows very similar to unit testing a method with 5000 lines of code in it Smile.

I'm hoping Katmai will help.

How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

How to use transaction in a SSIS Package

I try ton use a transaction in a SSIS package. When running i have an error :

[source [1]] Error: The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009.

[Connection manager "myconnection"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025 "Le partenaire du gestionnaire de transactions a dsactiv la prise en charge des transactions à distance/par rseau.".

Can someone help ?

thanks

Where is that 'myconnection' connection manager pointing to?

Make sure you open it an test its connection.

|||The connection is pointing an oracle database and is correct. When i run tha package whitout the transaction it works correctly. This error appears only when i implement the transaction.|||I believe SSIS uses Microsoft's Distributed Transaction Coordinator - Oracle needs to have support for that enabled explicitly (or at least it did last time I used it).|||I try to do the same thing using SQL SERVER 2005 databases for source and destination. And i have the same error.|||

Make sure you have Distributed Transaction Coordinator running on both the source and destination machines.

Brian Knight has a good video about it on Jumpstart TV - Using Transactions in SSIS

|||

Try to use checkpoints too!

Regards

|||

PedroCGD wrote:

Try to use checkpoints too!

Regards

Not the same as transactions though...|||

I know is not the same..

but could help... some people dont know checkpoint and it features.

Monday, March 19, 2012

How to use password with a DTS package

We have a VBA application that calls a SQL Server 2000 Job which calls
a DTS package to pull data from one database and populate another
database. In order to pull the data, we need to provide a password to
the connection. Currently, the VBA app populates an .ini file with the
password (provided by the app user) which the DTS package then uses to
log in to the source database.
I'm not very happy about storing a password anywhere where somebody
could find it. Can anybody suggest a better solution?
Thanks in advance,
SaulHi
Your package password can be specified on the command line using the /M or
/~M parameter, therefore you may want to pass an encrypted parameter or
possibly an encrypted command check out
http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
am not sure why you are running this from VBA and not using SQL Agent!
John
"saul_margolis@.hotmail.com" wrote:

> We have a VBA application that calls a SQL Server 2000 Job which calls
> a DTS package to pull data from one database and populate another
> database. In order to pull the data, we need to provide a password to
> the connection. Currently, the VBA app populates an .ini file with the
> password (provided by the app user) which the DTS package then uses to
> log in to the source database.
> I'm not very happy about storing a password anywhere where somebody
> could find it. Can anybody suggest a better solution?
> Thanks in advance,
> Saul
>|||Thanks John. I know I can pass in global variables when calling the
DTS package, but the DTS package is called by the job. I would need
some way of passing in the password to the job. Do you know if that's
possible?
John Bell wrote:[vbcol=seagreen]
> Hi
> Your package password can be specified on the command line using the /M or
> /~M parameter, therefore you may want to pass an encrypted parameter or
> possibly an encrypted command check out
> http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun.
I
> am not sure why you are running this from VBA and not using SQL Agent!
> John
> "saul_margolis@.hotmail.com" wrote:
>|||Hi
I am not sure why you need to do this as the password for any given package
would be relatively fixed! It is not passed as a global variable by it's own
argument on the command line.
If you ran the package from a stored procedure it could pick up the package
name and other parameters from a table
http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
and encrypt the whole command and keep the ini file. You could also use a
table of encrypted commands!!
John
"saul_margolis@.hotmail.com" wrote:

> Thanks John. I know I can pass in global variables when calling the
> DTS package, but the DTS package is called by the job. I would need
> some way of passing in the password to the job. Do you know if that's
> possible?
>
>
> John Bell wrote:
>|||Hi John,
It's not actually the password for the package that is needed. It's
the password that the package will use to login to the server which
contains the source data.
John Bell wrote:[vbcol=seagreen]
> Hi
> I am not sure why you need to do this as the password for any given packag
e
> would be relatively fixed! It is not passed as a global variable by it's o
wn
> argument on the command line.
> If you ran the package from a stored procedure it could pick up the packag
e
> name and other parameters from a table
> http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> and encrypt the whole command and keep the ini file. You could also use a
> table of encrypted commands!!
> John
>
> "saul_margolis@.hotmail.com" wrote:
>|||Hi
Can you not used the /E parameter?
John
"saul_margolis@.hotmail.com" wrote:

> Hi John,
> It's not actually the password for the package that is needed. It's
> the password that the package will use to login to the server which
> contains the source data.
>
> John Bell wrote:
>

How to use password with a DTS package

We have a VBA application that calls a SQL Server 2000 Job which calls
a DTS package to pull data from one database and populate another
database. In order to pull the data, we need to provide a password to
the connection. Currently, the VBA app populates an .ini file with the
password (provided by the app user) which the DTS package then uses to
log in to the source database.
I'm not very happy about storing a password anywhere where somebody
could find it. Can anybody suggest a better solution?
Thanks in advance,
SaulHi
Your package password can be specified on the command line using the /M or
/~M parameter, therefore you may want to pass an encrypted parameter or
possibly an encrypted command check out
http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
am not sure why you are running this from VBA and not using SQL Agent!
John
"saul_margolis@.hotmail.com" wrote:
> We have a VBA application that calls a SQL Server 2000 Job which calls
> a DTS package to pull data from one database and populate another
> database. In order to pull the data, we need to provide a password to
> the connection. Currently, the VBA app populates an .ini file with the
> password (provided by the app user) which the DTS package then uses to
> log in to the source database.
> I'm not very happy about storing a password anywhere where somebody
> could find it. Can anybody suggest a better solution?
> Thanks in advance,
> Saul
>|||Thanks John. I know I can pass in global variables when calling the
DTS package, but the DTS package is called by the job. I would need
some way of passing in the password to the job. Do you know if that's
possible?
John Bell wrote:
> Hi
> Your package password can be specified on the command line using the /M or
> /~M parameter, therefore you may want to pass an encrypted parameter or
> possibly an encrypted command check out
> http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> am not sure why you are running this from VBA and not using SQL Agent!
> John
> "saul_margolis@.hotmail.com" wrote:
> > We have a VBA application that calls a SQL Server 2000 Job which calls
> > a DTS package to pull data from one database and populate another
> > database. In order to pull the data, we need to provide a password to
> > the connection. Currently, the VBA app populates an .ini file with the
> > password (provided by the app user) which the DTS package then uses to
> > log in to the source database.
> >
> > I'm not very happy about storing a password anywhere where somebody
> > could find it. Can anybody suggest a better solution?
> >
> > Thanks in advance,
> > Saul
> >
> >|||Hi
I am not sure why you need to do this as the password for any given package
would be relatively fixed! It is not passed as a global variable by it's own
argument on the command line.
If you ran the package from a stored procedure it could pick up the package
name and other parameters from a table
http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
and encrypt the whole command and keep the ini file. You could also use a
table of encrypted commands!!
John
"saul_margolis@.hotmail.com" wrote:
> Thanks John. I know I can pass in global variables when calling the
> DTS package, but the DTS package is called by the job. I would need
> some way of passing in the password to the job. Do you know if that's
> possible?
>
>
> John Bell wrote:
> > Hi
> >
> > Your package password can be specified on the command line using the /M or
> > /~M parameter, therefore you may want to pass an encrypted parameter or
> > possibly an encrypted command check out
> > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > am not sure why you are running this from VBA and not using SQL Agent!
> >
> > John
> >
> > "saul_margolis@.hotmail.com" wrote:
> >
> > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > a DTS package to pull data from one database and populate another
> > > database. In order to pull the data, we need to provide a password to
> > > the connection. Currently, the VBA app populates an .ini file with the
> > > password (provided by the app user) which the DTS package then uses to
> > > log in to the source database.
> > >
> > > I'm not very happy about storing a password anywhere where somebody
> > > could find it. Can anybody suggest a better solution?
> > >
> > > Thanks in advance,
> > > Saul
> > >
> > >
>|||Hi John,
It's not actually the password for the package that is needed. It's
the password that the package will use to login to the server which
contains the source data.
John Bell wrote:
> Hi
> I am not sure why you need to do this as the password for any given package
> would be relatively fixed! It is not passed as a global variable by it's own
> argument on the command line.
> If you ran the package from a stored procedure it could pick up the package
> name and other parameters from a table
> http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> and encrypt the whole command and keep the ini file. You could also use a
> table of encrypted commands!!
> John
>
> "saul_margolis@.hotmail.com" wrote:
> > Thanks John. I know I can pass in global variables when calling the
> > DTS package, but the DTS package is called by the job. I would need
> > some way of passing in the password to the job. Do you know if that's
> > possible?
> >
> >
> >
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Your package password can be specified on the command line using the /M or
> > > /~M parameter, therefore you may want to pass an encrypted parameter or
> > > possibly an encrypted command check out
> > > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > > am not sure why you are running this from VBA and not using SQL Agent!
> > >
> > > John
> > >
> > > "saul_margolis@.hotmail.com" wrote:
> > >
> > > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > > a DTS package to pull data from one database and populate another
> > > > database. In order to pull the data, we need to provide a password to
> > > > the connection. Currently, the VBA app populates an .ini file with the
> > > > password (provided by the app user) which the DTS package then uses to
> > > > log in to the source database.
> > > >
> > > > I'm not very happy about storing a password anywhere where somebody
> > > > could find it. Can anybody suggest a better solution?
> > > >
> > > > Thanks in advance,
> > > > Saul
> > > >
> > > >
> >
> >|||Hi
Can you not used the /E parameter?
John
"saul_margolis@.hotmail.com" wrote:
> Hi John,
> It's not actually the password for the package that is needed. It's
> the password that the package will use to login to the server which
> contains the source data.
>
> John Bell wrote:
> > Hi
> >
> > I am not sure why you need to do this as the password for any given package
> > would be relatively fixed! It is not passed as a global variable by it's own
> > argument on the command line.
> >
> > If you ran the package from a stored procedure it could pick up the package
> > name and other parameters from a table
> > http://www.sqldts.com/default.aspx?210, alternatively use the /Z parameter
> > and encrypt the whole command and keep the ini file. You could also use a
> > table of encrypted commands!!
> >
> > John
> >
> >
> >
> > "saul_margolis@.hotmail.com" wrote:
> >
> > > Thanks John. I know I can pass in global variables when calling the
> > > DTS package, but the DTS package is called by the job. I would need
> > > some way of passing in the password to the job. Do you know if that's
> > > possible?
> > >
> > >
> > >
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Your package password can be specified on the command line using the /M or
> > > > /~M parameter, therefore you may want to pass an encrypted parameter or
> > > > possibly an encrypted command check out
> > > > http://www.sqldts.com/default.aspx?301 on getting help for running dtsrun. I
> > > > am not sure why you are running this from VBA and not using SQL Agent!
> > > >
> > > > John
> > > >
> > > > "saul_margolis@.hotmail.com" wrote:
> > > >
> > > > > We have a VBA application that calls a SQL Server 2000 Job which calls
> > > > > a DTS package to pull data from one database and populate another
> > > > > database. In order to pull the data, we need to provide a password to
> > > > > the connection. Currently, the VBA app populates an .ini file with the
> > > > > password (provided by the app user) which the DTS package then uses to
> > > > > log in to the source database.
> > > > >
> > > > > I'm not very happy about storing a password anywhere where somebody
> > > > > could find it. Can anybody suggest a better solution?
> > > > >
> > > > > Thanks in advance,
> > > > > Saul
> > > > >
> > > > >
> > >
> > >
>

How to use OLE DB Command to update a derived column

Hi,

In my SSIS package,

1. I read from a source namely SOURCE. SOURCE is defined like

CREATE TABLE SOURCE

( f1 int, f2 int, f3 int, id int)

, where f1 is unique and id is always null after reading.

2. pass it to a Slow Change Dimension control to separate the new records and changed records

Then for new records,

3. link to a Derived Column control to add in some new columns

4. link to a OLE DB Command control to update field id before write to destination

In Step 4, I use the command :

INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool

UPDATE SOURCE

SET id =

(SELECT MAX(id) FROM global_id_pool)

WHERE f1 = ?

Using the command, the parameter can't be recognised with an error reported in BIDS. So eventually I had to change the query to the following to make it work

UPDATE SOURCE

SET id =

(SELECT MAX(id) + 1 FROM global_id_pool)

INSERT INTO global_id_pool

SELECT MAX(id) + 1

FROM global_id_pool

I don't mind the parameter can't be added. But after running, I found id field in the table after the OLE DB Command control is still NULL while the field in the SOURCE table in database is updated. So it seems the OLE DB Command worked on database but the data in memory cache wasn't affected.

So I just wonder if there is way to UPDATE the cache in OLE DB Command control. Many thanks for any help.

When you update the database after you've already selected from it, you can't expect that updated data to show up in the data flow stream. (It's technically in a different transaction's scope)

Can't you just send the new records to a staging table? Then in a second data flow you read from that staging table, while joining to the global_id_pool table (in the SQL source statement), and then insert into the destination table?

Monday, March 12, 2012

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

How to use multiple delimiters for the same flat file source while creating the package

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.

See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.

|||

Hi,

Thank you for your response.But i still encounter a problem.

It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things

|||

I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?

|||

Hi,

Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,

ERRORS:

1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.

WARNINGS:

1: Warning 1 The dependency 'EnvDTE' could not be found.

2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.

3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.

4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.

Thanks in advance

|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.

On the warnings, make sure your script project has references to the assemblies.

|||

Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.

As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.

|||Sounds like a problem in the script - could you share what you are using?

how to use multiple delimiters for the same flat file

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.

Quote:

Originally Posted by gopiganguly

Hi everyone,

There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.


How do you know there are 820 lines?

If you saw it in a text editor, it sounds like a different linefeed is being used (carriage return + linefeed combination opposed to just linefeed).

Otherwise, replace nulls with linefeed characters. You can use the SQL REPLACE function. Or, open the file with filesystemobject.textstream, readall(), then do a replace on the returned stream.

Wednesday, March 7, 2012

How to use deployment utility using a single package configuration file

Hi,

We are trying to create a deployment utility for a solution. The issue we are facing is, we are using a single package configuration file and when we try to build the solution to create the deployment utility, the build process fails saying that the package configuration file already exists. THe reason for this is while trying to build, the utility copies the configation fiel for the packages, it copies for one, but for the second onward, when it tries to copies, it fails saying the file already exists.

Any idea how to overcome this, or else any suggestions how to perform the similar steps to create a deployment utility for a solution in which the packages share a single package configuration file.

Any suggestion would be really appreciated.

Thanks in Advance,

Manish Singh

This is a recognised problem with the deployment wizard and will, I'm sure, be fixed at some point.

In the meantime, you may want to look at alternative methods such as XCopy. Another alternative may be the WiX toolset, I have had a lot of success with that.

-Jamie

|||Thanks for the post Jamie , but could you let me know how XCopy or WIX Toolset works

Thanks in Advance
Shailesh|||

XCopy basically means using the command-line tool xcopy.exe to manually copy the files.

Using WiX is far more involved and is a much igger subject than can be answered in a single post I'm afraid.

-Jamie

|||Thanks again Jamie , but i found a better option to use the shared configuration file . U can actually create an environment variable and set the path of the Configuration file . Which you could indirectly reference in each and every package .

There is another workaround , where you can manually create the manifest file . This approach allows you to call one configuration file in another :: havent checked this part though .

Thanks for your response
keep up the good work

Regards
Shailesh|||

Shailesh Chaudhary wrote:

Thanks again Jamie , but i found a better option to use the shared configuration file . U can actually create an environment variable and set the path of the Configuration file . Which you could indirectly reference in each and every package .

Shailesh

This is called an Indirect Configuration. I am a big fan.

Indirect configurations ROCK!
http://blogs.conchango.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx

-Jamie

How to Use Data Type GUID in UserVariable or foreach loop?

Hi Folks,

I am trying to build a Package that selects a list of Uniqueidentifiers by "Execute SQL-Task" and then loops through the ResultSet of the query using ADO.Net Enumerator to do something with this GUID Value, namely deleting all entrys with this PK in a different DataBase.

The main Problem I am facing is that you can′t select the type "GUID" for a user Variable in "Execute SQL-Task". All the Datatypes are there except for GUID!

This leads to the following error:

Error: 0xC001F009 at Package: The type of the value being assigned to variable "User::ImagicID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Is this a bug? Or am I doing something terribly wrong?

Best Regards and thanks in advance for your help

Chucker

Hi,

What is the variable initially defined as in the variables section? I believe it has to match that of the one that is being referenced in the SQL parameter mapping editor.

Is it possible for you to perform a CAST or CONVERT in the SQL statement to return the GUID ID as a string/varchar and do a string comparison when checking the GUID against the primary key?

Its just a though but i hope it helps.

Grant|||

For the Iterator (in the ForEach Loop), the only type that works (without converting) is "object".

When using this Type in the Delete Statement, I get the error that this is the wrong type.

So this may work if I only knew how to convert that "object" to GUID in a SQL Statement

I want to write something like

delete from table where pk = @.Uservariable

in the Execute SQL-Task within the ForEach Loop

then I get the error Message that @.Uservariable is not of type GUID

as I already mentioned in the Property Page of the ForEach Loop where

the colum pk can be mapped to a uservariable @.uservariable, the data

Type GUID does not exist.

Cheers

Martin

|||Did you try string? I had a similar issue, but it involved integers. According to books online this shouldn't be an issue, but stranger things have happen.

How to use criteria with text file source in DTS package?

I have a peculiar problem with a database.

I need to import data from text files where one column matches a value in a table that's already on the database. I cannot even fathom how to do this without pulling the entire 1GB file onto the database and simply deleting data that doesn't match the criteria. While this would work, I'm sure there's a more efficient way to do it! Can anyone give me some pointers? I'm not exactly a DTS expert.

Thank you.for this you will have to edit the column transformation ActiveX script generated by DTS. just need to add a "if" condition to skip the row in case the condition is not matching. something like below will do the job

Function Main()
if DTSSource("Src_Col_Name") = "Filter_Value" then
DTSDestination("Col1") = DTSSource("Col1")
DTSDestination("Col2") = DTSSource("Col2")
DTSDestination ............
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
End Function|||Is there an easier way to do this? The values I'm filtering by are held in a SQL table. I don't want to have to manually update these in the script because there will be several dozen policy IDs we're filtering by, and the table we're updating has 229 columns so that's a ton of scripting to do!

For example, we have a 1gb file we could import, but the people who use the DB are interested only in data pertaining to 30 policy IDs. They want us to import ONLY the data for those policy IDs; they don't want to see anything else.|||In your DTS function, write a few lines of VBA code to connect to the database and see if the current source row's policy ID exists in the table that holds the list of interesting policies.

-PatP|||Thanks for the suggestions, guys. :beer:

The way we ended up doing this is a very roundabout and slow. We're doing it in batches of 100,000 and putting the data into an intermediate table, running an update to put the data that matches the criteria into the final table, truncating the intermediate table, and going around to the start and doing it again until we get to the end of the file. This way, we managed to read through a 1GB text file and pull out only what we were interested in in our 200MB database. I know this is hideously inefficient and our DBAs would probably kill us if they knew about it but I did ask them for help and all they could do was point me in the direction of a couple of other programmers in the company who didn't have any ideas.

I'm not sure if we will continue to use this solution but it is so far the easiest one to maintain that we've thought of. I won't be at this job much longer (few months at the most) and when I leave the other team members (who are less experienced with SQL Server than I am, as horrifying as that thought is) have to maintain what I've written, so gigantic long ActiveX scripts are something I'd prefer to avoid if at all possible; some team members cannot write VBScript at all. Also, the words 'best practice' are unfortunately rarely uttered here. :mad:

Realistically we can't avoid ActiveX scripts; we have a few here and there, always to convert dates from one format to another, but the scripts suggested would just be too large for our team to handle. ActiveX definitely has its place though; I just wrote a script last week to convert Julian dates to Gregorian. No other way to do it than with a script! These suggestions may come in handy for a smaller-scale import too, so I'll tuck away a copy of the tread J.I.C. :D

Friday, February 24, 2012

How to use a vriable in DTS Package

Hello,

I have set up a simple DTS Package to copy a table (backup) in my database. I have specified the name of the table, however I want the naming of the new table to be dynamic and use the current timestamp, but I am not sure if and how I can define a variable and use it in the package.

Any thoughts? Do you need any more info?

Thanks.

RodneyHere is my SQL for the new table creation. I want the following name to be dynamic "CON_D_EmployeeInfo_backup20040526" Maybe use "CON_D_EmployeeInfo_{CurrentDateTime}".

CREATE TABLE [ancosalesdm].[dbo].[CON_D_EmployeeInfo_backup20040526] (
[EmployeeID] varchar (15) NULL,
[EmployeeTitle] varchar (46) NULL,
[EmployeePosition] varchar (46) NULL,
[EmployeeCostCenter] varchar (4) NULL,
[EmployeeDivision] varchar (256) NOT NULL,
[EmployeeStatusFlag] varchar (3) NULL,
[curr_ind] varchar (1) NOT NULL,
[last_up_date] datetime NOT NULL,
[eff_start_date] datetime NOT NULL,
[eff_end_date] datetime NULL,
[key] int NOT NULL
)